In our accounts workbooks, we created a Totals sheet. This contained totals from the monthly worksheets, but it also contained items which were constants. For example, we have a dental insurance and this is paid monthly, so each Totals month has an entry which is one twelfth of the annual premium. So far, so good. Then we got sloppy.
If there was an extra dental monthly cost, say a filling, we sometimes just added this to the monthly totals entry e.g. =25+45, but then one of us had a brainwave (almost certainly me) and invented a new category (Dental) of cost to be entered in a monthly worksheet. This was then totalled at the end of the month as (examples) =25+Feb17!B15 or =25+Oct17!B41
The problem now is to search for and find any cell which contains a cell reference in a formula, i.e. the search won’t find ‘=25+45’ but will find ‘=25+Feb17!B15' and '=25+Oct17!B41'
Is this possible?
If so, can it be done for an entire Totals worksheet?
Thanks
Silverback
Search for formula containing a cell ref.
-
- 5StarLounger
- Posts: 776
- Joined: 29 Jan 2010, 13:30
-
- Administrator
- Posts: 78532
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Search for formula containing a cell ref.
If all those formulas refer to cells on other sheets, you can search for ! since all references to other sheets contain a !
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 776
- Joined: 29 Jan 2010, 13:30
Re: Search for formula containing a cell ref.
Duh! Thanks for pointing that out.
I think I've found a different way; if the search is for + and the option is 'Find All', Excel 365 obligingly lists all references to + as hyperlinks. It lists not only the cell in which the formula is found, but the actual formula. So it's easy to see which contain cell references.
Many thanks; you've saved me hours of manual investigation and typing.
More beer and chocolate.
I think I've found a different way; if the search is for + and the option is 'Find All', Excel 365 obligingly lists all references to + as hyperlinks. It lists not only the cell in which the formula is found, but the actual formula. So it's easy to see which contain cell references.
Many thanks; you've saved me hours of manual investigation and typing.
More beer and chocolate.