PDA

View Full Version : Obtain referenced cell info



albishop
09-02-2006, 06:43 PM
First time posting here so I will try my best to explain.

I have an Excel file that has a lookup formula in a cell which references the adjacent cell as the lookup value (sligtly more complicated as the real formula reads =IF(ISERROR(VLOOKUP(BH50,datarange,2,0)),"empty",VLOOKUP(BH50,datarange,2,0)). The lookup vaule can be to the right or the left, obviously resulting in 2 slightly different formulae.

The aim is to have an area at the top of the file which shows the lookup value of a cell (that contains the above formula) when the cell is active. The code to show the lookup value would be executed by pressing a button.

Any ideas would be greatly appreciated.
Al

matthewspatrick
09-02-2006, 07:05 PM
Sorry Al, but it's not clear what you're trying to do.

Can you post a sample file? You can upload a file if you go into the 'advanced' writer and click the paperclip icon.

albishop
09-02-2006, 07:24 PM
Thanks for the quick reply. I have attached a very trimmed down file that perhaps explains what I am trying to do.

I have not associated any code with the button as I'm unsure where to start.

Thanks again.
Al

Bob Phillips
09-03-2006, 05:20 AM
Sub test()
Dim sVal
On Error Resume Next
sVal = Application.VLookup(ActiveCell.Value, Range("datarange"), 2, False)
On Error GoTo 0
If IsError(sVal) Then
Range("F2").Value = "empty"
Else
Range("F2").Value = sVal
End If
End Sub

albishop
09-03-2006, 07:35 AM
Thanks - this is quite cool and almost what I need.

What I would like is when the active cell is in C8 and the button is pressed, the value in F8 would show G241512 (the cell to the right).

Cell C8 contains the lookup value formula which references the cell to either the right or left of it, so its the cell thats referenced by the active cell that is the key. Perhaps this somehow can be extracted and used in a lookup function similar to that above?

mdmackillop
09-03-2006, 08:40 AM
Your "table" is the wrong way round for LookUp to function. This uses Find instead


Private Sub CommandButton1_Click()
SetValue
End Sub

Sub SetValue()
Range("F2") = Columns(11).Find(What:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole).Offset(, -1)
End Sub

albishop
09-03-2006, 09:37 AM
Very nice! Thanks!

Secondary problem here, which I should have explained earlier (apologies). It is possible to have the duplicate data in column 11 (Lookup data in the table) thereby causing the find function to return only the first value that it finds in column 10.

Is there a way to extract from lookup function in the active cell and use this in the find function? ie I am in cell C8 which contains the formula;
=IF(ISERROR(VLOOKUP(D8,datarange,2,0)),"empty",VLOOKUP(D8,datarange,2,0))

From here I would like to extract the reference to cell D8 and use this in a separate lookup function controlled by the button.

By the way, which part of Scotland are you from as I am from West Lothian originally :)

Bob Phillips
09-03-2006, 09:47 AM
Perhaps combine them



Private Sub CommandButton1_Click()
Dim sVal
Range("F2") = Columns(11).Find(What:=ActiveCell.Value, _
LookIn:=xlValues, _
LookAt:=xlWhole).Offset(, -1)

On Error Resume Next
sVal = Application.VLookup(Range("F2").Value, Range("datarange"), 2, False)
On Error GoTo 0
If IsError(sVal) Then
Range("F3").Value = "empty"
Else
Range("F3").Value = sVal
End If
End Sub


Malcolm is from Scone near Perth. He showed us his car recently :)

albishop
09-03-2006, 10:03 AM
Same issue again unfortunately.

I'm trying to mess around with the dependants property to obtain the cell that the active cell is using for the basis of the lookup.

Bob Phillips
09-03-2006, 10:53 AM
What issue?

And what have you tried with the dependants property?

albishop
09-03-2006, 11:08 AM
The problem is that the find function stops when it sees the first instance of the value in the cell in the range that it is looking through.

I have attached the file that I have somewhat expanded upon to show the issue. This file contains the last code kindly provided.

Dependents property was a dead end (I think - I'm still a novice). Is there a way to extract the formula from the active cell into a variable?

Regards,
Al

Bob Phillips
09-03-2006, 02:31 PM
Is this what you want?



Private Sub CommandButton1_Click()
Dim sVal

On Error Resume Next
sVal = ActiveCell.Precedents
On Error GoTo 0

If TypeName(sVal) = "Variant()" Then
Range("F2").Value = "empty"
ElseIf sVal = "" Then
Range("F2").Value = "empty"
Else
Range("F2").Value = sVal
End If
End Sub

albishop
09-04-2006, 09:27 AM
Thanks - this is exactly what I was trying to do. I better go read up on the precendents property.

Once again, thanks for the assistance, input and your time.