Is it possible in Excel to use data like this
obj circuit floor
1234 23 First
1235 24 First
1236 26 First
1236 26 First

then add columns like this with Pivot table or another way?
obj circuit floor type make
1234 23 First flood lucas
1235 24 First spot lucas


1236 26 First D/L phillips
1236 26 First D/L phillips

using another workbook with data like this
obj type make
1234 flood lucas
1235 spot lucas
1236 D/L phillips
1236 D/L phillips

Sorry for the strange way of showing the problem. So based on the common obj link the extra data would be added to the first wookbook.

Problem is the first set of data would not stay in sync with the second workbook.
The sequence in the first workbook may be
1234
1234
1234
1236
and would only have a subset of obj codes. The second workbook would contain all the obj codes.

Yeah, a VLOOKUP will do that easily.

=VLOOKUP(objtable1, table2, 3, false)
=VLOOKUP(objtable1, table2, 4, false)

Wow I will have to try that do both data sets have to be in the same workbook on different sheets or can you link to a different workbook? I'm not sure about that side of things. Thank you for your input at least now I know it can be done.

All are possible.

That is such good news, thank you so much, I had to find out before spending all the time learning how to do it!

Okay I now have a working sheet. The right hand columns get the info they need from the columns A,B,C. But the right hand columns could contain 100 rows or more as could the data columns A,B,C. How do you generate the Vlookup statement automatically in columns I and J. If I have to type each statement in I might as well just type in the data from columns B and C. Should I be using a pivot table instead or is it something that needs VBA?

Attached Files

Test vlookup.xlsx (9.6 KB, 10 views)

There are a few ways to skin that cat.

Absolute referencing
As you've found, copying =VLOOKUP(E2,A4:C7,2,FALSE) changes the range of the lookup table as you copy down. You can use absolute references to handle that. Put a dollar sign in front of the references you want to keep constant. In this case, only the row numbers change so the formula in row 2 can be written as...

=VLOOKUP(E2,A$4:C$7,2,FALSE)

You can make both cell and column absolute in this case (e.g. $A$4:$C$7) and it won't matter. Same for the other VLOOKUP. Now when you copy them, no problem.

Named ranges
The above is fine, but not entirely robust. Naming the range is a bit better and makes the equation more logical. Select your lookup table on the left and use Formula>Define Name. Call you table something descriptive (e.g. PartList) and press OK. The formula can now be written as...

=VLOOKUP(E2,PartsList,2,FALSE)

IMHO, it's best not to have spaces in named ranges.

Format as Table
Even more robust is to use the Format as Table function. Select each table in turn and use Home>Format as Table and select a format you like. As with Ranges, I suggest your column headings not have spaces so I changed MANUFACTURED BY to MANUFACTURER.

Next, use Formula>Name Manager to logically name the tables. Excel automatically names them Table1, etc. which is not descriptive. I named them PartsList and Inventory. I also prefer to have each table on separate sheets, but I'll leave that up to you if you like (easily done with cut/paste). Making each table bigger or smaller is a snap by either right clicking and Insert/Delete or by dragging the little thingee on the bottom right of the table.

Now, when you enter the type the formulas in row 3, Excel automatically populates the cells with the formula all the way down. Look at how the formula looks now...

=VLOOKUP([@OBJ],PartsList,2,FALSE)

So much more logical. Anyhoo, I've attached the finished sheet here. Lemme know if you need more help. I know I've dropped a lot of info on you, but if this sinks in you will be an Excel pro. :-)

Attached Files

Test vlookup.xlsx (11.3 KB, 10 views)

Awesome! Gebobs, you have explained that so well in such a logical step by step way that I'll be able to absorb all that info. I also like the way you think it through to make it as efficient and logical and bullet proof as possible. I get a kick out of making AutoCAD do what I want with Lisp so I'm also pleased to see that Excel can do something like that so well. A very big thank you Gebobs!

Just trying to get further with the problem and I'm completely confused now. Just when I thought it all made sense! What I don't understand about your solution is that the table (Inventory)containing the manufacturer and Item now does not have the obj numbers. There is only one list of obj numbers which is with the table (partslist) that has the data from the drawing. So how does it know which item goes with which obj number in the partslist. Surely both tables have to contain the obj column ? Does the Inventory table have to have the Obj column (if it needs one) to be consecutively numbered? hmmm I'll have a strong cup of coffee and start playing with it step by step and see what I can work out. Something fun to do this weekend. What I would like to do it have the Inventory in Sheet1 then add the partslist to sheet2 as you suggested.....sorry for driving you nuts!

It knows to look in the named table first column for the link obj - clever thing! Thats why that data has to be in the first column. But I still don't know how you managed to hide the obj column in your inventory table....I'm making progress just have to get the partslist on another sheet and still get the link to work...

Oh I see, I am learning how amazing Excel is. It does not need the obj Inventory to be in numerical order, you can sort the parts list in any way you want and it still keeps track, you can sort the Inventory obj in any order too I am blown away! Now for a complete dry run....

Hi
quoting entire posts makes the thread hard to read and is just clutter. Better avoid it in the future - Thx (as there is no mod around, someone has to ask you )

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hello, what? Yes, it does! It's in the first column, E.

I didn't.

Yes you are right I was too excited and hit the quote button to answer his post- won't do it again...Thanks to you guys for a really useful forum...my problem is solved now.