Dynamic Worksheet reference

  • Thread starter Thread starter javaeboy
  • Start date Start date
J

javaeboy

Thanks in advance...
I need to dynamically reference the name of a worksheet.

I currently have a spreadsheet with about 60 worksheets on it. I have
2 main worksheets that can call 1 or all 60 of the worksheets. I
currently have the worksheets called 1 - 60. I have numerous cells
that pull the information from the various sheets across the board. I
would like to exchange the '1' in the folowing function to whatever is
in let's say cell A6. I need to reference worksheet 2 in the next row,
and 3 in the next and so on. I want to be able to type a name in the
respective cell (A6, A7,...) and have the respective call work to that
worksheet.

=SUM('1'!$H$3:$H$302)

I want to have the equivalent (I know the syntax isn't correct...

=SUM('worksheet named in cell A6" !$H$3:$H$302)

I need to be able to change the value in cell A6 and have the function
look at $H$3:$H$302 in the new A6 names worksheet.

I hope that was clear.
 
Thanks Domenic.

And so fast...

That one worked great and I adapted it for another fairly simpl
function I am using. I have been trying to adapt the INDIRECT call t
work in the last of my functions:

NOTE: This is an array function, so I had to do the F2 and the
CTRL+SHIFT+ENTER to change (?) it to an array function. Whatever put
the curly braces on the function...

This is the original call using '1' as the worksheet call:

=SUM(IF('1'!$B$3:$B$302=Daily!$A$1,IF('1'!$O$3:$O$302=B$65,1,0)))

Trying to replace the '1' which again will reference the cell A6

I tried this:
=SUM(IF(INDIRECT("'"&A6&"'!$B$3:$B$302")=Daily!$A$1,IF(INDIRECT("'"&A6&"'!$O$3:$O$302")=B$65,1,0)))

If you need to see what it is doing, It looks at B3:B302 in the name
worksheet, and SUMS using the 1 or 0 in the last part of the functio
for any dates in B3:B302 that are the same as the date I have in cel
A1 of worksheet Daily where the value in O3:O302 are = to the value i
cell B65.

Do I need to move the quotes and/or the ) ?

Is it possible to use the INDIRECT function in this case?

I don't get any errors, it just doesn't seem to pull the informatio
correctly or at all???

Thank you again
 
Back
Top