I need to find the Todays total count on column "J" on each sheet under categories "REPORTED" , "COMPLETED" , "SIGNED-OFF" and display total from each category in "TODAY" sheet. How can I do that in a formula rather than filtering and manually counting for each sheet? I greately appreciate any help
one way
On sheet "TODAY" in cells G1, G2 and G3 respectively put
Please Login or Register to view this content.
Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
Just when I think I am smart, I learn something new!
I did a couple of things to this file.
First of all, I converted the data into Excel tables. There are two reasons for this. Excel Table formulas use column header names, so they are easier to understand and debug. Secondly, Excel tables know how big they are, so you don't have to reference a million rows to be sure you have all the data. With tables you reference exactly the right amount of data every time.
I redid the formulas on the sheets to reflect this. I took a guess that you would also like to see today's total for each of the sheets as well as overall, so I added that column in. Then on the Today Sheet, I used what is called a 3-D formula.
=SUM('B01:L04'!H4)
This formula takes cell H4 on all the sheets from B01:L04 inclusive.
I'm afraid I upset your conditional formatting. I'll leave it to you to restore it.
One last thing. For the purposes of testing I set up a small table in columns M:O. If you enter a date in cell N3, the formulas will use that date. You can hide these columns if you want. Do not delete them. Also you can use cell N3 to look at any date you want.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Sorry, I meant "TODAY(LATEST UPDATE)" is to find out numbers with regard to " REPORTED(in Recorded column) " and "COMPLETED" (in STATUS column) and "SIGNED - OFF (in Signed off column) you could conside TODAY= LATEST DAY
Many thanks dflak. Your method work fine except one thing. Under Signed off count I need number off Signed offs with todays date in "SIGNED-OFF" column. Because when they signed of one item they will add signed off date only on SIGNED OFF COLUMN.(nothing will change on recorded column.) How do I change the formula to count no of SIGNED off in signed -off column with todays date? I hope I explained this better
You don't really need to change the formula. For the purposes of testing, I set up a small range of cells in Cells M2:O2. Cell M2 has today's date in it. If you enter a value in cell N2, it overrides this date. Cell O2 has the name: Show_Date. Cell O2 is today's date unless you have a value in cell N2 then it is the value in Cell N2. During testing, I set the value to 6/1 and got a number of "hits" on several of the sheets and on the Today sheet as well.
Thanks Lot
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks