Coming unstuck with Formula using multiple values

[MichaelH2912]

Good Morning all,

I'm a complete novice at excel... I'm learning as I go, and I think getting there. However I've hit a wall and struggling to get around it.

I have a calculator for work built into excel. This calculator (there will be multiple calculators for different products) is used for pricing non-standard products. I've been able to get everything working to the best of my limited excel capabilities, however there is one thing I need help with:

The final price on my sheet should be based on a combination of two values. I'll break it down so it is hopefully easier to understand:

Data:

D69 - D73 have products listed, for this example "Product A" through to "Product E".
F69 - F71 has currency: "GBP", "Euro", "USD"
Each product (A through E) has a parts list of all components and costs that work out a price, then a total price, so there are a total of 15 cells that have a price calculated, for arguments sake lets say they are F40 - F55.

The main part of the calculator is made up of validation lists. So under the "Product" section, you select one of the 5 products (A through E), and in H23 you select the currency. There are other conditionals that affect the parts lists, but they are irrelevant to what I'm trying to do here.

Formula:

The Formula I'm trying to achieve is in cell J32, and it should read something like this:

If D23 = "Product A" & H23 = "GBP", then the price shown in J32 is taken from cell F40
If D23 = "Product A" & H23 = "EURO", then the price shown in J32 is taken from cell F41
If D23 = "Product A" & H23 = "USD", then the price shown in J32 is taken from cell F42
If D23 = "Product B" & H23 = "GBP", then the price shown in J32 is taken from cell F43

and so on...

Any ideas/suggestions? I'm really struggling to make this work, but I know if should be possible

Thanks,

Michael

[MichaelH2912]

Update... I've managed to get some of it done, however I have more variations than the nested formula's allow. I can trim it down to 8 results, but that is still too many.

And the other issue is I'd like the currency to show in J32, but all I get is a number. Again, I'm sure this can be done, but I'm not smart enough to do it

[ukmikeb]

Hi

Consider :-

Please Login or Register  to view this content.

and you could concatenate the currency like :-

Please Login or Register  to view this content.

hth

[MichaelH2912]

Hi Mike,

thanks for the response... but I can't seem to get the code working I must be doing something wrong.

[ukmikeb]

Hi

Sorry, perhaps this :-

Please Login or Register  to view this content.

which should give the correct row displacement.

[MichaelH2912]

Thanks Mike; I managed to find a way to work around it:

I broke everything down to four categories.. in four cells I put simpler nested logical arguments to display a price based on the calculations, and then in my price box I put a formula whether the result should be selected from cell 1, 2, 3 or 4 based on the top level product that had been selected (so no need to worry about any currency variants)

I do have one particular question though... how do I get these to put through with the correct currency symbol? Just add in the concatenate formula you have put above?

[MichaelH2912]

... I meant to say, the pricing which it all stems from have the currency symbol in the cell, but on any other cells that pull this through using a formula don't seem to pull the symbol through... is there a way to make that pull through too? It would be easier for me if it could

[ukmikeb]

Hi

Just concatenate H23 using :-

Please Login or Register  to view this content.

if you want the Currency symbol before the price or :-

Please Login or Register  to view this content.

hth

if you want the Currency symbol after the price.