Replies

“then I will hide the row”

I take it you mean you will manually hide the row? and without knowing what exactly is in the row of cells you want to review I assumed number entries. If you have numbers across column A to J, in K1 you could place

=COUNTBLANK(A1:J1)

you can copy this down as far as needed. If it calculates to 10, meaning 10 blanks, then you can manually hide that row.
You would need some VBA code if you want the formula to calculate and then for Excel to automatically hide the row.

yoyoPHIL

“then I will hide the row”

I take it you mean you will manually hide the row? and without knowing what exactly is in the row of cells you want to review I assumed number entries. If you have numbers across column A to J, in K1 you could place

=COUNTBLANK(A1:J1)

you can copy this down as far as needed. If it calculates to 10, meaning 10 blanks, then you can manually hide that row.
You would need some VBA code if you want the formula to calculate and then for Excel to automatically hide the row.

yoyoPHIL

There is a problem with your formula when there is no number in the A cell but the B cell has a number. I believe that we want to show — in this instance. Your formula will calculate to 0 because an empty cell divided by a number is 0 according to excel and the 0 is a number.

See attached

yoyoPHIL

There is a problem with your formula when there is no number in the A cell but the B cell has a number. I believe that we want to show — in this instance. Your formula will calculate to 0 because an empty cell divided by a number is 0 according to excel and the 0 is a number.

See attached

yoyoPHIL

Its PHIL not Bill but at least you didn’t call me Woody (Hans has been mislabeled Woody already).

What I get from your answer to my first question was that your spreadsheet is acting as though it is set to English/US and not English/UK as you think. As Hans stated, “Excel uses the date format set in the Regional Options / Regional and Language Options control panel, EXCEPT for cells whose number format property has been set explicitly to a format that deviates from the Control Panel setting.”
If your control panel setting is set to English/UK then I believe you have a setting somewhere which tells the file/sheet to use English/US.
Did you try opening a new file and entering dates and did it act the same way? If the new file acted correctly, that is, the date was properly configured in English/UK, then the problem is within your original file and not the system settings. Hopefully this gets you on the right track to finding the problem.

yoyoPHIL

Its PHIL not Bill but at least you didn’t call me Woody (Hans has been mislabeled Woody already).

What I get from your answer to my first question was that your spreadsheet is acting as though it is set to English/US and not English/UK as you think. As Hans stated, “Excel uses the date format set in the Regional Options / Regional and Language Options control panel, EXCEPT for cells whose number format property has been set explicitly to a format that deviates from the Control Panel setting.”
If your control panel setting is set to English/UK then I believe you have a setting somewhere which tells the file/sheet to use English/US.
Did you try opening a new file and entering dates and did it act the same way? If the new file acted correctly, that is, the date was properly configured in English/UK, then the problem is within your original file and not the system settings. Hopefully this gets you on the right track to finding the problem.

yoyoPHIL

I think something like this would work for you. It places — for errors and also when either or both cells involved in the needed formula are empty and runs the formula when both cells have numbers.

yoyoPHIL

I think something like this would work for you. It places — for errors and also when either or both cells involved in the needed formula are empty and runs the formula when both cells have numbers.

yoyoPHIL

A few questions.
1) When you type 27/01/2004 into an English-US cell, you would get 27/01/2004 even when the format is set to date dd/mm/yyyy. Excel would not be able to convert it and thus would leave it as-is which would appear to be English-UK correct. Maybe those entries that you believe are working correctly are not really formatted in English-UK and are just being read as text? If you try entering 27-01-2004 does it convert to 27/01/2004 or does it keep the dashes? I’m just trying to decipher whether the file is being read/formatted in both English/UK and English/US. If you would find that only English/US is really being used it would narrow down your search for the problem.

2) Does this only occur on this particular spreadsheet or have you noticed problems on other files you run on your system?

3) Did you create this spreadsheet or did whoever made it place some coding that you are unaware of into it which adjusts formatting for some reason?

yoyoPHIL

Once a conditional format is created in a cell, it can be copied down, accross or even to specific cells on the sheet. Say the conditional format is already created in cell A1 and you need it in cell C5, E8 and F10. You can copy A1 and then select C5, E8 and F10, use paste special and under paste, select formats. Conditional formatting will be copied as well as any other formatting from A1.
As stated by the other loungers, attention needs to be paid to absolutes and named ranges.

yoyoPHIL

Once a conditional format is created in a cell, it can be copied down, accross or even to specific cells on the sheet. Say the conditional format is already created in cell A1 and you need it in cell C5, E8 and F10. You can copy A1 and then select C5, E8 and F10, use paste special and under paste, select formats. Conditional formatting will be copied as well as any other formatting from A1.
As stated by the other loungers, attention needs to be paid to absolutes and named ranges.

yoyoPHIL

Chipshot;

I like the formula. I added a slight adjustment. If the hymn was never sung or maybe only once or twice, etc., the result would be a 0 or if a date format is set you would get 1/0/00. My addon to your work stops this from occurring and leaves the result blank. See attached

yoyoPHIL

Chipshot;

I like the formula. I added a slight adjustment. If the hymn was never sung or maybe only once or twice, etc., the result would be a 0 or if a date format is set you would get 1/0/00. My addon to your work stops this from occurring and leaves the result blank. See attached

yoyoPHIL

The formula Hans supplied will not account for multiple winners. If both A and E have a high of 10 responses, whichever letter is encountered first in the range will be selected.
Try this formula, again if the range is A1:A100

=”A-“&COUNTIF(A1:A100,”A”)&”/ B-“&COUNTIF(A1:A100,”B”)&”/ C-“&COUNTIF(A1:A100,”C”)&”/ D-“&COUNTIF(A1:A100,”D”)&”/ E-“&COUNTIF(A1:A100,”E”)

It will give you the count for each letter in one cell. Hope it helps.

yoyoPHIL

The formula Hans supplied will not account for multiple winners. If both A and E have a high of 10 responses, whichever letter is encountered first in the range will be selected.
Try this formula, again if the range is A1:A100

=”A-“&COUNTIF(A1:A100,”A”)&”/ B-“&COUNTIF(A1:A100,”B”)&”/ C-“&COUNTIF(A1:A100,”C”)&”/ D-“&COUNTIF(A1:A100,”D”)&”/ E-“&COUNTIF(A1:A100,”E”)

It will give you the count for each letter in one cell. Hope it helps.

yoyoPHIL