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

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

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.
Last edited by Captain Crack on Sun Oct 13, 2019 6:22 am, edited 1 time in total.
Struggling but I will get there!
Captain Crack

Posts: 9
Joined: Thu Sep 11, 2014 11:29 am

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

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
Struggling but I will get there!
Captain Crack

Posts: 9
Joined: Thu Sep 11, 2014 11:29 am

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

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.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 6062
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

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.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 6062
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

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
Struggling but I will get there!
Captain Crack

Posts: 9
Joined: Thu Sep 11, 2014 11:29 am

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

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

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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 3981
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Who is online

Users browsing this forum: No registered users and 8 guests