BrianDP1977
Board Regular
- Joined
- Nov 5, 2005
- Messages
- 146
Not sure if this will make sense but here it goes. I currently have a group of cells that each display individual names based on a formula (used in each cell) that searches through a database of information and picks a desired name dependant on various conditions. The problem is that sometimes the results in certain cells don’t necessarily make sense or don’t tell the whole story to why the name was picked (i.e. the function is working properly but the result could use a little extra explanation or there is more that needs to be displayed). As such, I would like the user to have the ability to click on the desired cell and be able to chose a blank cell option from a list instead of the formula result. I’m aware that the person could just delete the formula from the cell and type whatever in it’s place but I do not want to lose the automatic entry function of the equation. Instead, I simply want the person to be able to pick a blank cell mode from a list when he wishes to put a custom entry into the cell and then switch back to the equation mode later if he wishes. Here’s an example:
This is the result of the spreadsheet with all cells automated (i.e. no custom entries). The months at the top are reference dates that change as time progresses and are used to help determine the name to be listed in the cells below.
SEP..........OCT..........NOV
TOM.........TOM..........FRED
I would like to replace the “FRED” entry with a custom entry of “TOM / FRED” (or whatever the user wants) but not lose the ability to switch back to the automated “FRED” entry produced by the cell’s function later on. I was hoping that this could simply be accomplished with a validation criteria defined as a list and inputting the cell’s equation into the source entry and changing the error alert style to a warning (thus allowing the use to overwrite the formula with a personal entry but still leaving the equation result as a selectable option later on). However, when I’m in the validation menu it won’t allow me to paste the equation into the source area (and the equation is way too large to type it for every cell that I need). I tried naming each cell using the name box and then set up another spreadsheet that defined each cell as a validation criteria but this time using the previous spreadsheet’s named cell as the source and this works. However, I lose the automatic updates available through the original sheet (i.e. as time progresses and the names change, I would have to go back to each cell on the new sheet and manually pick the name again from the resulting validation list to get the cells to update). Is there a better way to accomplish what I’m trying to do? There has got to be a way to easily switch a cell back and forth between the equation or just a typed entry (without having to continually delete the equation and re-input it / corner drag from another source). Sorry for the long explanation and thanks for any help.
This is the result of the spreadsheet with all cells automated (i.e. no custom entries). The months at the top are reference dates that change as time progresses and are used to help determine the name to be listed in the cells below.
SEP..........OCT..........NOV
TOM.........TOM..........FRED
I would like to replace the “FRED” entry with a custom entry of “TOM / FRED” (or whatever the user wants) but not lose the ability to switch back to the automated “FRED” entry produced by the cell’s function later on. I was hoping that this could simply be accomplished with a validation criteria defined as a list and inputting the cell’s equation into the source entry and changing the error alert style to a warning (thus allowing the use to overwrite the formula with a personal entry but still leaving the equation result as a selectable option later on). However, when I’m in the validation menu it won’t allow me to paste the equation into the source area (and the equation is way too large to type it for every cell that I need). I tried naming each cell using the name box and then set up another spreadsheet that defined each cell as a validation criteria but this time using the previous spreadsheet’s named cell as the source and this works. However, I lose the automatic updates available through the original sheet (i.e. as time progresses and the names change, I would have to go back to each cell on the new sheet and manually pick the name again from the resulting validation list to get the cells to update). Is there a better way to accomplish what I’m trying to do? There has got to be a way to easily switch a cell back and forth between the equation or just a typed entry (without having to continually delete the equation and re-input it / corner drag from another source). Sorry for the long explanation and thanks for any help.