[Solved] Stop one part of the formula from advancing

Discuss the spreadsheet application

[Solved] Stop one part of the formula from advancing

Postby Captain Crack » 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 :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.
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.

Postby Captain Crack » Sun Oct 13, 2019 6:17 am

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
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.

Postby RusselB » 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.
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.
User avatar
RusselB
Moderator
 
Posts: 5971
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Postby RusselB » 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.
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.
User avatar
RusselB
Moderator
 
Posts: 5971
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Postby Captain Crack » 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
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

Postby MrProgrammer » 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
(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.
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).
User avatar
MrProgrammer
Moderator
 
Posts: 3965
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 7 guests