Vlookup of Sheet Names

**HereInOhio** · 10-26-2016, 10:55 AM

I have a workbook that I keep all of my customers updates on. Basically a list of part numbers and the quantities they want on each of the following days. When he sends over updates periodically I need to determine which parts are new and which I have. The problem is they come over with one part number per sheet (there's about 150 parts). I also need to update all my information with their new 'updated' information.

This question has a few parts:
1. Can I write a vlookup that bounces my cell value (part number) off of sheet names (the sheet names from their update-one for each part). For example if I have cells with part numbers 81069, 80873, and 80999 but my customer sends over an update with one sheet for each part number 81069 and a sheet for 80999 and a sheet for 91111. I want to be able to tell what parts I already have listed and what parts are new.

2. I have the sheet look at each of the part numbers on my list and return cells from the update from the sheet of the customers update with that part number. (I have a reference to =CustomersSheet.xlsx]81069'!$B2). I currently go in and physically switch the part numbers one by one each line, 81069 in the example will change to 80873. I have this part number listed to the left. Is there a formula that will switch the part number to a cell in that row? For example if part 81069's row has the formula =CustomersSheet.xlsx]81069'!$B2 and I drag it down to part number 80873's row, is there a formula that will reference/swap the 81069 with the new rows part 80873?. =CustomersSheet.xlsx]80873'!$B2

3. Lastly, my sheet has the part in column A1 then the values in column B1,C1,D1, ect. The customer has it transposed so the quantities are in row A1, A2, A3, ect. Is there a way to have it transpose these for the lookup or is it easier to transpose my sheet, do the lookups then convert back? I have other information/formatting it may mess up so I figured I would ask.

Here's a simplified version of what I am trying to work with.
Customer Example.xlsx

Sorry if this is not clear, please just ask if I need to be more clear about anything.

**dluhut** · 10-26-2016, 11:20 AM

Hello,

Looking at your attached example, I still don't understand.

For example, for part 80999, you have the quantity based on the date itself.

However, for part 80873 and 81069, say for Oct 25, you have 0 and 800 in 'My Sheet', but for the each respective part sheet, the quantity would be 200 and 400.

So my question is, what should be the correct output in 'My Sheet'.

Would it be to reflect as the part sheet or +1 day

**dluhut** · 10-26-2016, 11:24 AM

Is this what you're looking for?

If for some reason attached sheet is not working, put the following formula on cell B2 on 'My Sheet'

Please Login or Register  to view this content.

**HereInOhio** · 10-27-2016, 10:28 AM

However, for part 80873 and 81069, say for Oct 25, you have 0 and 800 in 'My Sheet', but for the each respective part sheet, the quantity would be 200 and 400.

So my question is, what should be the correct output in 'My Sheet'.

My apologies -you have it correct. I accidentally put the "$" in front of the row instead of the column.

Is this what you're looking for?
=VLOOKUP(B$1,INDIRECT("'"&$A2&"'!$A:$B"),2,0)

Yes, it worked great for the lookup. This takes care of the question 2 and 3 above.

For question #1 I am wondering if there is an easy way to have it let me know if any new parts were added (that are not listed on "My Sheet") so I can add them and account for the new items. Even if it's as simple as throwing all of their sheet names in a list on a single sheet that I can do a vlookup from there.

One last question, on my example I had the part number sheets in the same workbook as "My sheet". In reality they are two different workbooks; 1st workbook with "My sheet" 2nd workbook with Customer updates with all part numbers.

I know I can copy all of their sheets into the workbook I am using but is there an easy way to have it reference their workbook? It would be nice to not have to copy all of their sheets every time I receive an update.

Thanks again for your help!

**dluhut** · 10-27-2016, 03:50 PM

Hello,

From what you said, it COULD probably be a combination of Macros and Formulas.

Macros where it needs to check if there's any new parts whereas formulas to take into account the lookup formula.

However, given the formula that I've given you, I'm positive that it won't work since there's workbooks involved.

**HereInOhio** · 10-31-2016, 07:17 AM

Thanks for the reply. It's not that big of a deal copying the worksheets from the customer's updates into the workbook I keep my sheet on.

You don't know of a formula that will list on a single sheet all of the sheet names contained in a workbook do you?

**JohnTopley** · 10-31-2016, 08:23 AM

Try

First, insert a sheet (preferably the first sheet in the workbook) and call it Contents.

Now, press Ctrl F3, and define two names, one

BookName

that refers to

=GET.WORKBOOK(16)

and a second one,

Sheets

that refers to

=SUBSTITUTE(GET.WORKBOOK(1),”[“&BookName&”]”,””)
that creates an array of each sheet in the workbook.

And create another name

Now, onto the contents:

In A2 of "Contents", put

=IFERROR(IF(COUNTA(Sheets)>=ROWS($A$2:A2),INDEX(Sheets,ROWS($A$2:A2)),””),"")

and drag it down all the way to say, A50.

Workbook has to be saved as XLSM file