VBA to vlookup into various xls files in a given folder

[Celticfc]

Hello MR Excel, I've been away for years, nice to be back, really struggling with a problem that I thought would be simple, looked into web/ AI bots but can't figure out:


I have transaction data saved in over 100+ xls files in "Orders" folder on my desktop. There's only 2 columns in these files:

Column A: Order ID
Column B: Total Cost.

All I want is to create a simple Excel file, where I will enter the Order ID in A1 and get the Total Cost into B1. Many thanks in advance.

 

[Joe4]

Not sure why you think this would be simple - they are no function set to look at all files in a folder!

If it were me, I would probably try to approach it one of two ways:

1. Combine all your files into one master file, pasting all the data from your files down the two columns on one page (either manually or via VBA).
Then you would be able to use a simple SUMIF function on any Order ID to get your total cost.

2. Create VBA code that opens all your files in your folder, one-by-one, and gets the total cost of your desired Order ID with a SUMIF function and adds it to a running total.
Then at the end return that running total.