COUNTA and formatting

Question

Trying to create a sheet for a marginally competent group of users, hoping they need only highlight a row (across multiple columns) so that I can then count and the process marked data.  Is it possible to use cell formatting (like a highlight color) for a COUNTA function, or is there an option besides adding a column and having users key in a place-holder character? Thx.

Answer 1

Hi Richard,

Being able to see what the worksheet looks like was one of my questions. I assume they are library books/references. Is this assumption correct?

However, my other concern is "how to determined if the row should be highlighted"?. Does it follow some logic whereby a formula can be used like "Older than a certain date" and "never checked out". It is this type of logic that can be used to count the highlighted lines (possibly with a UDF (User Defined Function).

If no particular logic is applied and the User simply highlights the row based on some intrinsic information then the additional column is probably the way to go and then that column can be used both to apply the highlighting and count the instances of highlighting.

Answer 2

That works.  I was hoping highlighted rows could be counted, but an added column is perfectly adequate.  Thank you.

Answer 3

No problem. This one shows rows 37 and 38 marked as I hope can be marked. The other columns show use tallies and dates of last use. Would like to count the number of highlighted rows in the file (which can be from hundreds to tens of thousands of rows). Was thinking COUNTA might do it but don't know the format to identify highlighting (or if it is possible).

The alternative would be to add a column before A and simply add a character manually where the row is highlighted. COUNTA works fine with that; just trying to save myself a long, tedious step.

Richard.

Answer 4

Really need to see an example of the data. Perhaps upload a screen shot.

There are lots of variations as to how Conditional Formatting can be applied. If the user could add a column and key in a place holder, how would they determine if the place holder should indicate that the row should be highlighted because if it is done with the use of a formula then much the same methodology can be applied to Conditional Formatting without using the additional column.

To upload a Screen shot if you are using win 2007 or later.

• Search for the SnippingTool in Windows Search field.  (or Snip % Sketch)
• Right click file name and select "Pin to the taskbar".
• Click the Snipping Tool icon in the Taskbar to open Snipping Tool
• Select "New" icon in the Snipping Tool window.
• Drag cursor over the required area.
• Save the screen shot to a file name. (jpg)
• Upload to this forum by clicking the "Insert Image" icon in the tool bar attached to the top of the editing box in this forum.
• Browse to where you saved the image and then click Open button.
• Click Upload button.

Tips when creating Screen Shots:

• Keep the screen shot to a minimum amount of the screen otherwise when uploaded it is zoomed to fit in the post and with large areas of screen, the print becomes too small to read.
• Try to include the column and row Id's in the Screen shot. (Might require hiding some columns and rows so only the relevant columns and rows are included in the screen shot).
• If you lose a DropDown when selecting "New" in the Snipping Tool and you would like to include the DropDown in the screen shot then in the Screen shot tool bar select Delay and set it to 5 secs. Then after clicking New you have 5 secs to display the DropDown and then just wait until the 5 secs are up and the Snipping Tool is ready for selecting the required area of the screen

More information on Snipping tool at following Microsoft Link.

https://support.microsoft.com/en-us/help/4027213/windows-open-snipping-tool-and-take-a-screenshot