Text-Based Reference (indirect?)

[mlschop]

Here's the deal:

I have two spreadsheets - report and data. The report needs to pull appropriate data from the data spreadsheet. The data spreadsheet has multiple worksheets (tabs), labelled numerically - 1, 2, 3, etc.).

The report contains a column that includes the worksheet reference to data. Is there a way be able to write a formula so that I can just reference this column to pull from the correct worksheet?

I tried using indirect, and it didn't work (assuming maybe it can't work outside a single sheet). Any suggestions?

This is what I tried:

Cell A1 contains the worksheet reference - B1 contains the cell in the data worksheet i'm looking to pull over.

=indirect("'C:\Documents and Settings\username\Desktop\[data.xls]"&A1&"'!$B$1")

[mlschop]

ok - i got indirect to work...but it will only work with the destination, "data", workbook open. as soon as i close it, i get #REF errors. anyway to get functionality with a closed worksheet?

[Brad Gile]

Originally Posted by mlschop

ok - i got indirect to work...but it will only work with the destination, "data", workbook open. as soon as i close it, i get #REF errors. anyway to get functionality with a closed worksheet?

If you want to use data in a closed workbook, you have to establish a link to it. Strong advice: DO NOT DO THIS! Links are inherently evil. If you do, you just ask for major trouble later on. Instead, keep what you have using the INDIRECT function, but put code in the Workbook_Open event of ThisWorkbook in your work book that automatically opens the workbook you want to use data from.



Brad

[Random Poster]

You could use the GetValue function. However, it cannot be used in a worksheet formula. You could create a VBA routine to grab the values and then put the values in the desired cells.


HTH,
RP

[Emily]

Why do you want the workbook to be closed? If you want data from a workbook, what's wrong with having to have the workbook open? I've always thought of it as a nice feature. It forces you to know where your data is coming from.

[Old Timer]

INDIRECT.EXT from the MOREFUNC.XLL add-in

[mlschop]

Originally Posted by Old Timer

INDIRECT.EXT from the MOREFUNC.XLL add-in

is the MOREFUNC.XLL add-in available via microsofts homepage as a download or something?

[Old Timer]

Try here.

It is not from MS.