lookup values from several identical workbooks based

sparx

New Member
Joined
Jul 29, 2008
Messages
16
I have a list of workbooks (customer requests) - placed in different folders, one per request)
folder has an increasing number

I want to create an overview excel sheet that reads the same values from each request workbook like:

typenameby whofor whomcreatedcust.reqdel.time
1130​
1131​
1132​

each workbook is called
CR_1130.xlsx
CR_1131.xlsx

my idea is to put in the CR numbers (1130) in the overview sheet and then the sheet would use this as the reference for the lookups/index or how it should be done.

Can somebody help me with this?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
With all 3 workbooks open, in the one you want the formula in type "=" (and whatever function you need) and then just switch to the first workbook and click in the cell needed, then switch to the second one and do the same, finish the formula and hit [Enter]. You'll get a formula that looks like this:
Excel Formula:
='[JobsStats.xlsx]Currency Value'!$A$1
the text between the square brackets is the name of the file. Note that the cell references are locked. They don't need to be. You can remove the $ before the column or row or both so that the formula can be copied to other cells and retain their relative reference. Note that if there's a space in the name of the file or sheet, those names will be surrounded by a single quote.
Now, close the two subsidiary workbooks, and the formula above will change to:
Excel Formula:
='C:\Users\USERID\Desktop\[JobsStats.xlsx]Currency Value'!$A$1
The formula automatically add the file path and it will continue to work even if the other workbook is closed. At least it would with Excel 2016. Don't know what version you're using. You can update your profile to indicate what OS and Excel version you're using to help others know what you're working with when they try to help.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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