Result based on multiple choices.

[Bob119]

Hello to all!

Sure this maybe a bit billy bob basic for some, but I have some handy excel skills but come across a situation that I can't seem to work out. I'm not specifically looking for an answer, I am happy to play around and post an answer when I have found it, just maybe need a pointer or two if someone has the time to spare.

I have a column with a list of names (basic example below) with option 1 or option 2 which contains currency. This data is in a tab called setup but doesn't really matter for this example.

On another sheet, I have 3 drop down lists that represent each column below and finally, in another cell I want the value to represent the drop down choices.
So for example, if someone picks Ford, then Option 1 the cell should return £10. If they choose Renault and option 2 it should return £25 and so on.

Prior to all of these choices, I have a drop down that is a plain yes or a no. This is at the start of my formula and overrides any of the choices regardless on what is selected and will return £0.00. (basically a pro bono option). This piece currently works for me: =IF(G5="Yes","£0.00",OR(xxxxx rest of formula here).

I can basically make 1 option work and that's by choosing Ford and Option 1. That's because I psychically target 'ford' in my formula and not a range of data. It will return £10 but not option 2. The more I bulk the formula, the more I get #Name? "True" (or) "False".

thanks for reading! -

Car	Option 1	Option 2
Ford	£10	£20
Renault	£15	£25
Land Rover	£30	£35
Audi	£40	£45

 

[Joe4]

Welcome to the Board!

Can you show us a sample of this sheet that shows the data structure of the sheet with these drop-downs, and show us what exactly you want returned to where?
I believe you posted the "source" sheet, but it would be helpful to see the structure of your other sheet with drop-downs too, and see what exactly you are trying to do.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Bob119]

Morning!
Thanks for the reply! - I upload the spreadsheet I have been playing with. Basically I have now managed to create what I am after, but the formula is chaotic. Its the only way I could seem to get it to calculate based on the choices selected. Anyway, feel free (to anyone) to download use and enjoy if it's of use to anyone. But it would be worth a little peek into the code to see if it could be improved...or not.

I haven't installed XL2BB yet, does it work on a MAC?

For now, I have uploaded it to Google Drive as a Zip file as it kept opening with Google Sheets.

Speadsheet Via Google Drive

 

[Joe4]

I haven't installed XL2BB yet, does it work on a MAC?

Yes. See: XL2BB - Excel Range to BBCode

For now, I have uploaded it to Google Drive as a Zip file as it kept opening with Google Sheets.

I am not at a location where I can download files now. I may be able to take a look at it later in the day when I am at a computer in which I can download files.

 

[Joe4]

Since you have already set up a nice lookup grid on your "Settings" tab, you can simply do a VLOOKUP for each column and add them all together.

So in your example workbook, you can put this formula in cell F6 and copy down to F9:

=VLOOKUP(B6,Settings!A$1:B$23,2,0)+VLOOKUP(C6,Settings!A$1:B$23,2,0)+VLOOKUP(D6,Settings!A$1:B$23,2,0)+VLOOKUP(E6,Settings!A$1:B$23,2,0)

 

[Bob119]

Thanks for taking a look!. - Yeah, I prefer to have static values in a setup tab as businesses change and it allows the end user to update content without messing around with the formula. That works pretty well although the pro-bono option doesn't apply anything. But I to be honest I think I might be getting stuck in my own car analogy groove as I am experimenting further it's not working how I want it to in my head.

I am working on a management system to keep track of Arena Advertising for a non-profit.

I have 4 arenas where a client can choose a 2x1 or a 4x1 board. The arena attracts a different value based on its location and chosen board size.
So I want a value cell to look at is what arena has been selected, then what size ad board is required, then generate a value for that board.

I attach a sample of the excel spreadsheet I am working with if you have the time to take a look.

Thanks!

The Other Excel Spreadsheet

 

[Joe4]

That works pretty well although the pro-bono option doesn't apply anything.

That is to be expected, as you have not entered any values for the Pro Bono option yet (in your first attachment):

I have 4 arenas where a client can choose a 2x1 or a 4x1 board. The arena attracts a different value based on its location and chosen board size.
So I want a value cell to look at is what arena has been selected, then what size ad board is required, then generate a value for that board.

That appears to be a much more complex question that your original. I suggest you post that in a new thread.
Also, please be sure to mention which version of Excel you are trying to do this in, as you have 3 different version listed in your profile (365, 2019, and 2016), and newer versions of Excel have new functions that may be able to help.

 

[Bob119]

Thanks for looking. - I did add £0.00 into the pro-bono settings field but it didn't apply a zero result. It looks like the VLOOKUP was adding the costs of each field then at the end added the pro-bono field £0.00 to the end result rather then override any other values.

No problem, I'm sure I will get there I have built complex workbooks. - Thanks for your time.

 

[Joe4]

Thanks for looking. - I did add £0.00 into the pro-bono settings field but it didn't apply a zero result. It looks like the VLOOKUP was adding the costs of each field then at the end added the pro-bono field £0.00 to the end result rather then override any other values.

Sorry, I misunderstood that part. If you want to check/override that, pull that part off and apply to an IF like you originally suggested, i.e.

=IF(VLOOKUP(E6,Settings!A$1:B$23,2,0)=0,0,VLOOKUP(B6,Settings!A$1:B$23,2,0)+VLOOKUP(C6,Settings!A$1:B$23,2,0)+VLOOKUP(D6,Settings!A$1:B$23,2,0))

 

[Bob119]

That's alright thanks for checking. I have managed to complete what I had in my head which should vastly improve things. I used

=IF(G5="Yes","£0.00",VLOOKUP(B5,Settings!A$1:B$24,2,0)+VLOOKUP(C5,Settings!A$1:B$24,2,0)-H5)

which basically means that if G5 doesn't comtain Yes, then it's false value is the VLOOKUP Formula. For good measure, I have popped on a very quick and dirty -H5 at the end which deducts anything from the final total if a discount were to be offered.