CODEWITHSUNDEEP
excelexcel-formulaworksheet-function
Luis E. Prado
Luis E. Prado

Reputation: 2151

How do I use the result of one function in another function to get the value stored in a cell in another sheet in Excel?

In my current sheet I have some numbers in a column, which represent the row which I want to get the data from in another sheet. And I want to get it from the same column in which I am using the function...

I know using =Sheet1!A1 for instance gets me what is in A1 on Sheet1

and

=CONCATENATE("A",A1) being on sheet two, brings me back A + whatever value is stored in A1 on sheet number two... for simplicity let's say it's a one... so it would return A1

I am on Sheet2

I'm trying

=Sheet1!CONCATENATE("A", A1)

but the formula contains an error, I've tried rewriting this in many ways but it never works... any idea what the correct syntax I need is?

Greatly appreciated!

THanks

Upvotes: 0

Views: 2087

Answers (2)

Jerry
Jerry

Reputation: 71538

As Magicianeer said, you have to use the INDIRECT function. For your example:

=INDIRECT(CONCATENATE("Sheet1!","A",A1))

Should give the results you need.

However, it's a bit lengthy, and you can use & instead of CONCATENATE, and you can directly use Sheet1!A:

=INDIRECT("Sheet1!A"&A1)

What is evaluated in the brackets is Sheet1!A1, and INDIRECT converts this from text to a reference.

Upvotes: 2

Padawan
Padawan

Reputation: 313

Try:

=CONCATENATE("A", Sheet1!A1)

Upvotes: -1

Related Questions