0

I have a Pivot Table (columns 1 and 2), and I also have manually inserted data (columns 3 and 4):

Table1

But when more data gets added to the pivot table, the manually entered columns won't follow "their" row, so the rows become misaligned like this:

Table2

I would need the table to behave so that when new data gets added to the pivot table, the manually inserted rows keep up with "their" row, like this:

Table3

I've looked around, tried to make it work, but couldn't find anything. Any help?

1
1

You have a row misalignment issue. Hand-entered data is static and will stay put as dynamical formula results or pivot tables are free to expand and contract.

One solution would be to make columns 3 and 4 part of the table from which you create the pivot.

Another would be to use a unique ID (perhaps the name field, if names are guaranteed to be unique) as a lookup key, and maintain columns 3 and 4 in another sheet together with the unique ID. You can then use a vlookup() formula to place columns 3 and 4 side-by-side with the pivot table.

Lance has given a thorough treatment of the row misalignment issue and how it can be dealt with in some cases.

1
  • Thanks a lot, I thought about making the columns part of the pivot, but that wouldn't help with how things are done here. The unique ID idea on the other hand, is just perfect. Jun 17 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.