CODEWITHSUNDEEP
excelfunctionnested
Annie
Annie

Reputation: 15

nested indirect function in excel 2010

in our office we have a shared excel, where everyone has their own page to work on.(Shared) also we have a form from which is fed by the shared file. (FORM)

in the FORM, i had created the formulas with indirect so the user can simply write their name in Cell A5, and write the row number from his shared page (Cell A12), and the form would update with those data from shared.

however, we have come to the realisation that often we will need to change the Shared excel's name. to shared1 or 2 or so on.

i tried adding a second nested indirect where the name of the shard excel would be added in cell J2, instead of me going into all the formulas and changing the name of the excel manually. (got a ref error)

here is the formula that currently works, without the second indirect. i am trying to make it with two indirects so in J2 will be added the name of the excel which will change often:

=VLOOKUP(A12,INDIRECT("'[_sharedpo1.xlsm]"&$A$5&"'!$A:$U"),3,FALSE)

logic: lookup (row index, in page of sharedpo1 User A5, from 3rd column, exactmatch)

_sharedpo1.xlsm is the excel's name, which previously was _sharedpo.xlsm and god knows when it might be _sharedpo2.xlsm - and must be indirected.

Upvotes: 1

Views: 5417

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

No need for a second INDIRECT, just build the whole string in the first one

Assuming
- A5 holds Sheet Name
- A4 holds Workbook name without []
- shared workbook is open
- A12 hold lookup value

Use

=VLOOKUP(A12,INDIRECT("'[" & A4 & "]" & A5 &"'!$A:$U"),3,FALSE)

You could go further

  • A6 holds lookup range $A:$U
  • Build indirect string in A7 ="'[" & A4 & "]" & A5 &"'!" & A6 so you can verify it

Formula

=VLOOKUP(A12,INDIRECT(A7),3,FALSE)

Upvotes: 1

Related Questions