Pasting VLOOKUP to a new Spreadsheet

Piktro

Board Regular
Joined
Mar 19, 2013
Messages
56
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,196,179
Messages
6,013,886
Members
441,791
Latest member
SKeulder

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top