LinkBack URL
About LinkBacks
Hi
I need to dermine the number of errors in a workbook either by formula (preferred) or by VBA.
ege id there are 2 #REF and 3 #value then the value would be 5.
I am using Excel 5.
Thank You
Allister
Hi
I need to dermine the number of errors in a workbook either by formula (preferred) or by VBA.
ege id there are 2 #REF and 3 #value then the value would be 5.
I am using Excel 5.
Thank You
Allister
If you know the range of cells that you want to test then I think the following may work for you. Here I have assumed a range of A1:D7:
Formula:
Please Login or Register to view this content.
This is an array formula and must be entered via CTRL-SHIFT-ENTER
Let us know if this works for you
Geoff
Did I help significantly? If you wish, click on * Add Reputation to say thanks.
If your problem has been resolved please select ?Solved? from the Thread Tools menu
Thanks Geoff
What would the range look like if I wanted to include all the Sheets in the workbook. the number of sheets may increase overtime.
Allister
First, there's a mistake in my post #2 formula. It needs to be:
Formula:Please Login or Register to view this content.
The explicit coersion (the --) from logical to numeric is needed I think
I played around with so called 3D references like: Sheet1:Sheet3!A1:D7 but could not get it to work, so the best I can think of for now is to explicitly add the errors from each sheet, like:
Formula:Please Login or Register to view this content.
Do you have too many worksheets for this to be practical?
I have about 25 sheets and likely to add and delete sheets.
Last edited by AliGW; 07-16-2020 at 01:59 AM. Reason: Please don't quote unnecessarily!
I would suggest to use the Power Query.
To update your data is by clicking a refresh if you done this more often.
Hope it helps.
Thanks for your suggestion.
If all sheets have different structures how do I do what you suggest in PQ?
Thanks
On relection i think I will go with a VBA solution - something like one that find & lists the Errors in the workbook but in this case it counts them and outputs to a cell
Does anyone have such a macro I could use?
Thank You
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks