Formula or conditional formatting?

[simlark]

Hi,

I have a need to use a formula which takes data from a series of drop down lists. The issue I have is that the drop down lists also contain text which renders the formula useless.

For example:

A1 APPLES Qty 10
B1 ORANGES Qty 20
C1 BANANAS Qty 30

The formula is simply, =product(A1,B1,C1) which should give a result of 6000. I can eliminate the text however that would require me to split the cells and have extra drop down lists which makes the sheet difficult to use.

Any thoughts,

Cheers, Scott

 

[simlark]

sorry, forgot to add variously using WinXP & 97 but always Excel 2010.

 

[JazzSP8]

Hi Scott

I'm almost hesitant to reply to this as I'm sure there must be a better way, but...

This would work for your needs as described providing that your quantities are always going to be no more that 99.

=PRODUCT(RIGHT(A1,2),RIGHT(A2,2),RIGHT(A3,2))

Or, this version will just take the last part of the cell value and use that as a number so it can be any amount.

=PRODUCT(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"  ","*",LEN(A1)-LEN(SUBSTITUTE(A1,"  ",""))))),RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2,"  ","*",LEN(A2)-LEN(SUBSTITUTE(A2,"  ",""))))),RIGHT(A3,LEN(A3)-FIND("*",SUBSTITUTE(A3,"  ","*",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))))))

HTH

 

[simlark]

thanks for your reply. Tried it but seems too many arguements confuse the issue further, it confused me at least. I will revert to splitting the cells into sub lists to the text is not included in the formula.

Cheers