Share this content
0
3638

Update workbook linkage without opening source

Update workbook linkage without opening source

I have a cashflow model.

There are 65 workbooks each representing a division and accept prime entry cost data. The results of the same 65 "children" workbooks are linked into and consolidated into a "parent" workbook which aggregates and summarises the results of the 65 sites.

In the parent workbook I have set up flex tables to apply to the children workbooks. e.g. I can input a 5% flex requirement (in parent) which will link and globally adjust base costs by 5% in all the children workbook.

My problem is that unless I open or reopen all 65 children workbooks, the updated aggregated results will not feed back up through to the parent.

Is there a way of updating the parent without opening the children? Or is there another way of creating a global setting for the 65 children which will work around having to open 65 files.

Thank you for reading this. All help gratefully received.

Replies

Please login or register to join the discussion.

avatar
31st Jul 2014 10:42

A few questions

You say that you cannot return the updated results. Does this mean that you are successfully updating the closed workbooks but cannot get the data back? If so, how are you performing the update (VBA I assume)? Or are you in fact not able to update the workbooks whilst closed?

How is the data being exchanged between workbooks in each direction? Formulae, data links, vba?

Are the flexes applied at a high or low level? For instance, is it practical to just pull through the relevant data into 65 sheets and apply the flex in the parent model. Do you need the underlying workbooks updated to, for instance, feedback to the relevant divisions?

Is the underlying data in the form of tables? Unlikely from the description but, if so, it opens up a number of possibilities.

But my best guess at this stage is that, unless you can simplify the data and manipulate it at parent level, you are probably looking at a vba solution.

Thanks (0)
avatar
By mung1
to dmmarler
31st Jul 2014 12:17

Thanks Paul, - I hope this answers:

The general and ideal situation is that all 65 children w/b would be closed when,applying the flex in the parent w/b. The link between files is by simple formula e.g.

=[parent file]Sheet1!$A$1. for child to receive the flex% instruction from the parent

=[child file]Sheet1!$A$1. for the parent to receive back changed results from the child.

The update is necessary so the operator of the parent w/b can immediately see the aggregate affect of a flex input to the 65 children. User of the child w/b would only see the effect when they opened the child w/b. The child w/b is in itself quite a meaty size and works out the it's own forecast P&L, WIP, balance sheet, cashflow and a bunch of assumption. Therefore the flex (and there are flexes to several different metrics) needs to be applied to the workings in child w/b as there is no workings in parent w/b to apply the flex directly to (i.e within the same w/b - which would be nice). So there are 65 sets of individual P&Ls, BS, WIPs, cashflow that are consolidated to the parent. And BTW there are 5 other parents groups of this magnitude leading to one corporate consolidation.

The data outputs are in a table form, but crucially not the prime entry data. So using these tables would be rather "after the fact" as the flex needs to be applied at source (pre tables).

Maybe vba solution is the way forward. I have a very basic understanding of vba and happy to give it a go. Would you be able to get me on my way with a basic script or steer me in a direction there.

Many thanks for your help.

 

 

Thanks (0)
avatar
31st Jul 2014 13:31

Hmmmm

390 odd workbooks with 6 sub consolidations and a parent consolidation. Are you sure Excel is the best solution for this? Still - that doesn't help with your immediate problem.

Re vba: These posts may give you a steer:

http://www.mrexcel.com/forum/excel-questions/700043-change-cell-value-cl...

http://www.ozgrid.com/forum/showthread.php?t=157061 (particularly posts 16 and 17)

 

But you will probably find it will take a fair amount of research,development and experimentation. Try to make it as bullet proof as possible and especially ensure the macro has good error handling and can capture and report times when a non standard or incomplete run occurs.

Only use non-critical data and files until you are sure all is well.

Better still, if you can, find a vba expert either internally or externally. It will be quicker in the long run and will save you some long hours and sleeepless nights!!

 

 

Thanks (0)
avatar
By mung1
to Vaughan Blake1
31st Jul 2014 15:23

Thanks Paul

I know the sheer volume is pushing spreadsheets to the extreme (and this is perhaps beyond that), but unfortunately this is not my call.

Thanks for the links and your feedback.

 

 

 

 

Thanks (0)
avatar
31st Jul 2014 15:44

Of course,

if all of the files are in the same directory and you have enough RAM, you could just highlight all the files in Explorer and hit enter to open them all.

A utility set or simple macro could be used to close them.

Thanks (1)
avatar
By mung1
to Tax Dragon
01st Aug 2014 15:00

Thanks Paul

Files are in the same directory and I was thinking along the same lines. One of your links led me to this thinking.

 

Thanks (0)
avatar
By mung1
to Tax Dragon
07th Aug 2014 09:56

Cracked it

Thank for your help Paul. I have now cracked it. Probably not the prettiest piece of script - but it works. Very pleased!

Thanks (0)
Share this content