Column lookup based on cell content

[Jkaen]

Hi all,

I am stuck trying to implement a function into excel. The part I am stuck on is using a lookup based on a cells content. I assume I want some use of the indirect function but cant quite make it to work.

The main issue I have is if the cell content of f2 is 1, I want to lookup D531, if its 2 then I want to lookup E5:E31 etc.

The array I am trying to enter this change into is:
{=IF(ISERROR(INDEX('Gantt Chart'!$A$5:$AB$31,(SMALL(IF('Gantt Chart'!$D$5:$D$31>0,ROW('Gantt Chart'!$A$5:$A$31)),ROW(1:1)))-4,1)),"",INDEX('Gantt Chart'!$A$5:$AB$31,(SMALL(IF('Gantt Chart'!$D$5:$D$31>0,ROW('Gantt Chart'!$A$5:$A$31)),ROW(1:1)))-4,1))}

The trimmed back spreadsheet is attached.

Can anybody advise how to amend it?

Thanks

 

[Jkaen]

Nevermind I managed to solve this in the end, for anybody else looking, solution is:

{=IF(ISERROR(INDEX('Gantt Chart'!$A$5:$AB$31,(SMALL(IF(INDIRECT("'Gantt Chart'!R5C"&$G$2+3,0):INDIRECT("'Gantt Chart'!R31C"&$G$2+3,0)>0,ROW('Gantt Chart'!$A$5:$A$31)),ROW(1:1)))-4,1)),"",INDEX('Gantt Chart'!$A$5:$AB$31,(SMALL(IF(INDIRECT("'Gantt Chart'!R5C"&$G$2+3,0):INDIRECT("'Gantt Chart'!R31C"&$G$2+3,0)>0,ROW('Gantt Chart'!$A$5:$A$31)),ROW(1:1)))-4,1))}

 

[IDS]

There must be a simpler way!

Could you give more details of what you are trying to do.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

FYI, Analyzing data in a Pivot (your Gantt) is very awkward. Best to Normalize your data, where you can use Excel's many data analysis tools effectively.

Here's your normalized data for the tirst 3 Activity Nos...
[pre]
Activity No Activity Week Value
01 A 1 1
01 A 2 1
02 B 4 1
02 B 5 1
02 B 6 1
02 B 7 1
03 sdfg 3 1
03 sdfg 4 1
03 sdfg 5 1
03 sdfg 6 1
03 sdfg 7 1
[/pre]
...using the technique in this FAQ on Tek-Tips:

http://www.tek-tips.com/faqs.cfm?fid=5287

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}