I have an invoice that currently takes all the data from a reservations excel document, is there any way I can get the invoice to open with the exact row that I'm working on. At the moment my invoice collects the data as follows:

='C:\My Documents\folder\[filename.xls]Other'!$U$4

I then do a find all / replace for the $4 to what ever row I'm working on.

So either a link/button on the reservations sheet that will open the correct invoice.

OR

Change the link below on the invoice so that the $4 is changed in 1 place on the invoice document and updates all the other 29 links.

='C:\My Documents\Costa & Sierra\[Reservations2010.xls]Other'!$U$4

I've tried adding $4 in another field in the invoice and taking that like this:

='C:\My Documents\Costa & Sierra\[Reservations2010.xls]Other'!$U&(g1)

Field g1 = $4

Thanks for any suggestions.

Ian

Look up the INDIRECT function in Excel Help and try these untested suggestions: =INDIRECT("Sheet2!$A$" & G1)

Where G1 contains a number (or a formula that evaluates to a number)

=INDIRECT("Sheet2!$A$" & ROW())

Where the ROW() function will return the number of the Row that the formula is in.

e.g. if the formula is anywhere in Row 3, it will evaluate to

=Sheet2!$A$3

Ask Your Question

Weekly Poll