HELP - Referencing cells from different sheets using fixed increment

Hi,

Something like this copied down.

Formula:

Please Login or Register  to view this content.

You'll need to adjust the ROW() reference as necessary. This assumes the formula starts in C1. Were ut to start in say C3 you'd need to 'rebalance' it with a -2 correction. i.e.

Formula:

Please Login or Register  to view this content.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hi,

See attached where I've created a copy of Sheet1 and added the formulae.

However note that your last invoice is not consistent with the others. This should start on row 161 (i.e. 40 rows on from the previous invoice) and not row 163.

I obviously don't know precisely how you operate this system but if as I suspect you are using it to create invoices to issue can I suggest that you do it differently.
Create a proper database of invoices and have a single row for every invoice, with each piece of data occupying a different column.

Then create a template workbook in which you will manually enter a particular invoice number. The other cells in the Invoice template will all be VLOOKUP formulae which look up the relevant column from the new database.

You'll simplify things immensely and moreover you've then got the basis for a Pivot Table where you can analyse your data in ways you've not even thought of yet.

See the attached which contains a typical database based on your invoices.

You could either manually add each row, but given that there are repeats of information like the date, invoice no, etc, a smarter way to do it would be to use a front end menu, which would simply be a blank template of your current invoice.

When creating a new set of records for the database a macro would first clear the invoice template, then after entering details much as you do now, you would click a button and the details from the template would be copied to the database, and the repeated items copied to as many rows as necessary.

The template could also be used to suck data out of the database when you want to recreate an invoice. And of course a pivot table based on the data will allow you to dice and slice the data and allow you to analyse statistics for management reporting should that be required.