[Solved] Shootin' Blanks

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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
At what point would you want to undo all the alternate entries and revert to formulas?
 

BrianDP1977

Board Regular
Joined
Nov 5, 2005
Messages
146
It’s an option that I would always want available to the user. Basically I want the user to be able to click on the cell and be presented with one of those down arrow menu things (like when you have a list data validation associated with the cell) and have a choice between leaving the current equation in the cell or converting it to a blank cell for typing normal comments in. This way, if no custom entries are placed in the cell, the cell simply keeps updating normally with generated names resulting from the equation. If a custom entry has been placed in the cell, that custom entry is what is displayed. However, if the user would like to change back to the original equation (and the result provided by that equation – that is the name automatically produced by the equation), all he would have to do is click on the cell, be presented with the down list arrow, click it, and chose the equation format again (i.e. the name that the equation is currently producing). I know I’m doing a terrible job of explaining myself. I wish I knew how to post a real example of the spreadsheet and what I’m trying to do and the problems I’m having with it. Is it easy to post an example of the actual sheet (like I’ve seen many people do)? If so, is there a quick explanation of it? If not I could always e-mail an example of the spreadsheet to someone willing to help. Thanks.
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
You might be able to do something like --
Book1
ABCD
1A1A1
2B2DAAA
3C3d4
4D4
5E5
6
7
8
Sheet3
<font face=Courier New><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> worksheet_selectionchange(<SPAN style="color:#00007F">ByVal</SPAN> Target<SPAN style="color:#00007F">As</SPAN> Range)<SPAN style="color:#00007F">If</SPAN> Intersect(Target, [D1:D5])<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN><SPAN style="color:#00007F">If</SPAN> Target.Cells.Count > 1<SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN>
Application.EnableEvents =<SPAN style="color:#00007F">False</SPAN>
Target.Formula = "=VLOOKUP(" & Target.Offset(0, -1).Address & ",A1:B10,2,0)"
Application.EnableEvents =<SPAN style="color:#00007F">True</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

What this does is monitor for the selection of a single cell only in the range D1:D5.

When there is such a selection, it seeds a formula which can then be over-written.
 

BrianDP1977

Board Regular
Joined
Nov 5, 2005
Messages
146

ADVERTISEMENT

This could possibly work. The only problem I see is that each cell’s equation is location specific due to the fact that it references the date in the column relative to its location (i.e. from my first example when this thread was first posted, the equation that produces the name references the date directly above it in it’s respective column to help determine which name should be displayed). Therefore, my thinking (based on very limited Excel experience) is that I would have to write a separate macro for each cell so that each seeded equation would be individualized to that specific cell. However, I’m sure that there is a different way to get the formula to reference the correct date reference in the seeded equation dependant on which cell is selected (i.e. if a cell in the 3rd column of the range of cells is selected, the seeded formula will index the date in the 3rd column date header as part of the seeded formula). I’m thinking I just need to add an index function as part of my seeded formula. Thanks for the help.
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Just explain to me what the formula should be if cell E3 were to change?

And if B6?
 

BrianDP1977

Board Regular
Joined
Nov 5, 2005
Messages
146
Any chance of shooting you an example spreadsheet of what I’m trying to do? Everything would probably make a lot more sense. Just shoot me a quick e-mail and I’ll forward the example spreadsheet with a little better explanation to you. If anyone else would like to take a shot at it, please feel free to send me an e-mail and I’ll forward the spreadsheet on. Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,910
Messages
5,574,994
Members
412,633
Latest member
simon_elvin
Top