Link Partial Data To Cell If Data Meets Requirements
Hello,
I need the value from Cell C2 minus it's ending to be linked automatically with Cell I2 if and when it's ending is a '-1'.
So for example, if Cell C2 receives a 12345 or a 12345-2, it wouldn't link automatically, but if it received a 12345-1, then Cell I2 would display 12345. Upon deleting or changing Cell C2, Cell I2 would need to delete or change respectively.
Any suggestions?
There is so much good in the worst of us,
And so much bad in the best of us,
That it hardly behooves any of us
To talk about the rest of us.
Any ideas at all?
Hi,
I am not sure if the solution I have below solves your problem, but something like the formula below might get you what you are looking for.
=IF(ISERROR(FIND("-1",F12,1)),"",LEFT(F12,FIND(-1,F12,1)-1))
HTH.
thanks,
MT.
Hey there, I see I get the honour of receiving your first post! Thank you!
That works quite nicely! I see only one issue - if I enter 12345-11, 12345 is displayed in Cell I2. Is there a way that you're aware of that only allows '-1' and nothing following the 1?
Hello there,
How about something along theses lines. Would this be satisfactory?
=IF(ISERROR(FIND("-1",F12,1)),"",LEFT(F12,LEFT(FIND(-1,F12,1),10)-1))
MT
Hmm, it still seems to do the same thing as your first formula, -11's and such still get linked... is it working for you?
You are correct. The formula fails when there is an additional number to the right of the -1. May be I was hallucinating. I am not aware of a way to do this using excel formulas. I could probably figure out a way using VBA since it has the Instr and InstrRev functions which I can try and use to control the result.
are the numbers preceding the "-1" always 5 digits or could they vary? If they are always 5 digits, then we could try to check to see if the count after locating the first occurrence of -1 exceeds 7 (since the -1 will be the 7th character). So anything greater than 7 would imply that we do nothing otherwise give the numbers preceeding the -1.
Haha, no problem.
I think a macro would be the best way - or even validation, if we can. The numbers preceding the '-1' are usually 5, but could vary, so that won't do us much good.
Thanks again for your continued help..
Does anybody have any ideas here?
Would anybody have any suggestions?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks