Ask Your Question
0

Dynamic formula variables [closed]

asked 2013-09-14 01:19:51 +0200

Brian gravatar image

updated 2015-10-26 22:06:55 +0200

Alex Kemp gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-26 22:07:11.690457

Comments

The index= answer below does appear to work for what I was attempting to do/describe above. Many thanks.

Brian gravatar imageBrian ( 2013-09-16 20:24:06 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2013-09-14 01:58:47 +0200

m.a.riosv gravatar image

I 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)
edit flag offensive delete link more

Comments

+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)

JohnSUN gravatar imageJohnSUN ( 2013-09-14 10:12:22 +0200 )edit

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

m.a.riosv gravatar imagem.a.riosv ( 2013-09-14 11:36:58 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2013-09-14 01:19:51 +0200

Seen: 633 times

Last updated: Sep 14 '13