Page 1 of 1

[Solved] Stop one part of the formula from advancing

PostPosted: Sun Oct 13, 2019 6:12 am
by Captain Crack
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 :D) 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.

PostPosted: Sun Oct 13, 2019 6:17 am
by Captain Crack
Don't worry I was not looking at my formula properly! OMG :lol:

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.

PostPosted: Sun Oct 13, 2019 6:25 am
by RusselB
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

PostPosted: Sun Oct 13, 2019 4:52 pm
by RusselB
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

PostPosted: Mon Nov 11, 2019 4:40 am
by Captain Crack
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

PostPosted: Mon Nov 11, 2019 6:32 pm
by MrProgrammer
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
(14.59 KiB) Downloaded 8 times

Captain Crack wrote:however I only want the B4 to change to B5 and so on, not the "Lots.E..."
Read section 8. Using formulas and cell references in Ten concepts that every Calc user should know.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.