Index formula problem

[Guest]

I have used the following excel formula for years:

=INDEX(Sheet3!A:F,MATCH(A1,Sheet3!A:A,0),3)

I just keep changing the columns and cells that it cross-references for me.
For some reason when I use it today, it returns me the proper value in the
first cell, but when I copy and paste it to the next cell down in the row -
it returns me the value from the first row.

The really strange thing is when I change the return value digit (3 in the
formula above) to the next cell, say 2 or 4, it returns me the proper value.

Unfortunately, this kills the value of the copy and paste efficiency, as
each cell has to be edited now to return the correct value...

Has anyone seen anything similar? Has something changed recently in Excel
2003?

 

[Guest]

Sounds like Calculations have been set to manual. Try this.

From the TOOLS Menu, choose "Options"
Select the Calculation Tab
Check "Automatic"
Click OK

HTH,
Elkar

 

[Bob Phillips]

=INDEX(Sheet3!$A:$F,MATCH(A1,Sheet3!$A:$A,0),3)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

You are a savior!
Thanks a ton.
--
thanks

Sounds like Calculations have been set to manual. Try this.

From the TOOLS Menu, choose "Options"
Select the Calculation Tab
Check "Automatic"
Click OK

HTH,
Elkar

 

[Guest]

Hi Bob,
That one works too - you guys are masters!
Thanks a ton.