Previous Topic Next Topic

Linking Spreadsheets

Solved/Closed
Dohnutter - Feb 13, 2010 at 11:00 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 13, 2010 at 01:58 PM
Hello,

I'm trying to set up a cell that will draw information from another spreadsheet using excel 2007

The spreadsheet I'm pulling the info from has an invoice on every tab, each tab being renamed to the invoice number ( sounds clatty but it works for me ). In another work book, I want to enter the invoice number and have the value for the nett amount be transfered across:

The cell currently has this formula : ='[Invoices.xls]9001'!$H$44.

I want the "9001" bit to be drawn from [income.xls]C3."income" being the spreadsheet I'm working in.

I'm probably barking up the wrong tree here, but any advice would be great

cheers

1 reply

Answer 1 / 1
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Feb 13, 2010 at 01:58 PM
It seems that at the end of the day some how INDIRECT would be required. But the catch for indirect would be that the other book needs to be opened too

One way of getting what you want can be via use of public function 

Public Function getValue(bookname As String, sheetname As String, address As String) As Variant

    temp = "'[" & bookname & "]" & sheetname & "'!" & address
    getValue = temp
    
End Function


Then in your call to function you say some thing like

=INDIRECT(getValue("Invoices.xls", C3, "$H$44"))

or get the value in the function itself
1