CODEWITHSUNDEEP
excel
Display Name
Display Name

Reputation: 1035

Cell Referencing

i dont know if what im trying to do is possible,

i want to manipulate data from another sheet for example:

for example on sheet2 i would use the following formula =Sheet1!A1+Sheet1!A2

what i want to do is have a cell that contains the text Sheet1! and then have a formula that uses the text in that cell such like

sheet2 a1 = Sheet1

formauls in Sheet2 are like the following

=A1!A1+A1!A2

is this possible

thanks

Upvotes: 1

Views: 151

Answers (2)

NickSlash
NickSlash

Reputation: 5100

You can use the INDIRECT() function in your formula to build the reference.

Sheet1!A1 = "Sheet2!"
Sheet2!A1 = "Hello World"
Sheet1!A2 = INDIRECT(A1&"A1")
Sheet1!A2 == "Hello World"

Upvotes: 2

RocketDonkey
RocketDonkey

Reputation: 37279

You can use the INDIRECT formula, which will take a string an convert it into a usable range. In your case, you can try putting Sheet1 in cell A1 on Sheet2, and in in cell A2 on Sheet2, put the formula:

=INDIRECT(A1& "!A1")

This will take the value in A1 (which is Sheet1), concatenate it with !A1 and return the value at the string created - Sheet1!A1.

You can also work backwards from a cell reference to a string by using the ADDRESS formula, which takes (at a minimum) a row number and a column number and returns the text representation of the cell reference. For example:

=ADDRESS(1, 1)

will return the value $A$1 in string form, which you could then pass into INDIRECT to get the value (this is a technique that can be be used to reference cells in a flexible/dynamic way).

Upvotes: 3

Related Questions