I have a worksheet that draws info from a pivot table which has info for
salesmen. I had to add a new salesman and now all the info on the worksheet
is not getting the correct info because inserting a new salesman made all the
data move down a few lines. How can I get the correct info without retyping
new formulas with the new rows I need to reference?(I.E., instead of AC164
now I need AC 169)Below is a formula from my spreadsheet: Thanks for any help!

=IF(Database!$C$2="PRIOR YEAR",'Calc
File'!$AC$8,IF(Database!$C$2="DECEMBER",'Calc
File'!$AC$164,IF(Database!$C$2="JANUARY",'Calc
File'!$AC$320,IF(Database!$C$2="FEBRUARY",'Calc
File'!$AC$476,IF(Database!$C$2="MARCH",'Calc
File'!$AC$632,IF(Database!$C$2="APRIL",'Calc
File'!$AC$788,IF(Database!$C$2="MAY",'Calc
File'!$AC$944,IF(Database!$C$2="JUNE",'Calc File'!$AC$1100,0))))))))

You should use GETPIVOTDATA to extract data from a pivot table. Then
your issue should not occur.

Instead of complex formulas, you can use a GetPivotData formula to
extract the data that you need. There are examples in Excel's Help, and
he

http://www.contextures.com/xlPivot06.html

In Excel 2002 and later versions, if you type an equal sign, then click
on a data cell in the pivot table, a GetPivotData formula will
automatically be created.
Then, you could modify that formula, replacing the text strings with
cell references. In your worksheet, the formula might refer to
"December", and you could replace that with $C$2.

KO wrote:
I have a worksheet that draws info from a pivot table which has info for
salesmen. I had to add a new salesman and now all the info on the worksheet
is not getting the correct info because inserting a new salesman made all the
data move down a few lines. How can I get the correct info without retyping
new formulas with the new rows I need to reference?(I.E., instead of AC164
now I need AC 169)Below is a formula from my spreadsheet: Thanks for any help!

=IF(Database!$C$2="PRIOR YEAR",'Calc
File'!$AC$8,IF(Database!$C$2="DECEMBER",'Calc
File'!$AC$164,IF(Database!$C$2="JANUARY",'Calc
File'!$AC$320,IF(Database!$C$2="FEBRUARY",'Calc
File'!$AC$476,IF(Database!$C$2="MARCH",'Calc
File'!$AC$632,IF(Database!$C$2="APRIL",'Calc
File'!$AC$788,IF(Database!$C$2="MAY",'Calc
File'!$AC$944,IF(Database!$C$2="JUNE",'Calc File'!$AC$1100,0))))))))




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thanks, I'll give it a try...I'm not too good at this, but am trying to learn

"Debra Dalgleish" wrote:

Instead of complex formulas, you can use a GetPivotData formula to
extract the data that you need. There are examples in Excel's Help, and
he

http://www.contextures.com/xlPivot06.html

In Excel 2002 and later versions, if you type an equal sign, then click
on a data cell in the pivot table, a GetPivotData formula will
automatically be created.
Then, you could modify that formula, replacing the text strings with
cell references. In your worksheet, the formula might refer to
"December", and you could replace that with $C$2.

KO wrote:
I have a worksheet that draws info from a pivot table which has info for
salesmen. I had to add a new salesman and now all the info on the worksheet
is not getting the correct info because inserting a new salesman made all the
data move down a few lines. How can I get the correct info without retyping
new formulas with the new rows I need to reference?(I.E., instead of AC164
now I need AC 169)Below is a formula from my spreadsheet: Thanks for any help!

=IF(Database!$C$2="PRIOR YEAR",'Calc
File'!$AC$8,IF(Database!$C$2="DECEMBER",'Calc
File'!$AC$164,IF(Database!$C$2="JANUARY",'Calc
File'!$AC$320,IF(Database!$C$2="FEBRUARY",'Calc
File'!$AC$476,IF(Database!$C$2="MARCH",'Calc
File'!$AC$632,IF(Database!$C$2="APRIL",'Calc
File'!$AC$788,IF(Database!$C$2="MAY",'Calc
File'!$AC$944,IF(Database!$C$2="JUNE",'Calc File'!$AC$1100,0))))))))




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html