Reading more than one set of data from a single cell

[Broadsider]

I am trying to set up a database in a single workbook with several
worksheets. I want to be able to see in a cell on the first sheet a number
that is the result of a formula using three other numbers on another sheet.
That's simple, but is there a way of then seeing those other numbers in a
drop down or in the same cell without having to go to their worksheet?

For instance, can I use a spinner to change an address in a cell to show
different sets of data?

I think the subject line says it more clearly.

Mike H

 

[Jim May]

Follow this example (and see if it helps you)..
Create a new Workbook (Sheet1, Sheet2, Sheet3)
Enter
On Sheet3 - Cell F3 50
G6 20
H9 25
G12 = (F3/G6)/H9 < <should yield 40
On Sheet1 - Cell D3 =Sheet3!G12 <<should yield 40

In a Standard Module enter (Via copy and paste):

Sub FindValues()
Dim MyMainCell As String
Dim ExpChar As Integer
Dim WSRef As String
Dim CellRef As String
Dim MySubCell As String
MyMainCell = ActiveSheet.Range("D3").Formula
ExpChar = InStr(1, MyMainCell, "!")
WSRef = Mid(MyMainCell, 2, ExpChar - 2)
CellRef = Right(MyMainCell, Len(MyMainCell) - ExpChar)
MySubCell = Worksheets(WSRef).Range(CellRef).Formula
Range("E3").Value = Worksheets(WSRef).Range("F3")
Range("F3").Value = Worksheets(WSRef).Range("G6")
Range("G3").Value = Worksheets(WSRef).Range("H9")
End Sub

With Sheet1 active Run FindValues
Cells E3,F3,and G3 should populate with the values you want to see..

HTH
It's a Sample Only.. <g>

 

[Broadsider]

Right, thanks,

I shall try that first thing tomorrow

Mike H