VLookup in VBA using data validation list

[chriscusick]

Hi,

Apologies if this explanation is a little long winded but I want to make sure I cover everything in one go.

I have a spreadsheet which uses data validation lists to fill in certain cells. As the data validation continues down the line the next list changes appropriately.

What I am trying to achieve is that once Cell G4 (in the example attached) is populated, a value is returned using Vlookup in Cell Q4.

Now, here is the problem:

The possible combinations from the data validation lists is quite large and the values are ALL different (See below for the lists) so the vlookups need to first reference cell E4, Then F4, then finally G4 to get the correct value.

Possible list outcomes:

Cell E4 Cell F4 Cell G4
A and M Self Gen Cav, loft, boiler
A and M Warm Lead EPC, GDA
Wspace Self Gen Cav, Loft, Dual
Wspace Warm Lead ECO GDA
EUM Self Gen CDSO Dual, CERO, CERO Dual, CERO Loft, CSCO, CSCO Loft, EPC, HHRCO, HHRCO Dual, HHRCO Loft
HIM Warm Lead CERO, CERO Loft, CERO Dual, HHRCO, HHRCO Dual, CSCO, CSCO Loft, CSCO Dual, Telesale

As you can see, its a fair amount of information (all of this is in the example sheet as well) which using any kind of nested formula just won't work.

So, to recap I need a vlookup that will give me the correct value for cell G4, from the possible outcomes above and place it in cell Q4. It also needs to be independent for each row, since it is possible to have "A and M, Self Gen, Cav" in row 4, but "HIM, Warm Lead, CERO Dual" in row 5.

I hope all of this made sense? Apologies if it didnt but I am not much good at wording things on a keyboard. The vlookup tables are in sheet 2 of the uploaded spreadsheet, if they are any help


Company Sheet New.xls

[peterrc]

Hi chriscusick,

Before I go any further with this, are the values shown (in attached PDF) in column Q correct based on the selections in Columns E, F and G ?
Please advise.

peterrc

[chriscusick]

Hi peterrc,

Yes the figures in column Q are correct based upon the selection in E,F and G, as per the PDF file you attached

[peterrc]

Hi chriscusick,

Have a look at the attached - I think it's what you are looking for.
Please note:-
1. Column E now includes a reference to Warm Lead & Self Gen
2. Column F now automatic - no need to select from a drop down list
3. Sheet2 no longer needed - all details now on the INFO sheet

Any queries let me know.

Regards

peterrc

[chriscusick]

Peterrc this is amazing thank you SO very much for this.

I admit I have NO idea what you have done in column Q...it means nothing to me, but thank you for doing this, it's PERFECT!

Would I be right in assuming the references in column E were needed for the formula you wrote? I'm not complaining, just trying to understand why the "Warm lead" and "self gen" references were added in there.

Again, thank you so much

Regards

Chriscusick

[peterrc]

Hi Chriscusick,

Thanks for such a positive response - glad I could help.

I have attached another spreadsheet which hopefully explains how your spreadsheet works.

Regards

peterrc

[peterrc]

Hi Chriscusick,

The cell reference (for the formula) in the attachment I sent you should read B27 not B26.

Regards

peterrc

[chriscusick]

peterrc, Thank you for the additional worksheet that explains the formula to me, it was very kind of you to send it and so informative! I have to admit the formula was confusing me a bit.

One final question on something that is bugging me: I added a final column and entered a basic sum function: =SUM(Q4-R4) but for cells that are empty (no info in Q or R) it returns the #VALUE error, which I cannot get rid of. if there are values then the function works.

Why is this happening? How do I get the cell to return either £0.00 or just stay blank?

[peterrc]

Hi Chriscusick,

As you may know Excel can show a number of error messages, #NA!, #VALUE, etc.
ISERROR is away to prevent these errors showing.
Please see the attached - it will resolve your problem.

Regards

peterrc

[chriscusick]

Peterrc you are a LEGEND. Thank you again so very much for this. I knew there was a way to prevent these errors from showing but I couldn't figure it out lol. I think I am officially having a blond week!!

Again, you have been extremely helpful with this and I cannot thank you enough!