Creating a Template Printing Page or a clever way of copying linked cells

**SHS** · 08-06-2009, 05:18 AM

Hi,

I have an excel sheet which varies in the number of rows of data in contains on a daily basis. Each day i need to print this information in a set format. I have created another worksheet which takes this data and arranges it as i require it then i can print this worksheet.
The problem arrises that i am getting more and more data in my main worksheet which means i keep having to add more and more pages to the printing worksheet - which takes too much time and effort.

I can see two solutions to this problem, however, have no idea how to implement either.

The first solution would be to have one printable page and when i say print it takes the information off the main worksheets, populates the print template, prints it, takes the next line of information, populates the print template, prints it, etc. until it reaches a blank row.

The second solution would be that when i copy the print template down (say the first page takes cells 1 --> 30, then 31 -->60 etc.) then the linked sell in the first printable page say links to cells A1, B1 C1, D1 in the worker sheet then the second page copied down will be linked to A31, B31, C31, D31 in the worker sheet - i want it to be linked to A2, B2, C2, D2. Currently i edit the number manually, but each printable page has 25 linked cells, so, as you can imagine this is quite a long boring job.
I wonder if it is possible to change the formula =worksheet1!A1 and have something like =worksheet1![worksheet4!a1] and have worksheet 4 repointing in the right direction. Then in worksheet4 have something clever going on probably with a macro - but agan - i have no idea where to go with this idea.

I hope this makes sense to someone and someone can point me in the direction of a solution. I am happy using macros (the excel sheet currently has two in it) i just have no idea where to start on this problem.

I am also happy to upload my excel sheet if anyone wants to actually see what i'm trying to do rather than just trying to understand my explanation above!

Any help really greatfully received - even if its just to say "No, you can't do it!"

Thanks,

Mike

**maybelle** · 08-06-2009, 07:15 AM

Hi,

Without knowing what exactly you want to print- i assume you have a set number of rows that should print on top of a page and then data which can be x lines one day and y the next.

Would repeating a few rows at the top of each page be an option? View>>Header & Footer>>Sheet>>Rows to repeat at top?

M

**SHS** · 08-06-2009, 08:31 AM

Hi, thank you for your response,

let me try and make my explanation a bit clearer:

In the main worksheet i have names, addresses, quantities, items, costs, etc.

I load this information in from a CSV file.

Each row is 1 person and their associated items, etc.

I then want to print this information so the name, address and some fixed information (returns address, postage image, etc.) are on the top half of a A4 sheet, on the bottom half is the quantity, item, cost, etc.
This is then repeated several times for each line of the main worksheet, i.e. each persons order can be printed off on a seperate sheet of A4.

Say i have 100 customers one day, that means i need 100 pages to be printed off, as it stands that would mean i would have a one off task of creating a very long excel sheet with the address, item, etc. correctly linked to the rows in the main worksheet. This obviously would take a long time, as ,as it stands i have to edit the formula for each page to link to the correct cell.

I have attached the excel sheet to show what i mean - you don't have to enable macros for it to work if you are worried about security - the macros are just used for tidy up.

Thank you,

Mike

***A slightly faster though still not ideal way is to copy and paste the pages and do a search and replace to update the linked cells cell numbers, i guess this will do if there is no better solution***

**maybelle** · 08-13-2009, 03:30 PM

Hey,

Sorry for the belated response- have you thought of setting this up as a mailmerge in word? You could create a template there and then just by selecting the appropriate excel file you'd be able to print everything...

M