Tally that shifts cells daily.

[Davenil]

Hello everyone,

I’m trying to work on a small project at work and I’m having a lot of trouble figuring out if excel can even do what I’d like it to do. I’ve tried going about this a couple different ways, but I’m at a loss. I don’t have a ton of experience with excel, so maybe I’m just missing something? Any help would be greatly appreciated.

The issue we’re having is we are having transmitters losing connection for half a second periodically and then going back to normal. It’s been going on for weeks and we’re trying to keep track of when they lose connection and how many times it happens a day. So, we’re having operators write down every time a certain transmitter goes out. It’s now getting into the +100 times a day though and having to constantly write down every single time one goes out is getting annoying.

I’ve been trying to create an excel document where I have a list of all the transmitters in a table on their own individual lines with their descriptions and then I wanted to add a daily counter to each of those rows. Either the counter would reset at midnight or I can just have the operators reset the count to 0 at midnight every night.

My original thought was to have a spin button on each row for each transmitter. Then, when a transmitter dropped out, the operator would just have to click up and it would increase the running tally for that day. Then every day at midnight the tally would reset to 0. Only problem is to get the counter to reset to 0 everyday at midnight I would need some kind of code in the VBA editor and the program would need to be closed and reopened in order to get it to make the change. Plus, that doesn’t help with keeping historical data and tracking things.

Another thought was, instead of keeping a running tally, every time the operator clicked the spin button up it would automatically add a new line to a table on a separate sheet with the transmitter number and the date and time. So, instead of just keep track of how many times the transmitter dropped out, it would populate a table with a new line at that specific time and we would just have a big list of every dropout. I just don’t know if that’s possible in excel and I feel like that would be pretty tough to build a dynamic bar graph chart to give a visual on how often each transmitter is dropping out each month.

So, then I was thinking maybe there was a way to link the spin buttons to specific cells only during specific days. For example, on Monday when you clicked up, it would increase Cell B2 on the “May” sheet, which could be the May 20th line for that specific transmitter. Then on Tuesday when, you clicked up, it would increase Cell B3 on the “May” sheet, which could be the May 21st line for that same transmitter. Once it gets to June, the button would switch to the “June” sheet and start over on the June 1st line. Only problem is I can’t find any kind of function or code that would dynamically move the Cell that a spin button was linked to based on the Today() function. Or at least one that you didn’t have to close out and reopen the program for it to work properly.

Then I was trying to think of some kind of IF THEN formula that would begin a tally in a certain cell and then stop tallying once midnight hit and would start a new tally in a different cell. The only problem is I can’t figure out how to hold the previous value once that time changes. As soon as midnight hit, the “THEN” part of the statement would activate and whatever the tally was at the time, it now moves to the “False” part of the statement and that number resets to that new value. I don’t know how to hold that previous value once the “Then” part kicks in.

I was trying to think of maybe instead of transferring a specific value, that spin button value would instead fill in columns on a hidden sheet with 1’s and then maybe I create an IF THEN statement that keeps a tally of how many “1s” are in each column only during specific times of the day and then just stops adding to the tally once it hits midnight. The 1's would all get removed once the spin button value gets reset to 0 and it'd start all over again in the next day's cell. Maybe that way could work so that the previous value wouldn’t change once the day switches? I just can’t figure out what that formula would even look like. I don’t know enough about excels capabilities.

Basically, ideally the operator would just have to click up on the spin button on a “Transmitter” page every time a certain transmitter drops out. That would then increase a value on a table of rows of dates and columns of transmitters, keeping a running tally on a separate “May, June, July, etc.” sheet.

I thought it would be cool to then build a dynamic bar graph chart linked to those monthly tables that management can watch. That way, as the operators are clicking the spin buttons over time, the tables are getting populated and the charts are building themselves. We could then keep track of how many times certain transmitters drop out each day of each month. Watch if the rates are increasing or decreasing. Stuff like that.

Anyways, hopefully that wasn’t too confusing of an explanation. If anyone has any ideas that might help me out, I’d love to hear them. We’re hoping these issues get fixed by the end of this year. So, it’s not that big of a deal. I just thought it would be fun to see if I could build something like this and it’d help the operators out quite a bit these next couple of months.

Thanks,

Dave

 

[portews]

My first instinct would be to make a one sheet workbook with cell A3 (give yourself a couple of rows for titles and headers) being an input cell for the transmitter number. Make this a validated dropdown for the number of transmitters. They can type a valid number or use the dropdown in if they prefer. Next to it is a button to add the time and transmitter number to a log. You can put each transmitter count on a separate column with a COUNTIF formula to show you the stats. I'd put the log on the same page, just to see the raw data, just make sure to freeze the top few rows to keep the button and stats from scrolling down. If you don't want to see it, you can always have it on another page.

VBA for the button would look something like this:

Sub Button1_Click()
'https://www.mrexcel.com/board/threads/need-help-keeping-a-tally-that-shifts-cells-daily.1259086/

'find the last used row on the sheet
LastRow = Sheets("Sheet1").Range("b" & Rows.Count).End(xlUp).Row

'make sure they put something in A3
If Cells(3, 1) > "" Then
    'Put the Transmitter number in the A column
    Cells(LastRow + 1, 1) = Cells(3, 1)
    'put the date and time in the B column
    Cells(LastRow + 1, 2) = Now()
    'clear the contents from your selection
    Range("A3").ClearContents
Else
    'if they didn't, remind them to put a number in A3
    x = MsgBox("Select Transmitter Number in Cell A3", vbExclamation, "Transmitter")
End If

End Sub

 

[Davenil]

My first instinct would be to make a one sheet workbook with cell A3 (give yourself a couple of rows for titles and headers) being an input cell for the transmitter number. Make this a validated dropdown for the number of transmitters. They can type a valid number or use the dropdown in if they prefer. Next to it is a button to add the time and transmitter number to a log. You can put each transmitter count on a separate column with a COUNTIF formula to show you the stats. I'd put the log on the same page, just to see the raw data, just make sure to freeze the top few rows to keep the button and stats from scrolling down. If you don't want to see it, you can always have it on another page.

VBA for the button would look something like this:

Sub Button1_Click()
'https://www.mrexcel.com/board/threads/need-help-keeping-a-tally-that-shifts-cells-daily.1259086/

'find the last used row on the sheet
LastRow = Sheets("Sheet1").Range("b" & Rows.Count).End(xlUp).Row

'make sure they put something in A3
If Cells(3, 1) > "" Then
    'Put the Transmitter number in the A column
    Cells(LastRow + 1, 1) = Cells(3, 1)
    'put the date and time in the B column
    Cells(LastRow + 1, 2) = Now()
    'clear the contents from your selection
    Range("A3").ClearContents
Else
    'if they didn't, remind them to put a number in A3
    x = MsgBox("Select Transmitter Number in Cell A3", vbExclamation, "Transmitter")
End If

End Sub

Ah! Yea, that could definitely work! I was able to get the code to work for a test run, but I've been trying to tweak things and I'm running into trouble.

So my thought was to have one sheet as the operators sheet, where they select the transmitter and log it. That sheet would include the tag number, description, corrective actions, a running total of dropouts for that month, and then the date and time of the most recent log so operators know the button worked when they push it and it logged the correct transmitter they were expecting. Then I'd just copy that template for June, July, etc.

Then I was going to have a separate sheet as a log sheet that tracks all of those log inputs. I could then add a bar graph to the Data sheet, or the operators sheet, keeping track of the total dropouts for that month. That way the Instrumentation Techs and Management can look at the full log if they want.

I just don't know how to get the code to log the information on a separate sheet. I was trying to edit the code you created, but I keep getting errors like "Subscript out of range." Ideally it'd basically do the same thing, just put the information onto the May Data sheet instead of the May 2024 sheet. Then the month's running total would be on May 2024 and the Most Recent Dropout would have a formula to populate the cell with the most recent log Date/Time for that transmitter. I guess if the code gets too complicated though, then it doesn't really matter that much. I can just put everything on one sheet. I just figured operators don't care about the full log sheet of information. They just need to log the dropouts. So, rather than clogging up their sheet with a million lines of data, I'd try and keep their sheet simple with as little information on it as possible. And then populate a separate sheet with all that log data. I don't know how to tweak this code any further:

Sub LogDropout_Click()
'https://www.mrexcel.com/board/threads/need-help-keeping-a-tally-that-shifts-cells-daily.1259086/

'find the last used row on the sheet
LastRow = Sheets("Sheet2").Range("b" & Rows.Count).End(xlUp).Row

'make sure they put something in A3
If Cells(3, 1) > "" Then
    'Put the Transmitter number in the A column
    Cells(LastRow + 1, 1) = Cells(3, 1)
    'put the date and time in the B column
    Cells(LastRow + 1, 2) = Now()
    'clear the contents from your selection
    Range("C2").ClearContents
Else
    'if they didn't, remind them to put a number in C2
    x = MsgBox("Select Transmitter Number in Cell C2", vbExclamation, "Transmitter")
End If

End Sub

I wasn't sure what the "b" was in the Range line, but figured that might be part of my problem? And I wasn't sure if the "Sheet2" needed to be labeled "May Data" or if it needs to remain "Sheet2"?

Another issue I was running into was trying to have it log the information onto the bottom line of a table rather than just the lowest empty line. I was hoping to use a table so Instrumentation Techs can use the dropdown tabs to select specific Transmitter numbers and filter all the others out. Not sure why they'd want to, but I thought it might be a nice feature. The problem is the code considers the table as "something" and then starts logging the info on the next line below the table, rather than inside the table.

The weird thing though is if I input data into the first line of the table, it'll then start using the table like normal, but if the table is empty, it won't recognize it. Or if I try deleting lines, that sometimes messes things up. Just in case an operator accidentally logs the wrong transmitter, it'd be nice to be able to just delete that line from the table.

I added a couple screenshots of each page if that helps?

 

[portews]

The "b" is for column B on the spreadsheet (find the last row in column B). The sheet name can either be the real name in quotes, i.e. "May 2024" or the logical sheet name Sheet2, Sheet3, etc. (the order they were created), not in quotes. Either way, you don't want it hard coded, you'd have to make it a variable for the right month, increment it as necessary, test if the sheet exists and create it if it doesn't.

The reason the code you show doesn't work is your not telling what sheet to put the data in. in "Cells(LastRow + 1, 1) = Cells(3, 1)", neither side is specifying what sheet those cells are in, so it assumes they're all on the current sheet. You'd have to go with something like

Sheet(currmonth).Cells(LastRow + 1, 1) = Sheet("DataSheet").Cells(3, 1)

where currmonth is a variable where you've found the latest month.

Here's a nice example for finding the last row in a table:
How To Find Last Row using Tables [Excel VBA] – Skills and Automation