PREVSHEET & Adding Columns

Question

Hello,

Can anyone help with the problem below....

I am trying to pull information from a previous sheet to new sheet. I used the VBA macro formula for "prevsheet".

 In the new sheet (next tab) i used the formula 

=Prevsheet(Indirect("W9")) to pull a number from Column W, Row 9. in the previous sheet The only problem is if i add a column in the previous sheet my formula will not change to "V9" it stays at "W9" which pulls in the incorrect number. I need my formula to change to the correct column if i ever decided to go back and add/remove columns. This is for Excel 2010. 

***Post moved by the moderator to the appropriate forum category.***

Answer 1

Hi,

Then only way this can happen is if we searching for the heading of column AB11.  If that column has a heading, say Amount, then we can search for that in range A1:AZ1 and then look under that column.  So does column AB have a heading for you?

Answer 2

Hello,

I used the formulas you provided in my worksheet. Its pulling in perfect but if i add a column to the previous tab, then the formula you have on ur worksheet doesnt change for the adjustment. I am not sure what i am doing incorrectly. The only step i skipped was 2 bc i wasnt sure what the name manager box is. Not sure if thats the issue. 

This is the formula. 

INDIRECT(""&$N$1&""!AB11")

N1 Is pulling the previous tab. I tried this as well INDIRECT(""&$N$1&""!$AB11")

If i add a column to previous sheet i need it to pull from AC now. Not AB.

Thanks!

Answer 3

Hi,

Refer to my solution - Worksheet formulas in a newly copied worksheet should point to the previous worksheet

Hope this helps.

Answer 4

Hello, 

Thank you for the response. I tried the link which solves my problem when i add or delete columns to previous sheets but i also need to create a copy of the previous sheet and then use it for the next month. So for example if i link April  to "March" worksheet then when i create a copy and label it May it needs to pull Aprils numbers but with using link it still pulls March.

Answer 5

Hi,

Why not simply link to cell W9 on the previous sheet?