Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vary formula based on what another cell contains
Hello
I have a spreadsheet where I would like to create a formula that says: If cell.Offset(0, -2) contains "with GST", then formula = "cell.Offset(0, -1)/8". Elseif cell.Offset(0, -2) contains "without GST", then formula = "0" Elseif cell.Offset(0, -2) contains "ZR", then formula = "0" I'm not sure how to write the formula to look for something *contained* in the target cell, rather than checking the entire cell contents. Any help would be greatly appreciated. Thanks MMH |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vary formula based on what another cell contains
the operator is "Like"
* = none or many characters ? = exactly one char "toto" Like "*ot*" is true find more details in the vba help, but the idea is to use T$ Like "*wanted*" and this will be true since wanted is include in T |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vary formula based on what another cell contains
Whether the cell contains "without" or "ZR" the result is the same.
So I hope that the term "with GST" is at the and of the cell. Then the following formula will work. =IF(RIGHT(A1,8)="with GST",B1/9,0) Seen the lack of other responses I presume it is hard to check for any containment. -- Greetings from New Zealand Bill K "MMH" wrote in message ... Hello I have a spreadsheet where I would like to create a formula that says: If cell.Offset(0, -2) contains "with GST", then formula = "cell.Offset(0, -1)/8". Elseif cell.Offset(0, -2) contains "without GST", then formula = "0" Elseif cell.Offset(0, -2) contains "ZR", then formula = "0" I'm not sure how to write the formula to look for something *contained* in the target cell, rather than checking the entire cell contents. Any help would be greatly appreciated. Thanks MMH |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vary formula based on what another cell contains
Oops,
I have always worked with the divide by 9 in stead of 8 to find the amount of gst out of a gross amount. Your example showed divide by 8. Greetings Bill K "Bill Kuunders" wrote in message ... Whether the cell contains "without" or "ZR" the result is the same. So I hope that the term "with GST" is at the and of the cell. Then the following formula will work. =IF(RIGHT(A1,8)="with GST",B1/9,0) Seen the lack of other responses I presume it is hard to check for any containment. -- Greetings from New Zealand Bill K "MMH" wrote in message ... Hello I have a spreadsheet where I would like to create a formula that says: If cell.Offset(0, -2) contains "with GST", then formula = "cell.Offset(0, -1)/8". Elseif cell.Offset(0, -2) contains "without GST", then formula = "0" Elseif cell.Offset(0, -2) contains "ZR", then formula = "0" I'm not sure how to write the formula to look for something *contained* in the target cell, rather than checking the entire cell contents. Any help would be greatly appreciated. Thanks MMH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vary Color based on value | Charts and Charting in Excel | |||
correlation when the cell references vary | Excel Worksheet Functions | |||
how to vary cell name in formula | Excel Worksheet Functions | |||
vary the vlookup array depending on the value in a cell | Excel Worksheet Functions | |||
Vary variables in a formula via reference to another cell | Excel Discussion (Misc queries) |