Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a cell value from another cell.
Here's what I'm trying to do. I've created a drop-down list in a cell. I use
IF statements in two other cells that change the value of those cells depending on the value selected in the drop-down list. This works fine for the automatic value entry in the cells with the IF statements, but I also need to allow the user to select a "Manual Entry" value from the drop-down list that requires the user to manually enter values in the cells that have the IF statements. Of course when they do this, the IF statements are overwritten by the manually entered value. So if the user wants to select another list option, the automatic value entry no longer works. Is there some way to make the two cells with the IF statements allow manual entry without overwriting the functionality of the IF statements? What I'm hoping is that perhaps a third cell can be used to determine the value of the cells that currently contain the IF statements. For example, perhaps this third cell can contain an IF statement related to the drop-down list, then conditionally supply the value to the other cell depending on the selection in the drop-down list. That way entering values into that cell would not overwrite a formula in that cell. Any help would be greatly appreciated. I'm using Excel 2003, SP3. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a cell value from another cell.
We have encountered similar situations where we need cell formulas to bring live data into a workbook, but need a way to manually override the live data if there are problems with it. The relative inelegant solution we came up with was to have the 'automatic' value in cell C1, a place for manual entry in cell B1 and a formula in cell A1 that was: =IF(B1="",C1,B1) or the perhaps more descriptive =IF(ISBLANK(B1),C1,B1) Any calculations involving the values of C1 or B1 instead reference/use the value of A1. Again, not elegant, but for our purposes, the ability for the user to see what the automatic value is was helpful - and we wrote out to a data historian the manually entered override if it was not blank, so having it in a cell was handy. -- jamescox ------------------------------------------------------------------------ jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=113317 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a cell value from another cell.
Your solution is close to what I need, but I'd still like to do a bit more.
It would be very good if the manual entry field (B1 in your example) would revert to blank or a zero value if the automatic entry field is used. Your example is similar to the way I have it set up now, but it allows values to appear in the manual entry field even after a selection is made that enters values in the automated field (C1). This could be really confusing for the user. Thanks for your reply. "jamescox" wrote: We have encountered similar situations where we need cell formulas to bring live data into a workbook, but need a way to manually override the live data if there are problems with it. The relative inelegant solution we came up with was to have the 'automatic' value in cell C1, a place for manual entry in cell B1 and a formula in cell A1 that was: =IF(B1="",C1,B1) or the perhaps more descriptive =IF(ISBLANK(B1),C1,B1) Any calculations involving the values of C1 or B1 instead reference/use the value of A1. Again, not elegant, but for our purposes, the ability for the user to see what the automatic value is was helpful - and we wrote out to a data historian the manually entered override if it was not blank, so having it in a cell was handy. -- jamescox ------------------------------------------------------------------------ jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=113317 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a cell value from another cell.
You may be able to get there by using the Worksheet_Change event. This would allow you to detect changes in the cell with data validation and, when appropriate, setting the value of B1 to "" Note, however, that your logic on setting this up has to be based on the user manually changing the cell with data validation - a cell whose value changes as a result of a recalculation will not trigger the Worksheet_Change event. -- jamescox ------------------------------------------------------------------------ jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=113317 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a cell value from another cell.
I'm afraid you're going beyond my limited knowledge of Excel. I have no idea
how to implement the scripting for this event. "jamescox" wrote: You may be able to get there by using the Worksheet_Change event. This would allow you to detect changes in the cell with data validation and, when appropriate, setting the value of B1 to "" Note, however, that your logic on setting this up has to be based on the user manually changing the cell with data validation - a cell whose value changes as a result of a recalculation will not trigger the Worksheet_Change event. -- jamescox ------------------------------------------------------------------------ jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=113317 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a cell value from another cell.
If you've never done any VBA programming and have no other foreseeable needs for that skill, you are probably going to need more help integrating this potential solution into your workbook than it's reasonable to try to give via this forum. If you have done some other VBA coding and just aren't familiar with the Worksheet_Change even, I can give you some jump-start code. As an alternate to either of those, let's fall back and think about what you are really trying to do. It sounds like you want a data validation cell that will show your user some good choices, but will also allow your user to input values that aren't in the data validation list. If that's accurate, a ActiveX (not a Forms Control) Combobox can give you that capability. It can accept a range of cells as the list of items (and a heading to display from a cell directly above that range of cells) and the selection from the list OR the user-entered text (or numeric value) can be linked to another cell. If you can implement your data entry with that sort of fuctionality, then that's probably your best course of action. -- jamescox ------------------------------------------------------------------------ jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=113317 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Cell Background Color based on data from another cell | Excel Discussion (Misc queries) | |||
Changing a color in a cell based on the text in another cell | Excel Discussion (Misc queries) | |||
Cell colors or text color changing when date in cell gets closer. | Excel Worksheet Functions | |||
Changing one cell colour by inserting date in another cell | Excel Worksheet Functions | |||
how do i record changing cell data (cell is dde linked) | Excel Discussion (Misc queries) |