Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 16 Sep 2013 20:24:06 +0200Dynamic formula variableshttps://ask.libreoffice.org/en/question/22598/dynamic-formula-variables/I'm trying to generate a formula in libre that would select a column based on drop down list selection. I realize this is invalid but I think it illustrates what I'm trying to do better than my words could.
=IF($A$2=1:52,C5:BA5,)-(IF($B$2=1:52,C5:BA5,)
Which seemed to me like a logical simplification of this ridiculously long formula that I believe has too many IF's to work.
=IF($A$2=1,C5,(IF($A$2=2,D5,(IF($A$2=3,E5,(IF($A$2=4,F5,(IF($A$2=5,G5,(IF($A$2=6,H5,(IF($A$2=7,I5,(IF($A$2=8,J5,(IF($A$2=9,K5,(IF($A$2=10,L5,(IF($A$2=11,M5,(IF($A$2=12,N5,(IF($A$2=13,O5,(IF($A$2=14,P5,(IF($A$2=15,Q5,(IF($A$2=16,R5,(IF($A$2=17,S5,(IF($A$2=18,T5,(IF($A$2=19,U5,(IF($A$2=20,V5,(IF($A$2=21,W5,(IF($A$2=22,X5,(IF($A$2=23,Y5,(IF($A$2=24,Z5,(IF($A$2=25,AA5,(IF($A$2=26,AB5,(IF($A$2=27,AC5,(IF($A$2=28,AD5,(IF($A$2=29,AE5,(IF($A$2=30,AF5,(IF($A$2=31,AG5,(IF($A$2=32,AH5,(IF($A$2=33,AI5,(IF($A$2=34,AJ5,(IF($A$2=35,AK5(IF($A$2=36,AL5,(IF($A$2=37,AM5,(IF($A$2=38,AN5,(IF($A$2=39,AO5,(IF($A$2=40,AP5,(IF($A$2=41,AQ5,(IF($A$2=42,AR5,(IF($A$2=43,AS5,(IF($A$2=44,AT5,(IF($A$2=45,AU5,(IF($A$2=46,AV5,(IF($A$2=47,AW5,(IF($A$2=48,AX5,(IF($A$2=49,AY5,(IF($A$2=50,AZ5,(IF($A$2=51,BA5,(IF($A$2=52,BB5,))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
A2 and B2 are drop downs 1-52 which I would like to have the formula insert the cell value from that column/row.
Any help would be appreciated; tried to post a screen shot of the table but alas I lack the karma to upload.
Sat, 14 Sep 2013 01:19:51 +0200https://ask.libreoffice.org/en/question/22598/dynamic-formula-variables/Comment by Brian for <p>I'm trying to generate a formula in libre that would select a column based on drop down list selection. I realize this is invalid but I think it illustrates what I'm trying to do better than my words could.</p>
<p>=IF($A$2=1:52,C5:BA5,)-(IF($B$2=1:52,C5:BA5,)</p>
<p>Which seemed to me like a logical simplification of this ridiculously long formula that I believe has too many IF's to work.
=IF($A$2=1,C5,(IF($A$2=2,D5,(IF($A$2=3,E5,(IF($A$2=4,F5,(IF($A$2=5,G5,(IF($A$2=6,H5,(IF($A$2=7,I5,(IF($A$2=8,J5,(IF($A$2=9,K5,(IF($A$2=10,L5,(IF($A$2=11,M5,(IF($A$2=12,N5,(IF($A$2=13,O5,(IF($A$2=14,P5,(IF($A$2=15,Q5,(IF($A$2=16,R5,(IF($A$2=17,S5,(IF($A$2=18,T5,(IF($A$2=19,U5,(IF($A$2=20,V5,(IF($A$2=21,W5,(IF($A$2=22,X5,(IF($A$2=23,Y5,(IF($A$2=24,Z5,(IF($A$2=25,AA5,(IF($A$2=26,AB5,(IF($A$2=27,AC5,(IF($A$2=28,AD5,(IF($A$2=29,AE5,(IF($A$2=30,AF5,(IF($A$2=31,AG5,(IF($A$2=32,AH5,(IF($A$2=33,AI5,(IF($A$2=34,AJ5,(IF($A$2=35,AK5(IF($A$2=36,AL5,(IF($A$2=37,AM5,(IF($A$2=38,AN5,(IF($A$2=39,AO5,(IF($A$2=40,AP5,(IF($A$2=41,AQ5,(IF($A$2=42,AR5,(IF($A$2=43,AS5,(IF($A$2=44,AT5,(IF($A$2=45,AU5,(IF($A$2=46,AV5,(IF($A$2=47,AW5,(IF($A$2=48,AX5,(IF($A$2=49,AY5,(IF($A$2=50,AZ5,(IF($A$2=51,BA5,(IF($A$2=52,BB5,))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))</p>
<p>A2 and B2 are drop downs 1-52 which I would like to have the formula insert the cell value from that column/row.</p>
<p>Any help would be appreciated; tried to post a screen shot of the table but alas I lack the karma to upload.</p>
https://ask.libreoffice.org/en/question/22598/dynamic-formula-variables/?comment=22693#post-id-22693The index= answer below does appear to work for what I was attempting to do/describe above. Many thanks.Mon, 16 Sep 2013 20:24:06 +0200https://ask.libreoffice.org/en/question/22598/dynamic-formula-variables/?comment=22693#post-id-22693Answer by m.a.riosv for <p>I'm trying to generate a formula in libre that would select a column based on drop down list selection. I realize this is invalid but I think it illustrates what I'm trying to do better than my words could.</p>
<p>=IF($A$2=1:52,C5:BA5,)-(IF($B$2=1:52,C5:BA5,)</p>
<p>Which seemed to me like a logical simplification of this ridiculously long formula that I believe has too many IF's to work.
=IF($A$2=1,C5,(IF($A$2=2,D5,(IF($A$2=3,E5,(IF($A$2=4,F5,(IF($A$2=5,G5,(IF($A$2=6,H5,(IF($A$2=7,I5,(IF($A$2=8,J5,(IF($A$2=9,K5,(IF($A$2=10,L5,(IF($A$2=11,M5,(IF($A$2=12,N5,(IF($A$2=13,O5,(IF($A$2=14,P5,(IF($A$2=15,Q5,(IF($A$2=16,R5,(IF($A$2=17,S5,(IF($A$2=18,T5,(IF($A$2=19,U5,(IF($A$2=20,V5,(IF($A$2=21,W5,(IF($A$2=22,X5,(IF($A$2=23,Y5,(IF($A$2=24,Z5,(IF($A$2=25,AA5,(IF($A$2=26,AB5,(IF($A$2=27,AC5,(IF($A$2=28,AD5,(IF($A$2=29,AE5,(IF($A$2=30,AF5,(IF($A$2=31,AG5,(IF($A$2=32,AH5,(IF($A$2=33,AI5,(IF($A$2=34,AJ5,(IF($A$2=35,AK5(IF($A$2=36,AL5,(IF($A$2=37,AM5,(IF($A$2=38,AN5,(IF($A$2=39,AO5,(IF($A$2=40,AP5,(IF($A$2=41,AQ5,(IF($A$2=42,AR5,(IF($A$2=43,AS5,(IF($A$2=44,AT5,(IF($A$2=45,AU5,(IF($A$2=46,AV5,(IF($A$2=47,AW5,(IF($A$2=48,AX5,(IF($A$2=49,AY5,(IF($A$2=50,AZ5,(IF($A$2=51,BA5,(IF($A$2=52,BB5,))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))</p>
<p>A2 and B2 are drop downs 1-52 which I would like to have the formula insert the cell value from that column/row.</p>
<p>Any help would be appreciated; tried to post a screen shot of the table but alas I lack the karma to upload.</p>
https://ask.libreoffice.org/en/question/22598/dynamic-formula-variables/?answer=22599#post-id-22599I think you are looking for extract the value in a position inside a range:
=OFFSET(C5;0;$A$2-1)
or
=INDEX(C5:BA5;0;$A$2) Sat, 14 Sep 2013 01:58:47 +0200https://ask.libreoffice.org/en/question/22598/dynamic-formula-variables/?answer=22599#post-id-22599Comment by m.a.riosv for <p>I think you are looking for extract the value in a position inside a range:</p>
<pre><code>=OFFSET(C5;0;$A$2-1)
</code></pre>
<p>or </p>
<pre><code>=INDEX(C5:BA5;0;$A$2)
</code></pre>
https://ask.libreoffice.org/en/question/22598/dynamic-formula-variables/?comment=22611#post-id-22611Hi JohnSUN, you are right, it is for keep the first cell of referenced range, easier to understand (I think), also could avoid issues inserting a column in B e.g.Sat, 14 Sep 2013 11:36:58 +0200https://ask.libreoffice.org/en/question/22598/dynamic-formula-variables/?comment=22611#post-id-22611Comment by JohnSUN for <p>I think you are looking for extract the value in a position inside a range:</p>
<pre><code>=OFFSET(C5;0;$A$2-1)
</code></pre>
<p>or </p>
<pre><code>=INDEX(C5:BA5;0;$A$2)
</code></pre>
https://ask.libreoffice.org/en/question/22598/dynamic-formula-variables/?comment=22609#post-id-22609+1! The first parameter of the For OFFSET() can be B5: so then don't have to subtract 1 from the $A$2 **=OFFSET(B5;0;$A$2)**Sat, 14 Sep 2013 10:12:22 +0200https://ask.libreoffice.org/en/question/22598/dynamic-formula-variables/?comment=22609#post-id-22609