Populate “template” sheet from data in “source” sheet

I’m not even sure how to phrase my question, and I apologize for that.

1. From a list of page contents, shown in Source_Image.png, I want to create Bookmap_Image.png.

2. Frequently, the source data will change, for example, if a 4-page section needs to be added after page 20 or something. Therefore, I need the rows after this new data to (a) repaginate (change the values in column 1 and column 2), and (b) populate into the bookmap, reflowing in the same fashion.

What Source_Image.png shows now is, as you may be able to see, just a pointer to the cell containing the pertinent data. As limited as I am, there’s got to be a better way. Page_Image.png shows what the first page of the bookmap template looks like. A publication could have 60 pages or 600 or more, so I’m reluctant—call me lazy—to continue my cell-by-cell manual solution.

Is there a VBA solution for this problem.

Thanks very much in advance even for just taking the time to read this.


Left this bit out, sorry. Formula_Bar.png

Please post a workbook with some sample non-sensitive data. It will help us help you better.

Also, repost the pictures in JPEG format. PNG files don't show up for everyone on this board.

I'll be blunt. I'm not going to take the time to set up the spreadsheet. If you have a sample and can attach it, it would help a lot. This looks very do-able using a table and some lookups.

OK, I got the workbook. What is it exactly what you want to do? Do you want to add a page number on the lower left of the page and have it look up the "nf" value and what's supposed to go there. Or do you want to go in the opposite direction: change the data on the AR source page and have it reflect on the AR Bookmap page?

Also what does the information on the lower right in the brackets do or should I not be concerned with that?

Yes, it should change the data on the AR source sheet and reflect it on the AR Bookmap sheet, which I will save as a template for future use. For example, the contents of C2 on the Source sheet should copy to what is now merged A1B1 on the Bookmap sheet; Source B2 to copy to Bookmap B2; and Source A2 to copy to Bookmap A2.

This repeats for the next Page Number/Folio/Page Description row. Every third column (E, J, O in the sample image) should remain blank. After eight instances of this process, four 2-page spreads, it should skip the blank row (3, 6, 9 in the sample image) and repeat; then after four 8-page "rows," start over on next page.

There are no calculations to be made, per se. If I need to insert 4 or 5 pages after page 50, say, it should reflow the subsequent rows to reflect this new data. The brackets are simple text. [nf] means "no folio" and [bf] means blind folio.

The purpose of this is to generate a visual reference of the contents and position of every page of a publication. Thank you for giving this some thought. Please do let me know if I've failed to explain adequately.

This thread has been re-started in Commercial Services. You may offer to solve it if you have Guru status or above.

http://www.excelforum.com/showthread.php?t=1164308