keep rows aligned with pivot table?

[nimv1]

Hi there,

After creating a pivot table on a new worksheet(columns A-C), I manually insert text outside of the pivot table (e.g. column D).

However, when I update the data and click refresh in the pivot table, the text outside the table loses its alignment.

Is there a way to lock this so if the pivot table data moves rows, the text that is in the same row also moves to stay in line?

Your help is much appreciated,

Regards

N

[JBeaucaire]

What data is being added outside the table? Can it not be added IN the table as calculated fields? Or added to the raw data so it appears on its own within the table.

[kev_]

See example in attached workbook

One way would be to use the Indirect function in combination with Named Ranges based on the values in left column of pivot table
If you want text against Total rows or rows where the text in left column contains spaces, then use a helper column as in the example - the names of ranges cannot contain spaces. (You can hide the helper column or put in in column Z out of view etc)
If there are no spaces in the value against which you want text then you do not need to use a helper column

this removes spaces and is in cell F2:

Please Login or Register  to view this content.

this formula is in D2 where you want the text

Please Login or Register  to view this content.

Pivot_IndirectFormulaWithHelperColumn.jpg

set up named ranges

Pivot_NamedRanges.jpg

[nimv1]

Hi there,

Thanks for your help.
These do work, but I wanted something very basic as the text is updated regularly in column D and there is a lot of data so I cannot afford to do a named range.
The text cannot be added to the pivot.

I was thinking there is a simple formula so if you changed the order of the pivot table and had text in column D, this would also automatically move in alignment with the order of the pivot?

Regards

N

[kev_]

It is a simple formula
=Indirect(A2)
Unless there are spaces in the values in columnA

You simply update the text in the named cell when it changes.

If anyone can make it simpler than that I'll be impressed

[JBeaucaire]

If the text added is related to a TEXT field on the same row, then you could accomplish this with a simple VLOOKUP as well if you built a 2-column reference table elsewhere that had the text from the pivot in 1st col and your needed added text in the 2nd column.

Now use a VLOOKUP to get that text into column D next to your pivot.

When the pivot updates and rows move around, the vlookup formulas would update as well keeping your text aligned.

Remember, for a FORMULA solution to work for you in column D, then a formula must exist in column D, meaning it is referencing this information elsewhere.

[kev_]

Or another way would be to use INDEX and MATCH formula in column D with a separate lookup table elsewhere with one column an exact match for the relevant values (per column A of the pivot table) and the 2nd column holding the text to display next to the pivot table.
(which is very similar to JBeaucaire suggestion in post#6)

[kev_]

, but I wanted something very basic as the text is updated regularly in column D and there is a lot of data so I cannot afford to do a named range

Do the values in column A of the pivot table change regularly or are you always selecting from the same set of values? If your set of possible values is constant, a named range would only require setting up once.
If, on the other hand, the values in column A are a rapidly changing set, then INDIRECT,VLOOKUP or INDEX&MATCH all require the ongoing maintainance of a table that keeps changing..

[nimv1]

Hi there,

The values in column A get updated on a daily basis (e.g 4016, 4017,4018 additional rows to 4013,4014 etc).
They change order, but remain the same.
I will then have text outside of the pivot (e.g. Thomas in the same row as 4016, Sam in the same row as 4017 etc). But when pivot table is updated, the name in column D will not align.
Is there away of keeping it aligned without a named range?
If not I would like help on how to setup a named range please?

Regards
N

[JBeaucaire]

So keeping with our suggestion, you need a reference sheet with a table:
Data Range

	A	B	C
1	Code	Name	Info
2	2013	Carl	Dept1
3	2014	Tony	Dept2
4	2015	Cal	Dept3
5	2016	Thomas	Dept4
6	2017	Sam	Dept5
7	2018	Sid	Dept6

With that, you can use a VLOOKUP or INDEX/MATCH to pull in this extra text/info from this static ref sheet.

[kev_]

See attached workbook for worked example showing all 3 methods
(in the workbook itself each of the formulas below are wrapped in IFERROR to keep things pretty)

INDIRECT & NAMED RANGE

Please Login or Register  to view this content.

VLOOKUP

Please Login or Register  to view this content.

INDEX&MATCH

Please Login or Register  to view this content.

PivotLookUp.jpg

PivotNamedRanges.jpg