Using a cell value to determine which column to pul data from

[Eric Kelcher]

I was hoping there was a way if cell C4 is => a number pull from various columns in Worksheet 2 based on C4 value for all numbers within a range in Worksheet1

Currently i am having to use a lot of if/then statements to make this work correctly and I have just added 2 new payout columns it would be a lot simpler if I could write a code in Worksheet 2 that said if C4>40 use column H if >20 then use column D etc

Worksheet 1

09 Template.xls
	A	B	C	D	E
4		20	TBK
5	JohnKennedy			x	525
6	LyndonBJohnson			x	250
7	RichardNixon			x	150
8	GeraldFord			x	125
9	JimmyCarter			x	100
10	RonanldReagan			x	75
11	GeorgeBush			x	75
12	BillClinton			x	75
13	GeorgeWBush			x	75
14	BarackObama			x	75
15	11
16	12
17	13
18	14
19	15
20		35	SptBK
21	JohnBelushi			x	650
22	DanAykroyd			x	325
23	JakeBlues			x	225
24	ElwoodBlues			x	200
25	JamesBrown			x	175
26	BBKing			x	150
27	BuddyGuy			x	150
28	RobertJohnson			x	150
29	MuddyWaters			x	150
30	JohnLeeHooker			x	150
ASRA

Sample of Worksheet 2 on next post

 

[Eric Kelcher]

09 Template.xls
	B	C	D	E	F	G	H	I
2	A	B	C	D	E	F	G	H
3	500	525	550	600	650	700	750	800
4	225	250	275	300	325	350	375	400
5	150	150	175	200	225	250	275	300
6	125	125	150	175	200	225	250	275
7	100	100	125	150	175	200	225	250
8		75	100	125	150	175	200	225
9		75	100	125	150	175	190	200
10		75	100	125	150	175	185	190
11		75	100	125	150	175	180	185
12		75	100	125	150	175	175	180
13
14	Minfield	Minfield	Minfield	Minfield	Minfield	Minfield	Minfield	Minfield
15	0	16	21	26	31	36	41	46
CCS-ASRA purse

 

[Weaver]

You running a dead pool?

 

[Eric Kelcher]

looks that way doesn't it?


Forgot to mention I need this to run instant, ie a formula that can be into into cell(s) as opposed to a macro.

 

[Eric Kelcher]

This is basically what I want to happen, a formula like this in worksheet 2 that worksheet 1 would reference and would then insert the column number referenced in P4 for the forumla in worksheet 1 E5-14 then have a simlar formula in col Q that would work for Worksheet E21-30

This doesn't work and I cannot figure out a way to have the "value" of a column be a variable that I can assign based on a formula

=IF(ASRA!B4>45, P4=column9, IF(ASRA!B4>40, P4=column8, IF(ASRA!B4>35, P4=column7, IF(ASRA!B4>30, P4=column6, IF(ASRA!B4>25, P4=column5, IF(ASRA!B4>20, P4=column4, IF(ASRA!B4>16, P4=column3, column2)))))))

 

[Eric Kelcher]

Is there nothing like this VBA code that can be used directly in Excel spreadsheet???

Cells(3, CurCol)

I do have to be able to "grab" the value from another worksheet


So reality it would have to look something like this

Cells(ASRA!3, ASRA!P4)

where ASRA!3 would be my known row number ie fixed and ASRA!P4 would be my variable number for the column determined by the formula in P4.

 

[Jonmo1]

You could possibley use index

say
ASRA!P3 = 3 (row #)
ASRA!P4 = 10 (column #)

=INDEX(ASRA!A:Z,Row#,Column#)
=INDEX(ASRA!A:Z,ASRA!P3,ASRA!P4)

Would be equivelent of writing
=ASRA!J3

That may get you started.

 

[Eric Kelcher]

Awesome index does it!

I knew there had to be a way to address this