Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fixing Double References
Ok, this is gonna come off as really complicated, but really all i need to do
is to take what is outputted by this formula... =IF('Shop Order Info'!B164="","",IF(COUNTIF('Shop Order Info'!$B$2:B164,'Shop Order Info'!B164)1,"",'Shop Order Info'!B164)) Which outputs "SS-4-DATPM-4-2007" and remove the "-2007" from what the formula returns. The reason i'm asking is because a formula taking the output of this formula is adding an xtra "-2007". I cannot remove the second formula's "-2007" because it's being used for a "Match( )" and removing it causes the second formula to fail and therefore not outputting correctly. (I can do it by removing it from the second formula, it just means I'd have to manually redirect the formula to the right cell every time we changed the order or added new data. Which is a real pain... __<''') I want to find a way to have it remove the "-2007" from the first formula w/o changing the data from the Shop Order Info sheet. I figure I'm asking for the near impossible, but wth... I figured I'd try. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fixing Double References
as I understand you want to get a result without -2007 part, so try this
formula =IF('Shop Order Info'!B164="","",IF(COUNTIF('Shop Order Info'!$B$2:B164,'Shop Order Info'!B164)1,"",(LEFT('Shop Order Info'!B164,LEN(A13)-5))) "Andrew M." wrote: Ok, this is gonna come off as really complicated, but really all i need to do is to take what is outputted by this formula... =IF('Shop Order Info'!B164="","",IF(COUNTIF('Shop Order Info'!$B$2:B164,'Shop Order Info'!B164)1,"",'Shop Order Info'!B164)) Which outputs "SS-4-DATPM-4-2007" and remove the "-2007" from what the formula returns. The reason i'm asking is because a formula taking the output of this formula is adding an xtra "-2007". I cannot remove the second formula's "-2007" because it's being used for a "Match( )" and removing it causes the second formula to fail and therefore not outputting correctly. (I can do it by removing it from the second formula, it just means I'd have to manually redirect the formula to the right cell every time we changed the order or added new data. Which is a real pain... __<''') I want to find a way to have it remove the "-2007" from the first formula w/o changing the data from the Shop Order Info sheet. I figure I'm asking for the near impossible, but wth... I figured I'd try. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fixing Double References
The -5 oddly enough seemed to be a bit much and cut it farther than I think u
or i would expect it to...cut it to "SS-4-DAT", so i dropped it to 1 and got "SS-4-DATPM-4" :) The formula works correctly, it cuts off the extra -2007 w/o messing up either formula, So instead of having to mess with formula direction, it's just getting it to cut to the right length...which loads easier to do. So, yes, thank you very much Eva. Your answer works great. "Eva" wrote: as I understand you want to get a result without -2007 part, so try this formula =IF('Shop Order Info'!B164="","",IF(COUNTIF('Shop Order Info'!$B$2:B164,'Shop Order Info'!B164)1,"",(LEFT('Shop Order Info'!B164,LEN(A13)-5))) "Andrew M." wrote: Ok, this is gonna come off as really complicated, but really all i need to do is to take what is outputted by this formula... =IF('Shop Order Info'!B164="","",IF(COUNTIF('Shop Order Info'!$B$2:B164,'Shop Order Info'!B164)1,"",'Shop Order Info'!B164)) Which outputs "SS-4-DATPM-4-2007" and remove the "-2007" from what the formula returns. The reason i'm asking is because a formula taking the output of this formula is adding an xtra "-2007". I cannot remove the second formula's "-2007" because it's being used for a "Match( )" and removing it causes the second formula to fail and therefore not outputting correctly. (I can do it by removing it from the second formula, it just means I'd have to manually redirect the formula to the right cell every time we changed the order or added new data. Which is a real pain... __<''') I want to find a way to have it remove the "-2007" from the first formula w/o changing the data from the Shop Order Info sheet. I figure I'm asking for the near impossible, but wth... I figured I'd try. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add excel horizontal & vertical ruler | Excel Worksheet Functions | |||
Excel CSV file: How to preserve double quotation mark on Unix ftp? | Excel Discussion (Misc queries) | |||
Changing cell references in formulas to names and back again. | Excel Discussion (Misc queries) | |||
Double clicking in a Pivot Table, Please HELP | Excel Discussion (Misc queries) | |||
Calculating p-value from Fisher's Exact Test | Excel Worksheet Functions |