VLOOKUPS

[Marianne]

Hi

Im trying to copy a VLOOKUP formula for the table being searched in ... down
all the cells in a column and want to auto enter the "$" prefixes to preserve
absolute cell refereneces.

Example

How do I automatically enter

=VLOOKUP(C2,Sheet1!C$2$5,2,FALSE)

instead of what normally comes up - which is

=VLOOKUP(C2,Sheet1!C25,2,FALSE)

Has anyone any idea idea how to do this?

 

[Max]

With this placed inside the formula bar:
=VLOOKUP(C2,Sheet1!C25,2,FALSE)
select only this range part: C25
then hit F4 to toggle through the 4 different states (the $/no $ signs)
Stop the F4 toggle when the desired state appears: C$2$5

voila? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Lars-Åke Aspelin]

Hi

Im trying to copy a VLOOKUP formula for the table being searched in ... down
all the cells in a column and want to auto enter the "$" prefixes to preserve
absolute cell refereneces.

Example

How do I automatically enter

=VLOOKUP(C2,Sheet1!C$2$5,2,FALSE)

instead of what normally comes up - which is

=VLOOKUP(C2,Sheet1!C25,2,FALSE)

Has anyone any idea idea how to do this?

What do you mean by "comes up"?
Do you write the formula manually?
If you select the C25 part of the formula and then hit the F4
button, the references change to absolute. You may hit F4 several
times and see how it loops trough the following four combinations
$C$2:$C$5
C$2:C$5
$C2:$C5
C2:C5
If the C2:C5 comes up by you pointing at that range in the spreadsheet
while editing the formula, just hit the F4 before proceeding with the
editing.

Hope this helps / Lars-Åke

 

[Niek Otten]

Use a named range instead of cell references

 

[DILipandey]

Hi Marianne,

In the formula, select Sheet1 and press F4 twice.
--
Click on Yes, if it is useful.

Thanks & Best Regards,
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India