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
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
