reference an excel cell in another workbook

Sounds like you  need to use the INDIRECT function and have the invoice # referenced in a separate cell.

Unfortunately, INDIRECT doesn't work when external source workbook is not open.

I suppose you could run a routine that creates the formula with the right file reference without using the INDIRECT function, pulling in the invoice # as a variable in a string that gets fed back out to the cell.

For Each cell in Range("A1:A" & Rows.Count).End(xlUp).Row

InvRef = cell.value
r = cell.row
Let Range("B"& r") = FormulaR1C1 = "=[" & InvRef & ".xls]Sheet1!R1C1"

Next Cell

This would look at all entries in column A.

Formula string will also need text for the file path. To get the syntax, set up a link between two files and then close the source file. The formula in the destination file will then change to include the filepath. The filepath will need to added into the text string within the quotes before the first square bracket [.

Hopefully gets you started.

Cheers
Rob H

Rob

I wanted to make the referenced workbook be a variable, but didn't know how to insert it.

The click method results:
='C:\business\[invoice 1001.xls]Sheet1'!$B$4

I tried to take what was created by clicking and change it to have a variable like the number from cell "A2"

='C:\business\[invoice A2.xls]Sheet1'!$B$4 --- (This opened a browse window where I had to pick the workbook manually, and I've never got it to sequentialy increase.)

In example below column A is typed in.  Row 1 is achieved through click referencing.  Row 2 is an attempt at adding the "A2" cell as a variable in the reference.

Thanks for the help.

I tried your INDIRECT method and I got it to work, but like you said you have to have all the files open at the time.  Is there any way to output that to a different sheet that wont change?

In the method you use, does your data sheet record all your past invoices?  How are you getting it to stay in the data sheet?

I want to look at my bookkeeping sheet and see all my sales for the past month, quarter, or year which ever is best.

So your going the oposite route and referencing the datasheet from the invoice?

Just input all invoice variables in the data sheet and it will populate the invoice template that you can then print only while those variables are in the correct cells on data sheet.

Do you input your info on say row 1 and then insert a new row 1 for the next invoice?  How do you get the invoice template to reference the current data your putting in and not the last data you entered?

Template has one input cell for invoice number. Its actually a validated list feeding from an existing list of references in column A of the data sheet.

All data on template are lookups based round this number. As I create a new invoice I add a row of data to the data sheet against the next reference.

If I need to reprint at a later date, I either use the pdf copy created at the time or I can recreate the invoice in excel by entering the relevant reference.

My data sheet is purely raw data. If I wanted to do some reporting, I would reference this sheet from a report summary. My monthly accounts sheet references it for income purposes, by invoice number. Mine doesn't have enough consistency of category of sales for reporting (well it does I know its all my time) but no doubt one of the items in the data set per invoice item could be category or something similar ie category, qty, description, unit price

See attached.

Cheers
Rob H

Thanks for the example,  I've started using some of your suggestions and will enter raw data on the bookkeeping sheet then have that populate the template.  

The only minor snag I had was I just wanted the invoice template to reference row 1 of the bookkeeping, thats where I enter all the data for the current template (next template I'll insert a new row 1 pushing previous sales down).

How do I get the reference not to shift down with my insert and stay with row 1?

Assuming:
headers row 1
data for new invoice row 2
previous data row 3 onwards

When you come to do a new invoice, highlight all of row 2 (keyboard shortcut Shift + space does whole row). Copy (Ctrl + C)

Now right click A3 and select Insert copied cells. This will insert a row and paste the copied data.

Now delete and/or overwrite old data in row 1 with new data.

Cheers
Rob H

Alternatively, just add new data to the bottom and have the template fed by lookups on the reference, like mine does, rather than straight links to specific cells.

Set the lookup range suitably big enough at the start so you don't have to amend it.

Or you can use the INDIRECT to set the lookup range as variable dependent on number of rows in the data table. This can be in a formula in the cell or in a formula in a range name 'refers to:' criteria. For the latter the formula in the cell then refers to the range name which will change as the data increases.

Cheers
Rob H