Thank you Paul. By the time I read your advice this evening we had already tried that. We saved it as xls, then made the formula edits, then saved it as a xlsm - and that worked. So you were on the money. Thank you.
Thank you SXGuy. By the time I read your advice we had already tried that. We saved it as xls, then made the formula edits, then saved it as a xlsm - and that worked. So you were on the money. Thank you.
This works for me and so most helpful.
Many, many thanks.
Thanks for the offer Russell but I have now managed to work it out.
Thank for your help Paul. I have now cracked it. Probably not the prettiest piece of script - but it works. Very pleased!
Files are in the same directory and I was thinking along the same lines. One of your links led me to this thinking.
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 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.
Apologies for my slow feedback.
I have now upgraded to Excel 2013 and =formulatext works. Many thanks for the tip.
will try Excel 2013
Apologies for not responding sooner.
Another respondent has suggested using Excel 2013. So I have upgraded and this resolves. Just leaves writing the search/find formula but I should be able to work that out.