VLOOKUP/NAME CHANGER HELP - Sorting data

[peperammi69]

Hi there,

I'm trying to create an Excel spreadsheet to house all our parts/products for our building company.

I have set up in name manager 'Building Products' and then used data validation to create a drop down list of products under that category...

...the problem is, each item under the name 'Building Products' has a different price and a different quantity (ie: each, box, per meter).

Any advice on filling in the blanks? I think it needs a CHOOSE or VLOOKUP function....

Thanks in advance, Matthew Tingle

[GeneralDisarray]

do you have an example sheet to edit? this is easier to show than explain :D

[peperammi69]

Of course :-)

I have added a few dummy entries into the DATA sheet to try and get across what I mean...

[GeneralDisarray]

easy peasy

basically, you can benefit greatly from using INDEX() and MATCH() here in a couple of places:

1) In the name manager to allow for "dynamic named ranges" - this allows your drop down to auto-adjust to fit new products and eliminates a bunch of confusing blank space in the drop down.

2) In the cells for "Unit" and "Cost + VAT" - here you can get the values you need from the reference table.

I've authored what you need in the Unit column. I was going to go for the other column, Cost + VAT but I couldn't see where that came from in the table (didn't match anything in the row i could tell).

enjoy

[peperammi69]

Wow, thanks for the speedy reply!

The groups are 'Building Products', 'PVC Trim', 'Soffit and Fascia' etc. The 'Cost 1', 'Cost2' etc are the prices for products within the group. It's not your fault you didn't understand it though - its not very understandable to me yet! :-)

The idea is, on my Materials sheet I will have a list of parent products such as 'Building Materials' and 'PVC trim' and you pick from the drop down separately...

As I'm typing I've realized i can't do that anyway... What if you wanted to price sand and gravel together, you would need 2 lists...

...or am i wrong? :-P (EXCEL NOOB)

Thanks again,
Matthew Tingle

[GeneralDisarray]

I'm not sure what you are describing.


But, if you are attempting to set up a second drop down based on the first selection, that is possible. Read up on it here: http://www.excelforum.com/excel-tips...72#post2849772

I like doing that with index and match also