link to other sheets in formula

**Fixitwell** · 03-08-2008, 04:27 AM

Hello, this has me baffled for quite some time.

I have several "customer cards" (each one on a seperate excel sheet)
In the master file I want to select one customer by puting his name in a cell or input box.
Then I want to copy (read) the data for this customer to several cells in the master file. Who can help me thanks.

**sweep** · 03-08-2008, 05:42 AM

Hi,

Presumably, each of the worksheets has the customers name? If so, you can use the INDIRECT() function to address each sheet from a master.

Please see attachment for clarification.

**Fixitwell** · 03-08-2008, 01:54 PM

Originally Posted by sweep

Hi,

Presumably, each of the worksheets has the customers name? If so, you can use the INDIRECT() function to address each sheet from a master.

Please see attachment for clarification.

Thanks very much for you answer. This works indeed.
The only disadvantage is that I must have the customer sheet open when reading the data.
Perhaps I did not clarify correctly that each customer sheet is a seperate excel file stored on the harddisk.
It is no problem to get in the data one time by opening the customer file. However when I close the mastersheet and open it again the data is gone and has to be reread again. Is there anyway to include a path to the formula so that the data will be read automaticly.
Appreciate your input very much.

**sweep** · 03-09-2008, 05:08 AM

Hi,

Sorry for the misunderstanding.

You can't read data from a closed spreadsheet. However, there's a workaround here http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.htm for it.

**VBA Noob** · 03-09-2008, 05:18 AM

Or you could download the MOREFUNC.XLL add-in from this link

http://www.dailydoseofexcel.com/arch...sed-workbooks/

or some alterative links for code

http://www.j-walk.com/ss/excel/tips/tip82.htm

http://www.vbaexpress.com/kb/getarticle.php?kb_id=454

http://www.erlandsendata.no/english/...nvbadacwbdbado

VBA Noob

**Fixitwell** · 03-12-2008, 02:24 PM

Thread closed

Thanks for all the input.
Easiest solution for me is to use the Indirect function to read the data
and then "fix" the data by a macro that copies and pastes it as "value only"

Thanks once more for the help.