CODEWITHSUNDEEP
excelexcel-2007worksheet-functionworksheet
Fahad Ali
Fahad Ali

Reputation: 45

Excel - Inserting cell value in formula column name

I have two sheets, in first sheet the linked references value are there in columnA which has random values like 7000, 231, 799 etc

I want to insert all those linked values from Sheet2 into column in sheet1,

so those values are accessible with these codes, for example if references values are 7000, 231,799 then

i can access the values from sheet to with this formula

=sheet2!B7000

=sheet2!B231

=sheet2!B799

so I am directly trying to put the value in formula like this

=sheet2!B<value of A1>
=sheet2!B<value of A2>
=sheet2!B<value of A3>

i tried so many things which gives me error with #NAME?

please suggest me a solution.

Upvotes: 0

Views: 146

Answers (2)

Rincewind
Rincewind

Reputation: 138

=INDIRECT ("sheet2!B"&sheet2!A1)

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34180

You can use INDIRECT

=INDIRECT("sheet2!b"&A1)

but I prefer to use INDEX because INDIRECT is volatile and gives you certain problems like always having to save the workbook whenever you close it

=INDEX(Sheet2!B:B,A1)

Upvotes: 1

Related Questions