Finding Named Field in Formulas

[Jeff Jones]

I have inherited a complex spreadsheet with formulas all over the
place. The requirements are long gone. The original developers used
named fields to define cells referenced in the formulas. It's easy
enough to display all the formulas in one or all worksheets using the
Ctrl/~ keystroke combination. I would like to be able, once all
formulas are displayed and all worksheets are selected, to do a find
for each names field to find where it's used throughout the formulas
in the spreadsheet. Thus far I get a not found condition when doing
the find.

I could probably write a macro to spin the cells to see if I can find
named fields in the formulas but would need to test that. I know that
I can insert formulas into cells via a macro but haven't had the need
to look at the contents of formulas via a macro so don't know if this
could be done. As will every project in our exciting corporate world
my time frame is rather short and there are a lot of these named
fields.

Does anyone know how to do a find for data contained within the
formulas themselves? I am far too lazy to want to look at every
formula in every worksheet to see if these named fields are used but
will do so if that's how I have to proceed.

Hoping...

Thank you,
Jeff Jones

 

[Guest]

Try this:

Ctrl+F.........a shortcut for <edit><find>
Find what: (enter the range name you're looking for)
Search: WORKBOOK
Look In: FORMULAS
Click the [Find All] button

That will create a list of all cells containing the range name. Click on any
entry in that list to view the contents of the referenced cell

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Jeff Jones]

It sounds like it will. I admit that I get SO close to some of the
windows that I don't look at all the options. I hit the Ctrl/F find
keystroke combination and just enter my criteria without looking at
the options. The quicker I go, the behinder I get.

Thank you Ron.

Jeff

Try this:

Ctrl+F.........a shortcut for <edit><find>
Find what: (enter the range name you're looking for)
Search: WORKBOOK
Look In: FORMULAS
Click the [Find All] button

That will create a list of all cells containing the range name. Click on any
entry in that list to view the contents of the referenced cell

Does that help?
***********
Regards,
Ron

XL2002, WinXP

I have inherited a complex spreadsheet with formulas all over the
place. The requirements are long gone. The original developers used
named fields to define cells referenced in the formulas. It's easy
enough to display all the formulas in one or all worksheets using the
Ctrl/~ keystroke combination. I would like to be able, once all
formulas are displayed and all worksheets are selected, to do a find
for each names field to find where it's used throughout the formulas
in the spreadsheet. Thus far I get a not found condition when doing
the find.

I could probably write a macro to spin the cells to see if I can find
named fields in the formulas but would need to test that. I know that
I can insert formulas into cells via a macro but haven't had the need
to look at the contents of formulas via a macro so don't know if this
could be done. As will every project in our exciting corporate world
my time frame is rather short and there are a lot of these named
fields.

Does anyone know how to do a find for data contained within the
formulas themselves? I am far too lazy to want to look at every
formula in every worksheet to see if these named fields are used but
will do so if that's how I have to proceed.

Hoping...

Thank you,
Jeff Jones