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

 

[Queryman]

Okay, I am making progress. I am now able to get your code to work.

You had helped me with creating this macro
Private 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


Is there a way to record the value of cells c5:c8 separated by a space at the same time the above macro is executed, I need to record those valued somewhere on the workshhet, and retrieve them later with your message box code.

Thanks



Michael

 

[SkipVought]

What is in C5:C8?

It's already there. Why store it somewhere else as well unless in a variable?

sValue = ""
For each c in range("C5:C8")
  With c
    if sValue = "" then
      sValue = .Value
    else
      sValue = sValue & " " &  .Value
    end if
  End With 
next

Skip,

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

 

[Queryman]

C5:C8 are the drop down list boxes, they would contain a value that is changed each time c8 is calculated, so I would need to store the value in c5:c8 somewhere, does that make sense?
Thanks,



Michael

 

[SkipVought]

Does NOT make sense to me at all!

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.




Michael