Excel 2

[ashtonp2]

Hi

Can anyone advise me how I can write a formula which makes a cell equal to a1 if the contents of another cell A1 in another sheet equals 1 and b2 if the contents of the same cell equals 2. I need to be able to drag the formula down so on the next row the formula then equals a3 or b3 etc based on the same cell lookup.

Hope this makes sense (suspect not )

Paul

 

[ashtonp2]

Ps I should have said I have tired to do this with an if statement but the problem is that I actully need to do this for 12 columns and the formula is too long with 12 if statments

 

[SkipVought]

Hi,

Well it might be a good idea is you state the ENTIRE problem.

Are these 12 contiguous columns? If so
[tt]
=VLOOKUP(A1,Sheet2!$a$1:$I:1,2,false)
[/tt]
returns the value from sheet2 row1 column B (2)

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[carrr]

Try VLOOKUP:

http://www.ozgrid.com/Excel/excel-vlookup-formula.htm

http://www.contextures.com/xlFunctions02.html

Tired of waiting for an answer? Try asking better questions. See: faq222-2244

 

[ashtonp2]

Hi
The formula I started to write is

=IF('Lookup tables'!$C$51=1,T2,IF('Lookup tables'!$C$51=2,U2,IF('Lookup tables'!$C$51=3,V2,IF('Lookup tables'!$C$51=4,W2,IF('Lookup tables'!$C$51=5,X2,IF('Lookup tables'!$C$51=6,Y2,IF('Lookup tables'!$C$51=7,Z2,IF('Lookup tables'!$C$51=8,AA2,0))))))))

I need to be able to drag the formula down but as there is a limit to the size of the formula I can not make it long enough. Basically I have 12 months of data in 12 columns i want to make column 13 equal to a particular column depending on what people select from a drop down menue i.e. it would equal column 1 if they they select January, however i need to be able to drag the formula down so column 13 row 1 is a1 if jan and row 2 is a2 if jan and so on

 

[SkipVought]

The take a look at the OFFSET formula.

The entered values, 1, 2, 3, etc would be the COLUMN OFFSET (argument 3)
[tt]
=OFFSET('Lookup tables'!$A$2,ROW()-2,$F$1-1,1,1)
[/tt]
where $F$1 is where the 1, 2, 3 selection is.

Copy this formula down

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[jdhilljr]

If you have less than 29 options you can use choose.

=CHOOSE(a1,B1,c1,d1,e1,f1)

Jim

 

[ashtonp2]

Thanks Skip and Jim both formulas worked great.
Again thanks this really helped
Paul