Excel Function/Formulae

[GazzaN]

I want to use a cell to store a number. Then I want a formulae that will take this number and use it as a sheet name.

For example, the number to be used is a Year and I have a worksheet for each year. I have a whole load of data analysis on each sheet and so I want to be able to copy a new sheet from an existing one, rename it to the relevant year and add this year to the data sheet. The formulae should then take this value as a sheet name and so all the calculations should work correctly.

I could code it but wondered if anyone knew a way to o this using any built-in functions?

 

[AnotherHiggins]

Check out the INDIRECT function.

Example:
Cell A1 = 2004 (worksheet name)

formula in another cell might look like
[COLOR=blue white]=SUM(INDIRECT("'"&A1&"'!b2:c10"))[/color]


[tt]_____
-John
[/tt]Help us help you. Please read FAQ181-2886 before posting.