Excel- calculated cell from drop down boxes to be moved 1

[Queryman]

I have a worksheet that has several drop down boxes, the user gets to choose a single value from each list and then a calculation is done in excel on a cell which displays the value of the combination of the users choices.

I need a method to move that value to a different cell in the same worksheet based maybe on "IF" statements, that would conditionally move that value, based on section, row and column headers, example, if the year chosen is 2004, then the value would move to the 2004 column, and also there are about fifteen different drug name values, so it would have to go to that row based on drug name, so finally, there are two sections each with identical row and column names, but the section names are different, one is called "Most Likely" another is called "Optimistic". The move to the cells would have to be done one at a time, because the user only calculates one cell at a time using the drop down boxes. Is there a way to do his, I could create a macro button for the user to click to move it, but I am not sure how to code the move to the cell.

Any suggestions would be highly appreciated.



Michael

 

[SkipVought]

Michael,

No need to change to cell link. Just use 3 formulae and test the linked cell...
[tt]
=if(ChosenYear=F$1,LinkedCell,"")
[/tt]
assuming that F1, G1, H1 are year headings and LinkedCell contains the value to display in that column.

OR...

am I missing something?



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Queryman]

The calculated cell value will be changed everytime new selections are made using the drop down boxes and I need the linked cell to hold the value and not become blank.

Thanks,




Michael

 

[SkipVought]

The Linked cell NEVER becomes blank! The formula is in the target values columns.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

Why don't you explain in more detail, the design of your sheet.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Queryman]

Instructions
1) Choose scenario Most Likely
2) Choose Product Drug name 1
3) Choose Year 2004
4) Market Conditions No Competition
5) Sales in Doses (Millions) 4.40
6) % of yearly sales affected by competition 11.75%
7) Revised Sales in Doses (Millions) 4.92
8) Click on accept

MOST LIKELY 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013
Drug name 1
Drug name 2

Optimistic 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013
Drug name 1
Drug name 2



Michael

 

[SkipVought]

What relevance does 4) to 8) have?

Give me your eMail address and I'll send you a sample of what I just worked up.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Queryman]

No realvance, it's used to select a %range from 6

my email is Michael.Dasappan@aventis.com

Thanks



Michael

 

[Queryman]

I have an excel sheet that has several drop down lists
1) Choose scenario
Most Likely
2) Choose Product
Drug Name 1
3) Choose Year
2004
4) Market Conditions
No Competition
5) Sales in Doses (Millions)
4.95
6) % affected by competition
15.2%

the users get to choose one value from each list and from those selections, a calculation is performed. That calculated cell is moved to a different part of the worksheet using this macro

Sub Accept_data()

Dim RowLookup As Range, ColLookup As Range
Set RowLookup = Cells.Find([SelectedScenario] & " " & [SelectedProduct])
Set ColLookup = [YearHeadings].Find([SelectedYear])
Cells(RowLookup.Row, ColLookup.Column).Value = Range("c8").Value

End Sub

which the user starts by clicking a button on the worksheet.


In order for the user, not to be able to easily change the cell where the calculated cell value is moved to, I moved to range of cells to the middle of the worksheet, where it is not visible and made visible to the user another range of cells which refer to the part of the worksheet where the calculated cells are moved to using the macro, but here I locked the cells.

The purpose of going through these steps is to force the user to use the drop down list boxes to calculate instead of entering values directly in the cells. If you are still with me, what I need to show the user, when they click on one of the locked cells, which refer to that range which holds the calculated value, the values in the list boxes that was used to make the calculation.

Is this possible?

Thanks




Michael

 

[SkipVought]

Michael

Is this possible?

Is WHAT possible?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Queryman]

Is this possible?
What I need to show the user, when they click on one of the locked cells, are the values in the list boxes(cells) that was used to make the calculation.
Thanks,



Michael

 

[SkipVought]

Michael,

It is possible using Worksheet Events.

In the VB Editor, in the Project Explorer, you will see the Worksheet & Workbook objects. You can also get there by right clicking the Sheet Tab and select View Code.

Using the Worksheet_SelectionChange event, you can determine the selected cell and respond accordingly. For instance, if column A data were a Date and column B data were an amount, then if the user were to select a cell in column A you might do one thing and if column B were selected then another...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
    With Target
        If .Row = 1 Then Exit Sub 'this is the heading row
        If .Count > 1 Then Exit Sub 'more than one cell selected
        Set rng = Application.Intersect(, Target, Range(YourDataArea))
        If Not rng Is Nothing Then
            Select Case .Column
                Case 1
                    'do date thing
                Case 2
                    'do amount thing
                Case Else
                    'just get out
            End Select
        End If
    End With
    Set rng = Nothing
End Sub

Not sure exactly WHAT you want to do when a user selects a cell YourDataArea. Please explain in detail.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Queryman]

I have several drop down lists
1) Choose scenario
2) Choose Product
3) Choose Year
4) Market Conditions
5) Sales in Doses (Millions)
6) % affected by competition

the users get to choose one value from each list and from those selections, a calculation is performed. That calculated cell is moved to a different part of the worksheet using this macro

Sub Accept_data()
Dim RowLookup As Range, ColLookup As Range
Set RowLookup = Cells.Find([SelectedScenario] & " " & [SelectedProduct])
Set ColLookup = [YearHeadings].Find([SelectedYear])
Cells(RowLookup.Row, ColLookup.Column).Value = Range("c8").Value
End Sub

which the user starts by clicking a button on the worksheet.
In order for the user, not to be able to easily change the cell where the calculated cell value is moved to, I moved to range of cells to the middle of the worksheet, where it is not visible and made visible to the user another range of cells which refer to the part of the worksheet where the calculated cells are moved to using the macro, but here I locked the cells.

The purpose of going through these steps is to force the user to use the drop down list boxes to calculate instead of entering values directly in the cells.

If you are still with me, what I need to show the user, when they click on one of the locked cells (which refer to that range which holds the calculated value) are the values in the list boxes that was used to make the calculation when they made their original choices.

I am trying to get the users a workshhet where they
1. make choices using drop down lists in cells which creates a calculated field.
2. click on macro which moves that calculated field to appropriate place on worksheet which is hidden, and also to locked area which is visible to user.
3. Now I need to give the user an option to change the numbers, but since they can only do it using the macro and the drop down boxes, I need to show them what their original choices were from each of the drop down lists for each calculated locked cell.
Thanks,




Michael

 

[SkipVought]

Well the Row of the Selection would be the same as the RowLookup but the Column of the Selection would be offset by some amount.

ThisSelectedScenerioProduct = Cells(Target.Row, "AA").Value

assuming that column AA has the concatenated Scenerio & Product

ThisSelectedYear = Cells(1, Target.Column + 26

assuming that there is a 26 column offset

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
    With Target
        If .Row = 1 Then Exit Sub 'this is the heading row
        If .Count > 1 Then Exit Sub 'more than one cell selected
        Set rng = Application.Intersect(Target, Range(YourDataArea))
        If Not rng Is Nothing Then
           ThisSelectedScenerioProduct = Cells(Target.Row, "AA").Value
           ThisSelectedYear = Cells(1, Target.Column + 26
           MsgBox " Scenerio & Product: " & ThisSelectedScenerioProduct & " Year: " & ThisSelectedYear 
        End If
    End With
    Set rng = Nothing
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Queryman]

Hi Skip,
I added your code to the worksheet in a module, but I am unable to get this to work at all, nothing happens when I click on cells b13 to k36 Any suggestions?

Here is the code I added
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
With Target
If .Row = 1 Then Exit Sub 'this is the heading row
If .Count > 1 Then Exit Sub 'more than one cell selected
Set rng = Application.Intersect(Target, Range("b12:k36"))
If Not rng Is Nothing Then
ThisSelectedScenarioProduct = Cells(Target.Row, "AA").Value
ThisSelectedYear = Cells(1, Target.Column + 26)
MsgBox " Scenario & Product: " & ThisSelectedScenarioProduct & " Year: " & ThisSelectedYear
End If
End With
Set rng = Nothing
End Sub




Michael

 

[SkipVought]

This code assumes that the columns that you are hiding starts with AA (the column where the scenerio & product are concatenated) and that the corresponding YEAR column difference is 26.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Queryman]

I think it is. The columns I am hiding starts in column AA and the actual data resides in Ab13:KB36 with the column headers in row 12 same as the one I am not hiding which starts in column A and the data resides in b12:k36.

Am I missing something here?
Thanks,



Michael

 

[SkipVought]

well if your YEAR headings are in row 12

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
    Const HEAD_ROW = 12
    Const COL_OFF = 26
    With Target
        If .Row <= HEAD_ROW Then Exit Sub 'this is the heading row
        If .Count > 1 Then Exit Sub 'more than one cell selected
        Set rng = Application.Intersect(Target, Range("b12:k36"))
        If Not rng Is Nothing Then
           ThisSelectedScenarioProduct = Cells(Target.Row, COL_OFF + 1).Value
           ThisSelectedYear = Cells(HEAD_ROW, Target.Column + COL_OFF)
           MsgBox " Scenario & Product: " & ThisSelectedScenarioProduct & " Year: " & ThisSelectedYear
        End If
    End With
    Set rng = Nothing
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Queryman]

Thanks for the quick reply. I notice that I did not make myself clear.

What I needed the user to see are the values in the drop down list boxes in a messsage box. So I am thinking that when I have the user press the macro button to accept the calculated value, I need to record them those choices, somewhere on the worksheet for each drug for each year in order to be able to see them later.

BTW, I still can't get your changed code to work, do I need to create a macro button to run this? or it supposed to work when the cells are clicked on?
Thanks,



Michael

 

[SkipVought]

where do you have this code?

It need to be in the SHEET object of the sheet you are working on and NOT in a MODULE.

When the user SELECTS a SINGLE CELL in the range B12:K36, the selection change event code will "calculate" the correlated values in the hidden area.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884