Hi,
I hope you can help.
I have 2 spreadsheets, one contains a summary of invoices totals listed per month with a tab per month name, and the other contains a list of all invoices, there is a tab for each invoice number. I have links in my monthhly spreadsheet to the invoice spreadsheet, but each time I create a new month I am having to update every link to the new invoice number.
Workbook 1 - Monthly
Workbook 2 - Invoices
On the Monthly tab, I want to link various cells to a specific invoice number tab on the Invoices workbook.
Cell A2 will contain an invoice number, e.g. 12345
In Cell D4 (on the monthly workbook) I want to find the value from cell E3 on the tab named 12345 on the invoice sheet.
There are various other fomrulas on this same tab that I want to link to the same worksheet called 12345.
When I create the new month, I just want to be able to type a new invoice number into cell A2 and all the formulas update rather than having to re-point to a different invoice sheet.
I have tried INDIRECT formula, something along this lines:
INDIRECT("'[Invoice.xls]"&$A$2&"'!"&ADDRESS(4,4))
This will work if I have the Invoice sheet open, which I can live with.
I just wondered whether there is an easier way to do this and also rather than putting in ADDRESS(4,4) I'd prefer to put the cell reference from the Invoice sheet, e.g. D4.
I hope I explained that well enough!
Any help would be greatly appreciated.
Thanks
I hope you can help.
I have 2 spreadsheets, one contains a summary of invoices totals listed per month with a tab per month name, and the other contains a list of all invoices, there is a tab for each invoice number. I have links in my monthhly spreadsheet to the invoice spreadsheet, but each time I create a new month I am having to update every link to the new invoice number.
Workbook 1 - Monthly
Workbook 2 - Invoices
On the Monthly tab, I want to link various cells to a specific invoice number tab on the Invoices workbook.
Cell A2 will contain an invoice number, e.g. 12345
In Cell D4 (on the monthly workbook) I want to find the value from cell E3 on the tab named 12345 on the invoice sheet.
There are various other fomrulas on this same tab that I want to link to the same worksheet called 12345.
When I create the new month, I just want to be able to type a new invoice number into cell A2 and all the formulas update rather than having to re-point to a different invoice sheet.
I have tried INDIRECT formula, something along this lines:
INDIRECT("'[Invoice.xls]"&$A$2&"'!"&ADDRESS(4,4))
This will work if I have the Invoice sheet open, which I can live with.
I just wondered whether there is an easier way to do this and also rather than putting in ADDRESS(4,4) I'd prefer to put the cell reference from the Invoice sheet, e.g. D4.
I hope I explained that well enough!
Any help would be greatly appreciated.
Thanks