yes, of course Sam. thank you for asking/clarifying.
please forgive me for not knowing how to summarize in 30 words or less...


the project is to collect, summarize, and crunch data from many Facebook campaign reports...

- starting off auto-saving individual campaign reports with data for multiple ads -
I'd first like to pull that data into Account Log Workbooks for each campaign - the workbook template's columns already matching up with the reports, and a formula to pull all the rows from each daily report into the log - the workbook hyperlink preprogrammed based on the campaign report naming structure and date.

- then, data from the Account Logs are to be pulled into a "Cycle Book" - by hyperlinking to sheets in the Logs - which both averages the last 3 days' data and then crunches it through some formulas to mark which ads are to be deleted, and how much to scale up the budget on the remaining ones.

- then, there will be a Daily Overview book/sheet, which is to pull the data from each campaign's Cycle sheet - so as to be able to have a complete listing of ALL ads, from which to sort and overview all ads at once. the Cycle Books will also feed data into a "Performance Review" book to track each campaign's ad performance over time - hyperlinked to the sheets of a campaign's Cycle book.


now to recontextualize my questions:


1. i'm curious how to setup a relative workbook hyperlink, in order to create a 'Campaign Folder Template' that already has an Account Log Book, Cycle Book, and Performance Review Book in it - and the hyperlinks from the Account Log - Cycle Book - Performance Review will be referencing the books in that campaign folder, NOT the original folder the template was copied from. i'm guessing there might also be a way to eventually program an app with VBA or Access that automates everything when creating a new campaign/account folder - though at this level of knowledge, creating a folder with these template books would probably be the easiest - and will need to be duplicated & renamed for each new campaign, so want to be sure those hyperlinks are directing to the books in THAT campaign folder.


2. re: auto-inserting rows when hyperlinking from a separate workbook... whether that's from the Facebook reports to Account Log, Account Log to Cycle Book, or Cycle Book to Daily Overview - I can't really reference cell-to-cell, as the amount of rows will be changing for every campaign every 3 days as ads get deleted. so essentially, I'm looking for a formula that's going to go: "pull in all data from sheet 1 starting at row x" - and auto-inserts as many rows at it pulls in, with the row below it having the formula ""pull in all data from sheet 2 starting at row x" and auto-inserts the same number of rows as it pulls in, continuing on from as many sheets as required...


3. re: auto-updating hyperlinked books... given the large number of books involved in this setup as the number of campaigns grows, there will be alot of books that info is flowing through - yet, the only book required to be looked at is the Daily Overview, and occasionally the Performance Review. However, the info turning out right on the Daily Overview is going to rely on the info in several Cycle Books being updated, based on the data in the Account Logs being updated by the campaign reports. hence, it'd be prime to just open the Daily Overview, and it traces everything back / auto-updates without having to actually open all those other books.

I did receive the following answer elsewhere, saying it is possible for Excel to automatically trace back the data through all the books: "You can control that behavior in Options\Trust Center\Trust Center Settings\External Content"


i hope that paints a clear big picture.

thank you. :-)