Is there a formula to search for a string in a formula (instead of the results)?

[timgaylord]

The attached is an example of a budget template. The budget templates are distributed to each Department Manager (Mgr). Each template contains department specific historical data and blank fields. Mgrs are expected to populate cells C7 thru D8. Column E is is a volatile column that is updated every month. If Mgrs use formulas that reference the cells in column E, their numbers will change un-expectedly with each monthly update.

I need to figure out how to either:
(A) Prevent Mgrs from entering formulas that reference the cells in column E
or
(B) Come up with a way to quickly and easily identify instances in which Mgrs have used references to Column E within columns C or D.
- I was hoping to be able to, essentially, dump in a formula that, if entered in F7 would check the formulas in C7 and D7 and report any improper references to E7.

Any ideas?

[opsman]

This may seem a cop out answer, I'm unsure whether I've understood exactly what you're after. It's customary to provide an example where possible so viewers can work out the exact problem, but based upon what you've said is it not best to ask the mangers simply to do workings on a copy and then copy across their final workings into the master?

Otherwise, simply only allow them conditional access to the cells where you want them to input data;

I think you know this already but just in case;

1. select cells you want them to access
2. Format - cells - protection - and untick locked
then..
3. Tools - protection - protect sheet - untick select locked cells (and any other conditions) and password protect + confirm
4. Save

It works for me on my monthly management reports and keeps the master clean. Annoys the hell out of them though, but you can't keep all the people happy all of the time!

If they need to carry out formulas on the master then I suggest the only real alternative may be to update the master accordingly?

Hope that's helpful...

[Huron]

You could just copy & paste special - values for columns C & D every month.

[timgaylord]

Wow, I really appreciate all of the quick replies.

As I was uploading my thread, I was getting dragged out the door to the Department's Christmas Dinner. I'm sorry that I didn't get a good look at how poorly my example was rendering.

Now, I've uploaded a file. This should be better.

So....

Columns C & D are actually the only ones that aren't locked. The users are strictly limited to entering data in specific areas.

The real area of worry is that we have no control over where the users point any cell references in formulas. I know that this is something endemic to any process of this sort. But, If at all possible, I want to prevent the users from making these fatal errors directly within the Budget Templates. Any references they make to their own unique separate spreadsheets are their own problems.

In the attached example, Column E has "actuals" thru October. The users have, in some cases, entered the following formula to calculate next year's projections in column D (assumes a 10% increase for next year):
=(E7/10)*12*1.1
which returns a projection for $4,224.00 for GL Acct #12 in 2010
Now, if the user makes no changes rolling into the end of November, things are fine until I push out an update of the YTD Actuals which changes the column heading for E to "YTD Act 11-09" and changes the amount in E7 to $3,470.00. At this point the user's 2010 Projection for GL Acct # 12 leaps up to $4,580.00. So, without receiving a single revised template back from any user, my numbers suddenly change.
When this happened recently, I found the specific problem cell the by opening all of the templates individually and performing a FIND>LOOK IN FORMULAS searching for references to the column for YTD Actuals (a little easier to do on the actual templates since YTD Actuals show up in column AY).

In conclusion (too late to make a long story short), my problem isn't so much "What cells do I allow the users to access?".
My problem is much more "How do I make sure the user's don't point at this volatile cell that is only meant as a reference point showing them their most current 'Budget to Actual'?".

I'm thinking that my best choices are:

A - Find a way to prevent the users from entering formulas that reference the values in Column E

or

B - Find an easier way to identify when the users have entered formulas that reference the values in Column E

In my dream world, I would be able to enter a simple IF formula into column F that would say:
=IF([the formula in] D7 "contains" a reference to E7,
{THEN}"Attn User - You have referenced the values in Cell E7 which is a volatile cell and will change next month! Please reference a different source for your data!!"
{ELSE}"")

With a formula like that in place, (which users might ignore), when I consolidate all of the templates, any remaining problems of this nature would leap right out of my pivot tables to be easily tracked and fixed.

Of course, I know I can do a cumbersome EDIT>FIND>LOOK IN FORMULAS for column D in each of the 70 templates. (as I've already done, wasn't fun or efficient)
I just can't seem to find a way to place a formula / function within a cell to examine the contents of the formulas in column D.

Thanks again,

Tim

[timgaylord]

Re: "Why not just copy Columns C & D and paste as values?"

I'm close to that point, but find that it has it's own head-aches.
There are two main stumbling blocks:
1. The more astute users (and the higher ups) have gotten used to the ability to update their source data spreadsheets and have the changes wash through to the budget templates. (ex, "Hey, why didn't my changes show up in your consolidation of the budget templates?!?!")
2. The process to back-track "where did this data come from?" becomes much more cumbersome. I have a macro in place that makes a back-up of the existing version of the templates before saving the new versions. This macro is great, but, my busier templates might be opened 10 times a day. Many users (sometimes me) save on exiting whether they need to or not. So, if I "paste as values" in C & D, and need to trace the source(s), I could suddenly find myself having to check dozens of back-up copies to find the copy with the active formulas.

Thanks for the Post,
Tim