Is there a way to write an Excel function so it can read a variable and use
it as a reference?

For instance, to read a value from cell C1 in Sheet2 to A1 in Sheet1 I would
write the following function in A1:
=Sheet2!C1

But let's say I programmed a calculation that will give me an answer to cell
B1 in Sheet1, where the answer could be either Sheet2, Sheet3 or Sheet4. How
do I write the new function in cell A1 so it will use the result in B1 in
it's function, for instance;
=(Value in B1)!C1

Use: =INDIRECT(B1&"!C1")

Not wishing to be too pedantic but: 'function' refers to things like SUM or
AVERAGE and these are built in to Excel or written by the user with VBA.
What you are talking about is a 'formula'. SO =SUM(A1:A10) is a formula
using the SUM function.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Wildebraam Production" <Wildebraam
wrote in message ...
Is there a way to write an Excel function so it can read a variable and
use
it as a reference?

For instance, to read a value from cell C1 in Sheet2 to A1 in Sheet1 I
would
write the following function in A1:
=Sheet2!C1

But let's say I programmed a calculation that will give me an answer to
cell
B1 in Sheet1, where the answer could be either Sheet2, Sheet3 or Sheet4.
How
do I write the new function in cell A1 so it will use the result in B1 in
it's function, for instance;
=(Value in B1)!C1

If Sheet1 A1 = "Sheet2"

The below formula will return A1 of Sheet2

=INDIRECT(A1 & "!A1")

If this post helps click Yes
---------------
Jacob Skaria


"Wildebraam Production" wrote:

Is there a way to write an Excel function so it can read a variable and use
it as a reference?

For instance, to read a value from cell C1 in Sheet2 to A1 in Sheet1 I would
write the following function in A1:
=Sheet2!C1

But let's say I programmed a calculation that will give me an answer to cell
B1 in Sheet1, where the answer could be either Sheet2, Sheet3 or Sheet4. How
do I write the new function in cell A1 so it will use the result in B1 in
it's function, for instance;
=(Value in B1)!C1

Some sheet names need to be surrounded with single quotes.

=indirect("'" & a1 & "'!c1")

If the single quotes aren't required, this'll still work.



Wildebraam Production wrote:

Is there a way to write an Excel function so it can read a variable and use
it as a reference?

For instance, to read a value from cell C1 in Sheet2 to A1 in Sheet1 I would
write the following function in A1:
=Sheet2!C1

But let's say I programmed a calculation that will give me an answer to cell
B1 in Sheet1, where the answer could be either Sheet2, Sheet3 or Sheet4. How
do I write the new function in cell A1 so it will use the result in B1 in
it's function, for instance;
=(Value in B1)!C1



--

Dave Peterson