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.

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.

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.