Vlookup based on Active cell

[Thomp]

Is there a way to have a vlookup formual based on the data in the
active cell. I have data on one sheet that I what to pull in to a cell
on another worksheet based on the data in the active cell I click on.
I looking for the reference of the vlookup to be the active cell.

thanks in advance for any help on this

 

[Harlan Grove]

Is there a way to have a vlookup formual based on the data in the
active cell. I have data on one sheet that I what to pull in to a cell
on another worksheet based on the data in the active cell I click on.
I looking for the reference of the vlookup to be the active cell.

Yes and no. VLOOKUP can use the value in the active cell as its 1st
(3rd or 4th) argument as follows

=VLOOKUP(CELL("Contents"), . , . [, . ])

but Excel won't recalc as you move the cell pointer, so you'd either
need to press [F9] to recalc every time you move the cell pointer.
Note: SelectionChange won't quite work: if you have a multiple cell
range selected, then you can change the active cell within the
selected range using [[Shift]+][Enter] or [[Shift]+][Tab], but that
won't trigger the SelectionChange event.

Also, the formula above will throw a circular recalc warning when you
enter it and when you recalc when the cell containing it is active.

That said, I'm not sure this would be useful for anything interactive,
and there are likely much better ways to do what you want using
macros, but you'd need to provide more details about exactly what
you're trying to do.

 

[Thomp]

Is there a way to have a vlookup formual based on the data in the
active cell. I have data on one sheet that I what to pull in to a cell
on another worksheet based on the data in the active cell I click on.
I looking for the reference of the vlookup to be the active cell.

Yes and no. VLOOKUP can use the value in the active cell as its 1st
(3rd or 4th) argument as follows

=VLOOKUP(CELL("Contents"), . , . [, . ])

but Excel won't recalc as you move the cell pointer, so you'd either
need to press [F9] to recalc every time you move the cell pointer.
Note: SelectionChange won't quite work: if you have a multiple cell
range selected, then you can change the active cell within the
selected range using [[Shift]+][Enter] or [[Shift]+][Tab], but that
won't trigger the SelectionChange event.

Also, the formula above will throw a circular recalc warning when you
enter it and when you recalc when the cell containing it is active.

That said, I'm not sure this would be useful for anything interactive,
and there are likely much better ways to do what you want using
macros, but you'd need to provide more details about exactly what
you're trying to do.

I seem to have the formula wrong somehow. See below what I wrote. I
have a macro from John walkenbach's book that I think I can use to
make it recalculate if I can just get the formula right.

=VLOOKUP(CELL,('SAN Account List'!A2:S75,2,FALSE)

What I am ultimately trying to do is that I have two sheets one with
all of the data and another one which is kind of like my dashboard. I
want to be able to populate the cells on the dashboard based on a
vlookup that retrieves data from the data sheet in which the data in
the active cell matches the data on the data sheet

thanks,
Thomp

 

[Harlan Grove]

=VLOOKUP(CELL("Contents"), . , . [, . ])

....
I seem to have the formula wrong somehow. See below what I wrote. I
have a macro from John walkenbach's book that I think I can use to
make it recalculate if I can just get the formula right.

=VLOOKUP(CELL,('SAN Account List'!A2:S75,2,FALSE)

Yup, that's wrong. CELL is a function, and CELL("Contents") is a
function call that returns the value of the cell that was active at
last recalc.

What I am ultimately trying to do is that I have two sheets one with
all of the data and another one which is kind of like my dashboard. I
want to be able to populate the cells on the dashboard based on a
vlookup that retrieves data from the data sheet in which the data in
the active cell matches the data on the data sheet

I suspect what you want to do is use the first column of your table of
data as a LIST data validation source for one cell in your dashboard
worksheet. That is, in the dashboard worksheet, name a cell ID, format
it as unlocked, apply data validation to it (a list source with the
list being a reference to the leftmost column of your data table),
then make the formulas in your dashboard workbook similar to

=VLOOKUP(ID,ReferenceToYourDataHere,column_index,0)

Data validation would add a drop-down list to the cell named ID, so
users could click on the down arrow button on the right side of the
cell and scroll through the drop-down list to select the records from
the data table. Changing the ID cell *DOES* trigger recalculation, so
Excel would update all VLOOKUP calls referring to ID when ID changes.

 

[Dave Peterson]

=VLOOKUP(CELL("contents"),'SAN Account List'!A2:S75,2,FALSE)

I guess I don't understand why a simple =vlookup() in a separate cell couldn't
be used.

Is there a way to have a vlookup formual based on the data in the
active cell. I have data on one sheet that I what to pull in to a cell
on another worksheet based on the data in the active cell I click on.
I looking for the reference of the vlookup to be the active cell.

Yes and no. VLOOKUP can use the value in the active cell as its 1st
(3rd or 4th) argument as follows

=VLOOKUP(CELL("Contents"), . , . [, . ])

but Excel won't recalc as you move the cell pointer, so you'd either
need to press [F9] to recalc every time you move the cell pointer.
Note: SelectionChange won't quite work: if you have a multiple cell
range selected, then you can change the active cell within the
selected range using [[Shift]+][Enter] or [[Shift]+][Tab], but that
won't trigger the SelectionChange event.

Also, the formula above will throw a circular recalc warning when you
enter it and when you recalc when the cell containing it is active.

That said, I'm not sure this would be useful for anything interactive,
and there are likely much better ways to do what you want using
macros, but you'd need to provide more details about exactly what
you're trying to do.

I seem to have the formula wrong somehow. See below what I wrote. I
have a macro from John walkenbach's book that I think I can use to
make it recalculate if I can just get the formula right.

=VLOOKUP(CELL,('SAN Account List'!A2:S75,2,FALSE)

What I am ultimately trying to do is that I have two sheets one with
all of the data and another one which is kind of like my dashboard. I
want to be able to populate the cells on the dashboard based on a
vlookup that retrieves data from the data sheet in which the data in
the active cell matches the data on the data sheet

thanks,
Thomp

 

[Thomp]

=VLOOKUP(CELL("Contents"), . , . [, . ])

...
I seem to have the formula wrong somehow. See below what I wrote. I
have a macro from John walkenbach's book that I think I can use to
make it recalculate if I can just get the formula right.

=VLOOKUP(CELL,('SAN Account List'!A2:S75,2,FALSE)

Yup, that's wrong. CELL is a function, and CELL("Contents") is a
function call that returns the value of the cell that was active at
last recalc.

What I am ultimately trying to do is that I have two sheets one with
all of the data and another one which is kind of like my dashboard. I
want to be able to populate the cells on the dashboard based on a
vlookup that retrieves data from the data sheet in which the data in
the active cell matches the data on the data sheet

I suspect what you want to do is use the first column of your table of
data as a LIST data validation source for one cell in your dashboard
worksheet. That is, in the dashboard worksheet, name a cell ID, format
it as unlocked, apply data validation to it (a list source with the
list being a reference to the leftmost column of your data table),
then make the formulas in your dashboard workbook similar to

=VLOOKUP(ID,ReferenceToYourDataHere,column_index,0)

Data validation would add a drop-down list to the cell named ID, so
users could click on the down arrow button on the right side of the
cell and scroll through the drop-down list to select the records from
the data table. Changing the ID cell *DOES* trigger recalculation, so
Excel would update all VLOOKUP calls referring to ID when ID changes.

Thank Harlan..Both of these work great...Kudos!!!!

 

[Thomp]

=VLOOKUP(CELL("Contents"), . , . [, . ]) ...
I seem to have the formula wrong somehow. See below what I wrote. I
have a macro from John walkenbach's book that I think I can use to
make it recalculate if I can just get the formula right.
=VLOOKUP(CELL,('SAN Account List'!A2:S75,2,FALSE)

Yup, that's wrong. CELL is a function, and CELL("Contents") is a
function call that returns the value of the cell that was active at
last recalc.

I suspect what you want to do is use the first column of your table of
data as a LIST data validation source for one cell in your dashboard
worksheet. That is, in the dashboard worksheet, name a cell ID, format
it as unlocked, apply data validation to it (a list source with the
list being a reference to the leftmost column of your data table),
then make the formulas in your dashboard workbook similar to

Data validation would add a drop-down list to the cell named ID, so
users could click on the down arrow button on the right side of the
cell and scroll through the drop-down list to select the records from
the data table. Changing the ID cell *DOES* trigger recalculation, so
Excel would update all VLOOKUP calls referring to ID when ID changes.

Thank Harlan..Both of these work great...Kudos!!!!- Hide quoted text -

- Show quoted text -

Just wanted to add this last piece to my question. I used this macro
from John Wakenbach's book and it will make the active cell recalute
without having to hit F9..
Here is the macro..
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Row > 1 And ActiveCell.Row < 75 Then _
ActiveSheet.Calculate

End Sub

 

[Harlan Grove]

from John Wakenbach's book and it will make the active cell recalute
without having to hit F9..
Here is the macro..
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveCell.Row > 1 And ActiveCell.Row < 75 Then _
        ActiveSheet.Calculate
End Sub

Try selecting C3:H20 and move around within the selection using
[Enter], [Shift]+[Enter], [Tab], [Shift]+[Tab] and watch Excel *not*
recalc.