Formula Verification

[wtsabo]

I have a number of sheets, which are uniform in there formats and calculation metholodgy. The only thing that differs is the reference for data.

For instance:
Sheet 1 has formulas like =VLOOKUP(a1,'[Data File 1.xlsx]Maine'!$B$1:$c$10,2,FALSE)
Sheet 1 has formulas like =VLOOKUP(a2,'[Data File 2.xlsx]Maine'!$B$1:$c$10,2,FALSE)
Sheet 1 has formulas like =VLOOKUP(a3,'[Data File 3.xlsx]Maine'!$B$1:$c$10,2,FALSE)
Sheet 2 has formulas like =VLOOKUP(a1,'[Data File 1.xlsx]NewYork'!$B$1:$c$10,2,FALSE)
Sheet 2 has formulas like =VLOOKUP(a2,'[Data File 2.xlsx]NewYork'!$B$1:$c$10,2,FALSE)
Sheet 2 has formulas like =VLOOKUP(a3,'[Data File 3.xlsx]NewYork'!$B$1:$c$10,2,FALSE)
Sheet 3 has formulas like =VLOOKUP(a1,'[Data File 1.xlsx]Virginia'!$B$1:$c$10,2,FALSE)
Sheet 3 has formulas like =VLOOKUP(a2,'[Data File 2.xlsx]Virginia'!$B$1:$c$10,2,FALSE)
Sheet 3 has formulas like =VLOOKUP(a3,'[Data File 3.xlsx]Virginia'!$B$1:$c$10,2,FALSE)
and so on..

The issue I'm having is the ability to verify all the formulas in a sheet are referencing "Maine" or "NewYork" or "Virginia" and so on...
On occasion formulas change which results in some formulas sourcing from Maine, some from NewYork, some from Virginia....

Is there a simple solution to identify formulas which are not referencing all the same source? I've tried "watch window" and "show formulas" but this is very time consuming due to the amount of information and number of sheets.

Thanks for the help

 

[wigi]

Hello

Try the Excel option:
"Show formulas in cells instead of their calculated results"

In Excel 2007/2010, you will find it under "Advanced" within the Excel Options dialog screen.

 

[wigi]

Can you provide feedback please?

 

[wtsabo]

Can you provide feedback please?

"I've tried "watch window" and "show formulas" but this is very time consuming due to the amount of information and number of sheets."

Regards

 

[wigi]

Maybe an idea: why not make 1 template sheet with a placeholder for the state name.
Then, using an automated approach, sheets are created whereby the placeholder is replaced with the state, in a loop.
Then you're sure to have correct references.

 

[wtsabo]

Maybe an idea: why not make 1 template sheet with a placeholder for the state name.
Then, using an automated approach, sheets are created whereby the placeholder is replaced with the state, in a loop.
Then you're sure to have correct references.

This idea would be appropriate if the model was brand new and all the state sheets pulled figures for each formula. I have actually used this type of approach for different models. Unfortunately the models are not new and the data isn't consistent. The fastest way to review all the formulas was using data watch. It was was manual but effective. I also used Find All within the workbook for each of the states. I lack the VBA expertise to create a macro which reports Find All results so I just did it state by state.

 

[wigi]

Or, try Chip Pearson's FindAll VBA function. He posted this on this tremendous website.