joelsilsbee

Please guide me to get the following linking problem solved :

One table List containing fields in sheet#1.

Prepared another sheet #2 in a form pattern using say for Labelling purpose, which has 5 rows altogether, with borders. The corresponding fields are linked to the sheet#1

Now the question is, when i copy the 1st set of form (in sheet#2) which contains 5 lines to generate 100 sets, the forms/pattern/links all are obviously copied, but the links are taken obviouly incremental from sheet #1. As my sheet#2 has 5 lines, i need to get the data by skipping off those lines.

Currently i have to do manually, for that can anyone give me good a solution pls?

I afraid, if you think i have note explained my question clearly, pls see the attached sample sheet updating with a sample file... i have changed the font colour to Red, which i require your assistance pls.

thanks

Attached Files

sample sheet.rar (4.6 KB, 3 views)

joelsilsbee

Can anyone answer this please???

David M58

By modifying some formulas on Sheet2, you can copy and paste to create additional labels on Sheet2 and have the references to Sheet1 remain correct.

Here's one way to do it. On Sheet2, place the following formula in cell A4 (right underneath the NO heading):
=INDIRECT("Sheet1!A"&((ROW()+2)/6)+2)

Place the following formula in cell B4 (right underneath the DATE heading):
=INDIRECT("Sheet1!B"&((ROW()+2)/6)+2)

For cell D4:
=INDIRECT("Sheet1!C"&((ROW()+2)/6)+2)

For cell F4:
=INDIRECT("Sheet1!E"&((ROW()+2)/6)+2)

For cell D5:
=INDIRECT("Sheet1!D"&((ROW()+1)/6)+2)

For cell F5:
=INDIRECT("Sheet1!F"&((ROW()+1)/6)+2)

For cell D6 (to the right of the Wickdrain area heading):
=INDIRECT("Sheet1!G"&((ROW())/6)+2)

Now, when you copy the range A3:F8 and paste it to cell A9, the correct data for label 2 should be displayed. When you copy the range A9:F14 and paste it to cell A15, it should pick up the data for label 3.

joelsilsbee

thanks a lot