Creating a numerical countdown:show in spreadsheet

**timothystow** · 02-24-2009, 10:28 AM

I am working on a spreadsheet that pulls a report from data. I am using a "counta" formula to show how many listings I have based on the information listed in Col. A. I am showing (ex. Count 34) at the top of the spreadsheet. Now what I am trying to do is as I am copying the info under LastName and pulling the needed data from another source, I would like to figure out a formula that as I go down the list, the total number will increment down showing me what I have left to do on the list from the original total amount. Is this possible?

Also I would like to find a way to either have the words on the line previously that was done change colors or have the whole cell line across change to a different color showing that that line has been done. Can this be done as well? I am new to learning formulas and VBA programming language and I am eager to learn. Thanks for any help in this regard.

**NBVC** · 02-24-2009, 10:36 AM

Unless you can use one of the cells in a row as a reference to what is past and what is new, then you probably can't do that with Excel built-in functionality.. you may need VBA to create date stamps to go by....

Let us know what fields you have in the workbook that can possibly be use to determine the breakpoint (e.g. date created, row number (or record number), etc...)

Maybe a sample of your sheet attached might help too.

**timothystow** · 02-24-2009, 11:38 AM

In the attached file, you can see the report that I am working on. At the top there is a Count 34 in which I am using the "counta" formula. As I copy the lastname (as can be seen as highlighted) I then make sure the last two columns match in choices and then I make them "Bold." I hope that the attachment helps out a little. Then I go the next line down and repeat the procedure by highlighting the LastName.

**NBVC** · 02-24-2009, 11:47 AM

Doesn't really make sense...

How do we know what you now copy/pasted...

I see 34 lines, but what will the next line show that these don't... will there be a new PAMDate?

Also, please attach a excel spreadsheet so that we can test solutions, if needed.

**timothystow** · 02-24-2009, 12:00 PM

The only changes that occur as I go down the list is the column choices under "Downpayments" and "Installments" change from normal black to bold black and I do that by highlighting the two and changing the color type. Then I go to the next line down and "copy" the next last name on the list. See attached spreadsheet example. Thanks for any help...

**NBVC** · 02-24-2009, 12:07 PM

Still not sure if I understand you exactly.. but maybe this?

You can't use basic Excel to count the number of "highlighted" items, but if perhaps in the adjacent column you place an "x" or similar marker next to the completed items, then you can use the same Counta() formula to count the number of items.. then subtract the 2 Counta() results to get your "number left to do".

Otherwise, you will need VBA.. which is someone else's to help you with...

**timothystow** · 02-24-2009, 01:50 PM

This is not what I am looking for. I guess I need to look elsewhere. Someone here is bound to understand what I need to accomplish. It should not be that difficult to program.