Absolute/Relative Workbook hyperlinks?

[sivante]

When referencing to another worksheet/book, is it always linked to a file on a specific hard drive (absolute) - or can be relative, looking in a certain folder, regardless of whether that folder is on computer A, B, or a cloud?


I’m guessing its probably absolute - though is there any way to change to relative?


I’m looking to design a series of templates with multiple interconnected workbooks, to be hosted on a cloud. Won’t work if the reference values are absolute - so need relative values so when linking to the other workbooks, Excel will be looking in the relative folder.


thanks!!

[sivante]

if I am trying to pull data from multiple workbooks to be added as rows, how can I get Excel to auto-insert the amount of rows, rather than referencing individual cells?


i.e. if the number of rows in a workbook being referenced fluctuates, I can’t simply reference cell-to-cell…


i.e. If cell A5 is set to pull in and list all data from Workbook 1, which is 50 rows - cell A6 is set to pull in and list 100 rows from Workbook 2, and A7 set to pull in 50 rows from Workbook 3 - so it all lines up nicely as 500 rows when complete…?


What function/formula would this be - to have Excel auto-calculate how many rows are to be pulled in from the referenced worksheet, which also auto-inserting that many rows as it’s pulling in the data?


thanks!!

[sivante]

if there are a series of references from separate workbooks…




i.e. from A1-100 to B1-100 to C1-100 to D (301 workbooks/sheets in total, for example)… must workbooks B1-100 and C1-100 be opened and resaved to auto-update Workbook D when it’s opened - or when you open up D, will Excel automatically trace all the data back through the other books, even if they haven’t been opened since Workbooks A1-100 have been updated?



if all the books DO need to be opened to update - what workarounds or quick tips might there be to automate or speed up the process, so you don’t have to manually open & resave hundreds/thousands of books?




thanks!!

[SamT]

These three posts have been combined into one thread due to interacting issues.

[sivante]

ok, thanks

[SamT]

Can you give us the "big picture" of your project?

I have been considering all the implications of your three posts. but many things are not yet clear.

[snb]

@SamT

Have a look over here:

http://www.thecodecage.com/forumz/showthread.php?t=214963

[SamT]

Links Between Multiple Workbooks - Updating...? (http://www.thecodecage.com/forumz/showthread.php?p=1055012510#post1055012510)
Auto-insert rows with workbook reference values? (http://www.thecodecage.com/forumz/showthread.php?p=1055012509#post1055012509)
Absolute/Relative Workbook hyperlinks? (http://www.thecodecage.com/forumz/showthread.php?t=214961)

Please read this link, then post links here to other places you have posted this question. Thanks.

http://www.excelguru.ca/content.php?184

[sivante]

hey Sam,

thanks - I did get notified about crossposting elsewhere and was trying to update this yesterday with links, though as did post all 3 separate questions in 7 different forums, it wouldn't allow me to post here, i think hit the spam filters because too many weblinks in a single message. trying again, for just the single post titled the same:

excelexperts.com/absoluterelative-worksheetbook-hyperlinks
stackoverflow.com/questions/34561853/how-to-specify-absolute-relative-worksheet-book-reference-values-in-excel
social.technet.microsoft.com/Forums/office/en-US/477052cd-19de-430c-8287-15dfe62bbaa1/absoluterelative-workbook-hyperlinks?forum=excel
excelforum.com/excel-formulas-and-functions/1119733-absolute-relative-workbook-hyperlinks.html
mrexcel.com/forum/excel-questions/911677-absolute-relative-workbook-hyperlinks.html
excelforum.com/excel-formulas-and-functions/1119733-absolute-relative-workbook-hyperlinks.html
excelguru.ca/forums/showthread.php?5308-Absolute-Relative-Workbook-hyperlinks

[sivante]

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. :-)

[SamT]

I think that today, you should profusely apologise for not understanding the cultural courtesy rules in those other forums and beg their forgiveness. By Wednesday, pick one forum as your "Base," and tell all others that you have centered on that forum. You really want to preserve your reputation, since the community will remember this project, and you, for years.

I am not the "Formula Guy" at VBA Express. One of them may check in later. However I don't see Formulas being the overall solution in this case.

Remember the adage, "To a man with a hammer, every problem is a nail?" Well, my "Hammer" is Automating with VBA 'Macros.'

In 30 words or less: You have X number of Data Sources. You need to analyze the last three days of each Source. You need a historical review of each Active Source. Data Sources come and go.

Data Flow:


Raw data is on the Internet, Whence it is (magically) transferred to a Data Source
Data Sources (Daily Campaign Reports)
Reports

Daily Overview
Performance Review



Daily Overview = three day analysis of all active Data Sources
Performance Review = Historical (analysis) of all active Data Sources



As to the Account Log Workbook, Unless the "magical" transfer of data from the internet requires it, I see no reason not to place the data directly into it.

These Workbooks should have a Formula driven "Three Day Analysis" Sheet so that the Reports can pull directly from them.

BTW, just how many Campaigns can be expected to be active at one time? Tens? Hundreds? Or thousands? This has a real bearing on the best way to build your project.

I will let you absorb all this for now. We are looking forward to your comments

[sivante]

thank you for your counsel, Sam. while upon receiving the initial crossposting warning, I immediately tracked back all the posts to edit with links and apologize, I just did as you advised - adding an extended apology for all the posts, and choosing this VBA Express forum and Excel Forum as the two main ones I shall limit activity to - and be sure to cross-reference if do crosspost at any time because questions do not get answered in these forums. I appreciate your forgiveness, patience, understanding, and willingness to be of help.


great summarization.

the "magical" transfer would be establishing a Campaign Report sheet (Data Source) to be emailed daily for each campaign with the selected data needed, setup in Facebook's business manager. looking to use Google Docs "Save Emails" app to automatically download all the Campaign Reports to Google Drive - then CloudHQ to sync those downloads to Microsoft's OneDrive, as will be running Excel from Office 365 online.

the Campaign Reports (Data Source) are the medium through which Account Log Workbooks pull the data from - required as there's no other way I know of to pull the mass of data from Facebook's Campaign Manager, unless manually inputting one value at a time (impossible).


we'll be starting off with less than a hundred campaigns, but fairly quickly growing into the hundreds - and as eventually into the thousands, I'd think it best to build the system to be able to support scaling up into the thousands. the only difference there might possibly be to evolve it from running on Excel to Access - but as I'm still learning more of the complexities, feeling it'd be best to keep as simple as possible with Excel and then figure out how to transition to Access for expanded functionality as required.


thank you.

[SamT]

About Office 365:
https://support.office.com/en-us/article/Comparing-Excel-Online-Excel-Services-and-Excel-Web-App-855EE1A3-9263-425D-BCCD-4070D2413AA7

http://www.zdnet.com/article/small-businesses-beware-the-office-365-fine-print/

OK, I've reached the limit of My Office 365 knowledge :D

Access or Excel (PC version)
Excel won't be able to handle the data from 1000 campaigns, not to your satisfaction anyway.
Access will laugh at a measly 1000.

Let's assume that you are going to design the project around Access from the Start. The main change is that Access takes the place of all the Account Logs. Program Access to Average each Active Accounts last three days average and place that data into another Table, Historical Performance. Set up the Cycle Book, Daily Overview sheet, to ask Access for Today's data from the Historical Performance table. This means the one sheet in the Cycle Book has up to 1000 Rows of data to work on, not 1000 sheets. Perform the analysis on another sheet in the same book.

Don't ever imagine that jumping straight to design or coding of a project will be faster than planning the whole project first. I call those projects "Organically Grown" and they wind up as organized as a plate of spaghetti.

Here are some words of wisdom by the Project Manager for Excel 5 (and VBA?) Home Page (http://www.joelonsoftware.com/)
The Process of Designing a Product
(http://www.joelonsoftware.com/uibook/chapters/fog0000000065.html)Painless Functional Specifications - Part 1: Why Bother?

(http://www.joelonsoftware.com/articles/fog0000000036.html)If you like his style, Read on:
(http://www.joelonsoftware.com/uibook/chapters/fog0000000065.html)Designing for People Who Have Better Things To Do With Their Lives - Part 1

(http://www.joelonsoftware.com/uibook/chapters/fog0000000062.html)
(http://www.joelonsoftware.com/uibook/chapters/fog0000000065.html)

[sivante]

Sam




Ok, I definitely owe you a beer (at the LEAST). :-)


Seems Access is the simpler option for sure. Now just the particulars to sort out...


If the data source were to be a Daily Campaign Report via Facebook with a row for every active ad (exceeding 1000 per day once scaled up) - would it be best that data be continually added to a single table? If so, what field would need to be set as the Primary Key or just a simple generic number for the entry?


What would be the most organized way to log 1000s of ads' daily data - In an "Ads" table with 1000+ ads, with 365 rows worth of data each per year (or would that not work, because you couldn't have an ad number as a primary key AND multiple row of data for that same duplicate primary key in the same table)?


If all that data were accumulated in one table, could/would the programming code be set to list the averages in the next Historical Performance table, with a "cycle range" column displaying the dates the average took data from - and continually update that Historical Performance table, adding new rows and new averages for all active ads each 3 days?


I'm not sure if the 'Cycle Book' in Excel would even be required - if there's a different way to track the same data within Access (i.e. "Historical Performance" table(s)). And as for the 'Daily Overview sheet,' this could essentially be produced via a query and report...


Issue then just becomes would it make more sense to crunch the data through the budget scaling algorithm from the Historical Performance table in that table, or the query? And would it be possible to reference figures from preceding cycle ranges in the Historical Performance table in the algorithm the same way as possible in Excel? (As the algorithm references figures from both the current and preceding cycles)


(Or, would it be better to make more complex by automating the creation of a Cycle Book in Excel for every active ad to run the averages through the formula and track the results - automatically looping the results back in the Access database?)




I've begun learning from some Access tutorials, though still more on the basics and this seems like a unique application, so am sure the answers will become clearer the more I learn - as will the more clearly articulated questions to ask. However, these are the questions that have risen to the forefront so far - though can see things coming together MUCH more simply with Access than Excel...




Thanks!

(And the Spolsky stuff is good. ����)

[SamT]

Yeah, I think i have read almost everything Spolski has written for the internet.

Extracting data from the Database is straight forward. If the DB is well organized.



If the data source were to be a Daily Campaign Report via Facebook with a row for every active ad (exceeding 1000 per day once scaled up) - would it be best that data be continually added to a single table? Yes, but see below


If all that data were accumulated in one table, could/would the programming code be set to list the averages in the next Historical Performance table, with a "cycle range" column displaying the dates the average took data from - and continually update that Historical Performance table, adding new rows and new averages for all active ads each 3 days?I may be mistaken, but I thought you wanted Daily averages for the previous three days. Yes I would just use Queries for the Cycle report and Daily overview.

I would keep the daily averages in a separate Table for historical purposes just to not need to perform such heavy a load on Access. Do the math once, then store the answer.

The Database:


One Client has many Campaigns
One Campaign has have many Ads
One Ad can has many Daily Reports


(Anything else? One Copy Writer has many Campaigns and many Ads? One Ad has many outlets?) :dunno

That suggest three Tables


Client Table, PrimaryKey:=ClientID
Campaign Table, PrimaryKey:=CampaignID, SecondaryKey:=ClientID
Ads Table, PrimaryKey:=AdID, SecondaryKey:=CampaignID, TertiaryKey:=DailyReportDate


I would consider an ActiveAd Table, PKey:=AdID, Fields:=(Start As Date, Killed As Date, LastRun As Date, Active As Boolean.) LastRun lets you "Pause" an active ad without losing it, plus, electrons are cheap; you can buy a TeraByte of them for less than $100.

You might want these Relationship (Index) Tables: CopyWriter To Campaign, CopyWriter To Ad, Outlet to Ad.

That would let you "drill Down" to just about anything. For Example; Client A wants to know which ad in his Ikea Campaign ran on FM 93.9 on Dec 12, and who wrote the darn thing. Client B needs to stop his Chevrolet Campaign in Louisiana until the Listeria scare blows over, Etc.

In order to keep size down and speed up, after business hours each night, I would duplicate all daily transactions into a duplicate DB. After that, I would use aging to delete inactive accounts, 7 years for Clients, three years for Campaigns, one quarter for "killed" ads, and one year for Inactive Ads.