Inserting a string in a cell reference

[Joao Andre]

down vote favorite
share [g+] share [fb] share [tw]


Dear All,

I have in a cell ("I8") the name of the sheet and I in another cell
want to get the value of a cell that is located in a different workboo
but has the same name. So I've tried:

='[Results.xlsx]'&I8&'!$P$2

but I get an error. I've tried using " but I also get an error. I'v
done this already but I haven't got the excel file with me. Any advis
is welcome.

Kind regards,

Joã

 

[isabelle]

hi João,

the INDIRECT function will work if the file " Results.xlsx" is open

=INDIRECT("'[Results.xlsx]"&I8&"'!$P$2")

if not you can use a vba solution

--
isabelle




Le 2012-04-06 20:34, Joao Andre a écrit :

 

[Don Guillett]

down vote favorite
share [g+] share [fb] share [tw]


Dear All,

I have in a cell ("I8") the name of the sheet and I in another cell I
want to get the value of a cell that is located in a different workbook
but has the same name. So I've tried:

='[Results.xlsx]'&I8&'!$P$2

but I get an error. I've tried using " but I also get an error. I've
done this already but I haven't got the excel file with me. Any advise
is welcome.

Kind regards,

João

If the source wb is closed you may just use the formula referencing the sheet. Then you could use edit>replace>oldsheetname with newsheetname