Page 1 of 1

### [Solved] Stop one part of the formula from advancing

Posted: Sun Oct 13, 2019 6:12 am
Hi, again...

I'm now struggling with a slightly different part of the formula that I was using the other day.

=IF(B4="1A";Lots.E3;IF(B4="1B";Lots.E4;IF(B4="1C";Lots.E5;IF(B4=2;Lots.E6;IF(B4=3;Lots.E7;IF(B4=4;Lots.E8;""))))))&IF(B4=5;Lots.E9;IF(B4=6;Lots.E10;IF(B4=7;Lots.E11;IF(B4=8;Lots.E12;IF(B4=9;Lots.E13;IF(B4=10;Lots.E14;""))))))&IF(B4=11;Lots.E15;IF(B4=12;Lots.E16;IF(B4=13;Lots.E17;IF(B4=14;Lots.E18;IF(B4=15;Lots.E19;IF( B4=16;Lots.E20;""))))))&IF(B4="17A";Lots.E21;IF(B4="17B";Lots.E22;IF(B4="17C";Lots.E23;IF(B4=18;Lots.E24;""))))

basically I am filling out a table and I want it to fill out another table based on what I am entering (I'm probably using the wrong formula ) however I've been trying to "drag-copy" the formula so it populates the rest of the table... however I only want the B4 to change to B5 and so on, not the "Lots.E..."

I have very little experience in formulas like this but if I can get it going it will make my life so much easier.

### Re: Stopping one part of the formula from advancing.

Posted: Sun Oct 13, 2019 6:17 am
Don't worry I was not looking at my formula properly! OMG

I was using \$E\$3 but I was looking at the wrong cell for the result... think its time I called it a day brain=fried

### Re: Stopping one part of the formula from advancing.

Posted: Sun Oct 13, 2019 6:25 am
I know what you mean about "calling it a day"...probably a good idea.
A quick glance at your formula makes me think it could be written better...however, I'm going to leave that until I've had some rest myself.

### Re: [Solved] Stop one part of the formula from advancing

Posted: Sun Oct 13, 2019 4:52 pm
Your comparator cell, B4, is the same through out the formula, thus the usage of the concatenation character (&) is redundant, since you're only going to get one match to B4.
Here's a re-write of your formula, that also uses the CHOOSE function to eliminate a lot of the IFs.
You'll have to change the B30 in my formula to B4 to match the location in your formula, as well as add the sheet name to the required references.
Code: Select all   Expand viewCollapse view
`=IF(ISNUMBER(B30);CHOOSE(B30;E5;E6;E7;E8;E9;E10;E11;E12;E13;E14;E15;E16;E17;E18;E19;E20);IF(B30="1A";E3;IF(B30="1B";E4;IF(B30="1C";E5;IF(B30="17A";E21;IF(B30="17B";E22;IF(B30="17C";E23;IF(B30=18;E24;""))))))))`

This would've been a lot easier (ie: could use just the CHOOSE function) if all of your entries were strictly numeric.

### Re: [Solved] Stop one part of the formula from advancing

Posted: Mon Nov 11, 2019 4:40 am
Cheers for that, I've not used CHOOSE before but if I have the need to do this again, I'll definitely have a play

### Re: [Solved] Stop one part of the formula from advancing

Posted: Mon Nov 11, 2019 6:32 pm
Hi, and welcome to the forum.

Captain Crack wrote:
`=IF(B4="1A";Lots.E3;IF(B4="1B";Lots.E4;IF(B4="1C";Lots.E5;IF(B4=2;Lots.E6;IF(B4=3;Lots.E7;IF(B4=4;Lots.E8;""))))))&IF(B4=5;Lots.E9;IF(B4=6;Lots.E10;IF(B4=7;Lots.E11;IF(B4=8;Lots.E12;IF(B4=9;Lots.E13;IF(B4=10;Lots.E14;""))))))&IF(B4=11;Lots.E15;IF(B4=12;Lots.E16;IF(B4=13;Lots.E17;IF(B4=14;Lots.E18;IF(B4=15;Lots.E19;IF( B4=16;Lots.E20;""))))))&IF(B4="17A";Lots.E21;IF(B4="17B";Lots.E22;IF(B4="17C";Lots.E23;IF(B4=18;Lots.E24;""))))`

I have very little experience in formulas like this but if I can get it going it will make my life so much easier.
You might be able to use VLOOKUP, but there is not enough detail in your post to determine if that function would be appropriate. Read its tutorial. For further assistance you would need to attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

Otherwise you can use OFFSET and MATCH, as shown in my attachment. Your formula could be as simple as:
=OFFSET(Lots.\$E\$2;MATCH(B4;\$D\$2:\$D\$23;0);0) •••or•••
=OFFSET(Lots.\$E\$2;MATCH(B4;{"1A"|"1B"|"1C"|2|3|4|5|6|7|8|9|10|11|12|13|14|15|16|"17A"|"17B"|"17C"|18};0);0)
201911111012.ods