Help with importing dynamic worksheets

[100-IAN-001]

Hi, I have several worksheets with project data in them and I have som
master pages that collect he data. They are named (started from left)
"SUMMARY, INVOICE, start, 1,2,3,4,5,6,7,8,9,10, end" On the "SUMARY
page I have several functions in cells that college data from th
designated pages (='1'!E1) but I want to do the same thing on th
"INVOICE" page but have a cell (L9) that you enter a page number and i
brings in the data corresponding with the page number. For instance i
you enter 3 then the other cells would bring in ='3'!E1, ='3'!E2
='3'!E3 and so forth. If you entered 4 then the other cells would brin
in ='4'!E1, ='4'!E2, ='4'!E3 and so forth.
In order to make this happen I tried ='L9'!E1, but it then looks for a
outside excel file called "L9". I tried ='$L$9'!E1 but I get the sam
result. The only thing that works is:
=IF(L9=1,'1'!D7,IF(L9=2,'2'!D7,IF(L9=3,'3'!D7,IF(L9=4,'4'!D7,IF(L9=5,'5'!D7,IF(L9=6,'6'!D7,IF(L9=7,'7'!D7,IF(L9=8,'8'!D7,"NA"))))))))
But as I found out you can only nest up to 8 if/then statements whic
only works with sheets 1-8. Frustrating thing is if this was in php o
sql I would just use a variable and it would plug in easy. Any idea
would be appreciated. thanks

 

[Frank Kabel]

Hi
try
=IF(AND(L9>=1,L9<=8),INDIRECT("'" & L9 & "'!D7"),"NA")

Note: won't work if L9 could contain decimals such as 2.3