Count cells with a Certain Custom Number Format?

[mgirvin]

Dear Most Amazing Excelers In The World,

If I have a column of numbers and some (not all) of the numbers have the Custom Number Format:

0.00”*”

is there a way to count the numbers with this Custom Number Format?

 

[Peter_SSs]

Mike

I didn't respond to this earlier because I didn't really understand that short-hand referencing method and guessed that it may fail in some circumstances. I must admit I still don't really know why it works though I do see how to work it.

Anyway, I haven't yet found any circumstances where it fails and have been suggesting it in some threads that I have answered (eg this one )

So thanks also go to you for a great tip!

 

[pgc01]

Hi

Caveat about this notation:

This notation, !A1, that means cell A1 in the current sheet, is here since long before excel 2007, but is not usually used because it has always had problems with refreshing.

I think I already read something about this refreshing problem also ocurring in xl2007.

I can't find a link I had about this refreshing problems when using this notation. If someone has one, please post it.

 

[mgirvin]

Re: Count cells with a Certain Custom Number Format? Defined Name Relative Cell Reference (Global Name Formula Cell Reference)

I am glad to contribute, Peter_SSs -- especially since I have learned so much from many of your suggestions!

Yes, please do post a link on the refreshing problem! I would find it quite helpful.

I have used this "global relative cell reference in defined names" for years. I first read about it in a Walkenbach book. I have never had a "refreshing problem", but maybe it is just because I have not encountered that particular glitch. It really is a useful trick and avoids big named formula with ADDRESS and other functions. I use it for global % change formulas and the like. The way I always get it to work is to:

1) Click in the cell that I want to build a formula with Relative Cell References
2) Open Defined Names dialog box
3) Create formula with Relative Cell References with an explanation point in front of each
4) The named formula works anywhere in the workbook
5) If I have a cell selected that contains the named formula and I open the Name Manager (Defined Names), I can see the Relative Cell References change in the workbook.

 

[Peter_SSs]

Hi

Caveat about this notation:

This notation, !A1, that means cell A1 in the current sheet, is here since long before excel 2007, but is not usually used because it has always had problems with refreshing.

I think I already read something about this refreshing problem also ocurring in xl2007.

I can't find a link I had about this refreshing problems when using this notation. If someone has one, please post it.

I've been holding off responding to this thread to see if I came across this 'refresh' problem, and perhaps now I have.

While testing for this thread I found that when using the !A1 notation I couldn't get my two formulas in B2 and B3 (see post #4) to refresh, even if I pressed F9 to recalculate the sheet.

I then inserted some very simple Worksheet_SelectionChange code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Columns("B").Calculate
End Sub

This seemed to readily get B3 to re-calculate, but not B2. The only way I could find to get B2 to re-calculate was select B2 then F2 and Enter. (Possibly coincidence, but B2 was the cell I had selected when I defined the Name using the shorthand notation.)

Changing to the older style notation (and implementing the code shown in that other thread) seemed to work.

Do you think this is probably the 'refresh' problem referred to or may be something else?

 

[mgirvin]

Dear Peter_SSs,

Thanks for the post back! It is a known problem and it is helpful to see a concrete example of the refresh problem. Although I have not encountered it in my use of the !A1 relative cell reference, it is good to have two tricks up the sleeve.

I will definitely post back when I encounter this problem head on... just to add to the empirical evidence pile.

Thanks for being so persistent and diligent about this particular post!

 

[pgc01]

Do you think this is probably the 'refresh' problem referred to or may be something else?

Hi Peter

Sorry for not answering sooner but I was away from the board for Christmas.

Remark: I will assume the solution in post #8 of the thread in the link you posted, where you use the SelectChange event procedure to force recalculation.

I analysed the thread in the link you posted and in my opinion this is another problem.

1 -

The formula that you have in your name is not marked to be recalculated when you change the colour of a cell, independently of how you reference the cell.

To check this, test the name in Sheet1. If you change the Name reference from

=GET.CELL(63,!A2)

to

=GET.CELL(63,Sheet1!A2)

it will still not work althoug you are now using the sheet name in the cell reference.

My conclusion is that this problem with the recalculation of the formula has not to do with the notation you use. It's simply that changing a cell does not cause the formula to be marked as needing to be recalculated.

2 - Why did it start to work OK when you changed the name reference?

You changed the name reference to:

=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1))

I believe that now it works OK because you are using the function Indirect(). Indirect forces recalculation of a formula any time a cell is checked for calculation.

To check this change the name reference to:

=GET.CELL(63,!A2)+ISNUMBER(INDIRECT(""))

INDIRECT("") returns error and so ISNUMBER(INDIRECT("")) is always false. This means that you are adding 0 and so the value remains the same.

Now, however, it will refresh OK. When you use the Columns("B").Calculate the Indirect() in the formula will force the name to be recalculated. And you are still using the implicit "active sheet" cell reference.

Remark: Another way to solve the problem would be to keep the original name reference, but change:

        Columns("B").Calculate

to

        Application.CalculateFull

This would force the formula to be recalculated, but at the expense of recalculating also all other formulas which is very inefficient.

 

[Peter_SSs]

PGC

Thanks for the additional information.

I think I actually had an issue with the particular test sheet I was using. I had the sheet as shown in post #4 of that thread with cCol defined as =GET.CELL(63,!A2) and the formula shown (B2) also copied down to B3. What I couldn't understand is why B3 would re-calculate with F9 or with
Columns("B").Calculate
but B2 would not. I have since tried the set-up in fresh sheets and both cells update with either method.

Hope you had a good Christmas!

 

[mgirvin]

Dear pgc01,

Thanks for pointing out that the INDIRECT part of the formula was the element that was causing the re-calculation! And your test with:

+ISNUMBER(INDIRECT(""))

is very clever!

 

[pgc01]

Thank you,

and my best wishes for a happy and prosperous New Year 2010 for us all.