VLOOKUP Problem

[Mooseman60]

I am having problems with the VLOOKUP in my workbook

I can select one category and it inserts the information into the respective cells

When i select the another category it only fills in one cell

I believe the problem is that the table array is not changing

I have attached my workbook for you to see what the problem is, any help would be greatly appreciated

[Domski]

You could use Indirect like this in F23: =IF(D23="","",VLOOKUP(D23,INDIRECT(C23&"Lookup"),2,FALSE))

Beware that Indirect is a volatile function though so extensive use can have performance issues.

Dom

[Mooseman60]

Domski

That solved the problem thanks very much for your help

[Mooseman60]

I have another problem now. Instead of having all the data on one worksheet I have created separate worksheets for each category but now I can't get the second combobox to populate I have attached the updated workbook

[Domski]

Your source for the data validation needs to be: =INDIRECT(C20&"List")

Dom

[Mooseman60]

Dom

Where exactly do I paste this formula as I tried it in the formula below but it didn't work

=IF(D20="","",VLOOKUP(D20,INDIRECT(C20&"List"),2,FALSE))

[Domski]

You are using dependant data validation based on the value selected in column C so you need to change the source of that, not the vlookup formula.

Dom

[Mooseman60]

Dom
I'm not getting what you mean. Can you give me a step by step guide to what i need to do and where

[Domski]

If you select D20 and go to Data...Validation you will see that you currently have it set to allow a list with the source as =INDIRECT(C20). This is looking for a named range of the value selected in C20 to use as a source for the validation list. As your list for Roofing is called RoofingList you need to have the formula for the source as =INDIRECT(C20&"List")

Dom

[Mooseman60]

Dom

Thank you very much for that it works great and also for your patience. I am new to this side of Excel

[Domski]

One last point, as named ranges can't have spaced in the name you'll need to name the source for Box Gutters as something like Box_GuttersList. You data validation source will then need to be: =INDIRECT(SUBSTITUTE(C20," ","_")&"List")

Dom

[Mooseman60]

Dom

It still have a couple of problems when i select box gutters it only populates the product combo box and also if I select Zincalume Flashings it doesn't populate the combobox at all
I have attached workbook again

[Domski]

Your named ranges for your lookups need to have a _ in then rather than just removing the spaces and the formula should be =IF(D20="","",VLOOKUP(D20,INDIRECT(SUBSTITUTE(C20," ","")&"Lookup"),2,FALSE))

To be honest I'm a bit confused by the zincalume flashings bit. As the =INDIRECT(SUBSTITUTE(C20," ","_")&"List") works fine for the shorter names I can only guess that there is a limit on the length of the defined name allowed for a data validation source. Either that or I'm missing something really obvious.

Dom

[Mooseman60]

I am having problems getting the first combobox to populate the second combo box which then populates 3 other cells It works fine on one selection in the first combobox but not other selections

I have attached my workbook to show you whats happening

[Mooseman60]

I managed to solve the problem. The defined name lists and lookups cannot have spaces or underscores in them