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

Hello,

Unfortunately that formula does not work when I add a column to Jan tab. It will still pull in AB instead of "Closing NAV" which becomes AC.

Thank you for your help. I appreciate it. :)

Answer 2

Hi,

Assuming that the headings are in row 10, try this

=INDIRECT("'"&$N$1&"'!"&ADDRESS(11,MATCH("Closing NAV",$A$10:$AZ$10,0)))

So this formula will search for the text Closing NAV in row 10 (in A10:AZ10) and return data from row 11 of that column.

Hope this helps.

Answer 3

Hello,

Sorry but I am not sure how to link the excel file. Could you give me an example of what the formula would be if using a column header? For example Sheet 1 is Jan and Sheet 2 is Feb.

                                Jan excel tab                                                             Feb excel tab

                                Column AB                                                                 Column F    

                          Closing NAV  (header)                                            Opening NAV (header)

  Row 11                    $200                                                                 Needs to pull $200

  Row 12                   $500                                                                  Needs to pull $500

   Row 13                 $4000                                                                Needs to pull $4000

Formula I am using works where N1 is the "previous sheet" formula you gave in the last excel sent above.

=INDIRECT("'"&$N$1&"'!"&CELL("address",AB11)). But now If I a column to Jan tab, then the formula will continue to take AB where it should be taking AC.

Answer 4

Hi,

Please share the link from where I can download your workbook.  Also indicate the cell where you need my help.

Answer 5

Yes column AB has a heading "Closing NAV" with numbers underneath.  What would be the new formula I need to use?