LinkBack URL
About LinkBacks
In the sample file attached, when I enter 115 or 115/1 in D1, I get 115/1 in E1. Now I want 115/2 in D2 and 115/3 in D3. I don't know how to accomplish this. Could you please help me?
In the sample file attached, when I enter 115 or 115/1 in D1, I get 115/1 in E1. Now I want 115/2 in D2 and 115/3 in D3. I don't know how to accomplish this. Could you please help me?
Last edited by ajeshkdy; 08-18-2019 at 02:05 PM.
Hi, test my formula.
This is an array formula.
In 'I1' and copy down.Change for 'E1' accordingly.
Please Login or Register to view this content.
Best Regards,
Maras.
1] In D1, enter 115 or 115/1 (Or 118 or 118/1)
2] In E1, enter formula & copied right to F1, and all copied down to 14 rows
=IFERROR(VLOOKUP(LEFT($D$1,FIND("/",$D$1&"/")-1)&"/"&ROW($A1),$B:$C,COLUMN(A$1),0),"")
Regards
This works fine, but for some values it doesn't get the desired result . For example, if I enter 13 in D1, I get 115/2 and 116 along with 13, which is not desired. I have re-uploaded the sample sheet adding such instances. Kindly check the file.Originally Posted by maras_mak
Hi, test my formula.
This is an array formula.
In 'I1' and copy down.Change for 'E1' accordingly.Please Login or Register to view this content.
Thanks for your help.
Last edited by ajeshkdy; 08-19-2019 at 05:29 AM.
Corrected formula, e.g. in 'M7', copy down and to the right.Please Login or Register to view this content.
I will try this ASAP.
Regards,
Ajesh.
Last edited by ajeshkdy; 08-19-2019 at 07:34 AM.
ARRAY formula in E1 then copied across to Column F
Making slight changes use for other regions.Pl See file.Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Those instances are now corrected by this formula. But a new problem arises: When I enter certain values without a slash, it doesn't work. (Example:118). However, thanks for your effort.Corrected formula, e.g. in 'M7', copy down and to the right.Please Login or Register to view this content.
Regards,
Ajesh.
This is the perfect solution!ARRAY formula in E1 then copied across to Column F
Making slight changes use for other regions.Pl See file.Please Login or Register to view this content.
Thanks Srinivasamurthy.
Regards,
Ajesh.
Thanks for feed back and rep.
Though I marked the thread as "solved", I still need your help.
My mistake:
In the original data there are both 13 and 133. I forgot to include that 133 in the sample file. I only included 13.
What my problem is, when I enter 13 in D1, I get 133 along with 13 where I want 13 only. 133 is expected only when I enter 133 in D1.
I have re-uploaded the sample file adding this value(133).
I request you to check the file and instruct me what I should change in the formula.
Sorry for the inconvenience caused.
Regards,
Ajesh.
Last edited by ajeshkdy; 08-20-2019 at 01:58 PM.
I think it will be okay now.
Array formula in M7Please Login or Register to view this content.
That works!I think it will be okay now.
Array formula in M7Please Login or Register to view this content.
Thanks Maras
Regards,
Ajesh.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks