Pasting VLOOKUP to a new Spreadsheet

[Piktro]

First, I'd like to say that this forum has been extremely helpful since I signed up - you guys are great!

Here is the current issue I'm having.

We have a report called Accounting Posting that's generated online for our POS system. We export to Excel, and it creates a file for us to download.

In that file, it lists many values (necessary, and unnecessary) - across 4 different Sheets.
The sheets are always named BF01, BF03, BF04, BF05 every time we run this report.

I created an additional sheet called AP Formula that runs a VLOOKUP across all the cells in the other sheets, to consolidate the information into 4 neat columns. The problem I have, is that whenever I copy the data from the AP Formula into a new document, or into another Accounting Posting report, it pastes it as

=VLOOKUP(A9, '[Accounting Posting 2013 - 03 - 21.xlsx]BF01'!G$1:H$60,2,FALSE)

Instead of

=VLOOKUP(A9, BF01!G$1:H$60,2,FALSE)

I tried using the Indirect function in conjunction with VLOOKUP but I could not get it to work

 

[dermie_72]

instead opf copying the cell, highlight the text in the formula bar, and copy that, then paste that into the new worksheet.

See if that works.

 

[Piktro]

It does work, but unfortunately there are over 300 cells worth of data that would need to be retyped every time.
I'm trying to save our accountant from rearranging a bunch of cells, removing columns, rearranging data, etc.

It takes her on average, 3 minutes per Sheet - and she does 2 weeks worth of reports at a time, across 4 stores (48 sheets, 3 min each, takes almost 3 hours for just consolidating the data she needs to input)

I figure if I can shave some hours off her day it would be well worth it, especially if someone else ever needs to do the work - they would just be able to use the sheet, and not have to worry about what to input, what not to, etc.

 

[dermie_72]

if you drag the cells down/across ect, it should mean you don't ahve to change every cell, just the first cell in the tables.
if: =VLOOKUP(A9, BF01!G$1:H$60,2,FALSE) is dragged down, it will automatically change to =VLOOKUP(A10, BF01!G$1:H$60,2,FALSE) ect. The first value will increment down. If you drag right, then the A will become B to C ect.


The other option would be to do the paste as you first did, then highlight the tables, and press control + H, (find/replace), and replace '[Accounting Posting 2013 - 03 - 21.xlsx]BF01'! with nothing.