Any Excel gurus on here?

I have two separate Excel files that can’t be combined to one.

File One contains within it a list of ‘shipment’ reference numbers in column I. File Two has shipment reference numbers in column G on all of its many tabs (52 tabs, one for each week). File 2 also contains ‘booking’ references in column J.

I want column H in File One to tell me what the booking reference is for the shipment number in column I. The formula will therefore have to look at all tabs in File Two for the number contained in column I in File One, and then state the booking reference for that shipment.

I’d like to think Excel is capable of this but not sure how to do it. Any help would be appreciated!

Can’t you just use post-it notes or something?

can the not be put as sheets in the same file?
otherwise, Look up tables work across files
VLOOKUP()

Look up tables work across files

…..but not so well across 52 multiple data ranges in a separate worksheet.

Which is why what the OP is trying to achieve seems as though it should be simple but is deceptively difficult. I’ve been trying for the last 15 minutes and it’s beaten me.

Post-it notes it shall be!

I am sure that the competitive Excell-ers will be along presently to show us how it’s done.

People often struggle along with Excel when a database would be a MUCH better idea.

This is exactly one of those situations.

How big is the data set ?

A bit of SQL in Excel will be the best way forwards, a lot easier than the 52 lookups you’re heading towards right now.

not sure i get the problem,
let me restate and see…

Shipment number is the unique identifier
you have a list of shipment numbers and want to look across all 52 sheets for the booking reference which matches that shipment number?

Ive done something like that before, but seem to remember it was hideous. And of course it will chomp memory like it’s free food.

Definitely a database solution to a database problem really. But Im on my sick bed so might see if I can remember how I did it.

My SQL skillz are low but I’m sure someone will be along soon. I think you need to:

Define the 52 weeks of booking references as tables
UNION them all together
Do a JOIN with the shipment references

I’ll have a quick play while I eat my sarnies.

It’s back to array formulae, just make sure your worksheets have the same name, e.g. sheet1, sheet2 etc.

Couple of solutions.

A custom VBA formula is one
http://www.ozgrid.com/VBA/VlookupAllSheets.htm

another is defining ranges (for each array in the 52 sheets) and then using INDIRECT to parse them

There’s no syntax native to the VLOOKUP formula that can handle it. I cant get it to work with an Array formula either.

There’s no syntax native to the VLOOKUP formula that can handle it. I cant get it to work with an Array formula either.

Yeah, I KNOW that……now 🙂

It would be easier
you need a column with the names of the sheets
select then range of names and call them ‘Sheets’

=VLOOKUP(A2,INDIRECT(“‘”&INDEX(Sheets,MATCH(1,–(COUNTIF(INDIRECT(“‘”&Sheets&”‘!$A$2:$B$100″),A2)>0),0))&”‘!$A$2:$B$100”),2,FALSE)

though it doesn’t help that your lookup and target are the wrong way round,
can you swap them or recreate another lookup column?

One simpler method would be to create a new worksheet, with Shipment ID in leftmost column, and week numbers along top row (make sure weeknumbers are in the same form as the worksheet names in workbook2)

then use INDIRECT and VLOOKUP with an ISERROR capture to return True or False for the incidence of a shipment in a given week, then collapse the new table to a single column in column 54 giving the weeknumber in which the TRUE was returned for that shipment ID.

Can you install Powerquery? If this means nothing to you, Google it.

Thanks all.

A database wouldn’t work, the two files do a lot more besides what I’ve stated, I’ve just tried to keep it simple and only say what needs to be known for this query.

‘Powerquery’ – our IT department would almost certainly refuse my system permission to download anything. It’s pretty strict around here! For example, we’re about to have all USB ports deactivated so they can’t be used in conjunction with a mass storage device (which for me means I won’t be able to upload my run/ride commutes from my Garmin until I get home).

CharlieMungus – Member
not sure i get the problem,
let me restate and see…

Shipment number is the unique identifier
you have a list of shipment numbers and want to look across all 52 sheets for the booking reference which matches that shipment number?

Correct, but all 52 sheets are in another file.

I’ll have a play around with some of the suggestions. Appreciate them all, cheers.

You could use VBA to fetch the columns you need from the sheets in file 2 and paste them into a new sheet in file one. Then use vlookup to get the info from that sheet.

Create named ranges in the 52 tabs in File Two. They can by dynamic if that helps.

Some SQL like this in File One will pull them all together in one table:

SELECT Shipment_Number, Booking_Reference
FROM
(
SELECT Week1.Shipment_Number, Week1.Booking_Reference FROM Week1
UNION ALL
SELECT Week2.Shipment_Number, Week2.Booking_Reference FROM Week2
UNION ALL
SELECT Week3.Shipment_Number, Week3.Booking_Reference FROM Week3
UNION ALL
SELECT Week4.Shipment_Number, Week4.Booking_Reference FROM Week4
)

And so on for all the other weeks. And then do an INDEX/MATCH on this table to pull the records you want into your report. Or do a JOIN with your list in File One to get the results in one table – this might be necessary if it’s not a 1:1 relationship (multiple shipments per booking, or vice-versa ?)

Thanks again, going to try sort it this aft.

it needent be in the left in file one, but it needs to be at the left hand edge of the search array in file two.

ah, you’ve edited your post.

Stoner – Member
it needent be in the left in file one, but it needs to be at the left hand edge of the search array in file two.

ah, you’ve edited your post.

Sorry Stoner, I reaslised straight away and edited, you must have been quick!

This is a bit of a chore way of doing it but..I reckon you could set it up in about 20mins

How about just build a new helper-sheet in file one

Then
=[File2.xlsx]Week1!$G$2 in G2
&
=[File2.xlsx]Week1!$J$2 in H2

Now you can see where this is going, create a Week_No. index in column A and Line_No. in column B Then make the cell references above into variables

http://stackoverflow.com/questions/20011854/using-a-number-in-a-cell-to-generate-a-cell-reference

samunkim – That will be a nightmare to maintain as the source data grows.

number18 – if the idea of SQL doesn’t terrify you, what I’ve suggested will be by far easiest way to do it. This is a massive pain to do with formulas. I’ve done a worked example, let me know your email and I’ll send it over.

just copy and paste my solution

don’t forget it’s array, so ctrl and shift

Actually, Charlie’s solution is pretty neat (as long as it’s a 1:1 relationship).