usnapoleon
Board Regular
- Joined
- May 22, 2014
- Messages
- 79
- Office Version
- 365
- Platform
- Windows
For starters, I have never used checkboxes before, so I have spent the last few hours watching videos and reading instructions and I have not been getting anywhere. Sorry to take your time.
I had an idea on a spreadsheet I'm creating, and it occurred to me that checkboxes might be the best solution for my goal. ---- if you can think of a different idea, please let me know, I'm open to going in other directions.
That aside, let me share my idea, my code, and what I'm aiming to do, and with your expertise we can see where to go from here.
My business has to write off certain expenses and our account coding breaks it out by department and the expense. My idea is for my team to input the expense code (that is the blue areas in column B) and we'd check the box for the specific department it's related to (columns c-f). Depending on the checked box, column G will be formulaed to input the department code that we see in the black row. That formula is what I need help with.
Way over to the side in column T (not shown) I have a sumif formula that will look at column G and B and give totals for the 'Amount' in column H. I am just letting you know what we're doing with the overall results.
I can write an ugly code like =if(c5=true,"0010",if(d5=true,"0430",if..... blah blah blah... I am just going off the top of my head but it would be ugly regardless. And long. And ugly. And long... haha.
I currently have the top row of checkboxes (row 5) linked to each cell... so the first one is lined to C5, literally the cell that the checkbox is located at. Unfortunately it automatically types 'TRUE' or 'FALSE'. Nothing I can do about that, other than recolor the cells to white, change the text to white and nobody would ever know. Not sure how wise this would be.
Also, I had the idea that a fail-safe might need to be in place where if one box is checked on a row, the other boxes should uncheck. I read that somewhere.
You know... I might even edit the formula in column G (whatever we can come up with)so that it gives the department AND adds the Account Code in Column B. I think that would be easy, and it makes for an easier sumif formula in column T that I mentioned previously.
As you can see, there just seems to be a lot of work needed to get this going and I'm not sure if the end result would be worth it. Looking forward to some help.
I know there are other ways I can make the spreadsheet work. For example, I can always write a key somewhere that shows the department numbers that users like myself can reference, and instead of 4 columns of checkboxes I can make a single column that is similar to column B and manually type the department number each time. I just thought it would be easier on the user and also super-cool to use checkboxes - just check a box and be done. Knowing that other solutions such as this exist is also why I'm open to going into other directions - there could be another very cool thing we can do that would make it super easy on the user.
I've talked enough, sorry... let me share my spreadsheet... i did a preview of this and noticed it didnt show the checkboxes, like I display in my snip above. Weird!!
I had an idea on a spreadsheet I'm creating, and it occurred to me that checkboxes might be the best solution for my goal. ---- if you can think of a different idea, please let me know, I'm open to going in other directions.
That aside, let me share my idea, my code, and what I'm aiming to do, and with your expertise we can see where to go from here.
My business has to write off certain expenses and our account coding breaks it out by department and the expense. My idea is for my team to input the expense code (that is the blue areas in column B) and we'd check the box for the specific department it's related to (columns c-f). Depending on the checked box, column G will be formulaed to input the department code that we see in the black row. That formula is what I need help with.
Way over to the side in column T (not shown) I have a sumif formula that will look at column G and B and give totals for the 'Amount' in column H. I am just letting you know what we're doing with the overall results.
I can write an ugly code like =if(c5=true,"0010",if(d5=true,"0430",if..... blah blah blah... I am just going off the top of my head but it would be ugly regardless. And long. And ugly. And long... haha.
I currently have the top row of checkboxes (row 5) linked to each cell... so the first one is lined to C5, literally the cell that the checkbox is located at. Unfortunately it automatically types 'TRUE' or 'FALSE'. Nothing I can do about that, other than recolor the cells to white, change the text to white and nobody would ever know. Not sure how wise this would be.
Also, I had the idea that a fail-safe might need to be in place where if one box is checked on a row, the other boxes should uncheck. I read that somewhere.
You know... I might even edit the formula in column G (whatever we can come up with)so that it gives the department AND adds the Account Code in Column B. I think that would be easy, and it makes for an easier sumif formula in column T that I mentioned previously.
As you can see, there just seems to be a lot of work needed to get this going and I'm not sure if the end result would be worth it. Looking forward to some help.
I know there are other ways I can make the spreadsheet work. For example, I can always write a key somewhere that shows the department numbers that users like myself can reference, and instead of 4 columns of checkboxes I can make a single column that is similar to column B and manually type the department number each time. I just thought it would be easier on the user and also super-cool to use checkboxes - just check a box and be done. Knowing that other solutions such as this exist is also why I'm open to going into other directions - there could be another very cool thing we can do that would make it super easy on the user.
I've talked enough, sorry... let me share my spreadsheet... i did a preview of this and noticed it didnt show the checkboxes, like I display in my snip above. Weird!!
| Breck WO Template 11.23.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 3 | ▼TSW: BRECKADMIN ▼ | Dept : | 0011 | 0430 | 0012 | 0410 | |||
| 4 | Ranahan - TSW direct bills | Acct Code | Front Desk | Engineering | Housekeeping | A&G / Admin | Dept | ||
| 5 | FALSE | FALSE | |||||||
| 6 | |||||||||
| 7 | |||||||||
Monthly Activity | |||||||||
