Linking SpreadsheetsSolved/Closed

Question

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

rizvisa1 · Answer

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

Linking Spreadsheets

1 response

Similar discussions

Subscribe To Our Newsletter!