Lookup a tab in an external worksheet

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I have a spreadsheet called "bob" populated with hundreds
of tabs. I want to copy *all* the contents of a specific
tab from "bob" into another spreadhseet called "fred"
using a formula where I provide the tab name.

Nothing I've tried comes close to working.

Any ideas or examples?
 
So you have a worksheet named Mary in a workbook named Bob. And you want to
copy all the data (using formulas) to a different worksheet in a workbook named
Fred???

If yes, then open the Bob workbook.
go to the worksheet in Fred that you want the values:

Type =
then back to Sheet Mary in Workbook Bob.
point at A1.

You'll get a formula like:
=[bob.xls]Mary!$A$1
But this'll return a 0 if that corresponding cell is empty. This may be better:
=IF([bob.xls]Mary!$A$1="","",[bob.xls]Mary!$A$1)
In fact, get rid of the $ signs (so you can copy down and across:
=IF([bob.xls]Mary!A1="","",[bob.xls]Mary!A1)

Drag down and across until you've painted the used range.

If you close the workbook Bob, then your formula will include the drive and
folder:
=IF('C:\My Documents\excel\[bob.xls]Mary'!A1="","",
'C:\My Documents\excel\[bob.xls]Mary'!A1)
(one cell)
 
Back
Top