Using AutoFilter to Sort Data Pulled in by INDEX / MATCH

I have a workbook in which one sheet contains various columns of data that
are being pulled in from other sheets using the INDEX / MATCH function. One
column includes a list of identifiers for particular companies and the other
columns pull in data for the identifier in that particular row. The data
includes the names of various companies and different financial statistics
for each company. When I use AutoFilter to sort a particular column, it
correctly sorts the first row (i.e., the appropriate company appears at the
top of the worksheet) but the corresponding data is not correct because the
INDEX / MATCH function for a particular column is linked the identifier in
the row where the correctly sorted company used to be. If I'm not explaining
this well enough, please let me know.

I guess my question is: does using AutoFilter create problems when you are
sorting data that is being pulled in from other workeets, or should it not
make a difference? Several things are quite strange about the situation. 1)
It worked last week when I tried to sort 2) the columns where I just use a
MATCH formula work fine. Not quite sure what is happening, so I would greatly
appreciate any feedback! Also, please let me know if I'm not being specific
enough. Thank you!

> ... It worked last week

Hazarding a guess based purely on the above line

Maybe calc mode is inadvertently set to Manual ?

Click Tools > Options > Calculation tab
Ensure "Automatic" is checked > OK
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I just checked and it is set to automatic. Thanks for the suggestion, though!

"Max" wrote:

> > ... It worked last week
>
> Hazarding a guess based purely on the above line
>
> Maybe calc mode is inadvertently set to Manual ?
>
> Click Tools > Options > Calculation tab
> Ensure "Automatic" is checked > OK
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
>
>
>

Sorry to hear that wasn't it. I couldn't reproduce the posted behaviour over
here (light testing). Perhaps you might want to post your INDEX/MATCH
formula, with some relevant set-up description to allow us a closer look ?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"A Romeo Jr" <ARomeoJr@discussions.microsoft.com> wrote in message
news:3ED8B6D5-AFA2-41C7-A512-0A7204F9F688@microsoft.com...
> I just checked and it is set to automatic. Thanks for the suggestion,
though!

=INDEX('Price Targets'!F:F,MATCH(Summary!E4,'Price Targets'!C:C,0)) is the
formula I am using. I am pulling in the desired data into the summary
worksheet from the price target worksheet's column F by matching the
hard-coded identifier in E4 to the same identifier in the price targets
worksheet in column F. When I sort this column, it correctly sorts the first
row row by bringing the right identifier to the top but it does not bring up
the corresponding data. For example, if the identifier in E10 should be at
the top of the list (in other words, it should move up to row 4) it will go
there but all of the linked cells will still be linked to the identifer in
E10.

"Max" wrote:

> Sorry to hear that wasn't it. I couldn't reproduce the posted behaviour over
> here (light testing). Perhaps you might want to post your INDEX/MATCH
> formula, with some relevant set-up description to allow us a closer look ?
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "A Romeo Jr" <ARomeoJr@discussions.microsoft.com> wrote in message
> news:3ED8B6D5-AFA2-41C7-A512-0A7204F9F688@microsoft.com...
> > I just checked and it is set to automatic. Thanks for the suggestion,
> though!
>
>
>

Is this set-up what you have over there?

http://cjoint.com/?bEomeuvNn5
ARomeoJr_wks.xls

In sheet: Summary,

In say, F4: =INDEX('Price Targets'!F:F,MATCH(E4,'Price Targets'!C:C,0))
with F4 copied down

If so, when you sort the look-up values in E4:E6, the INDEX / MATCH formulas
in F4:F6 would continue to return correctly from "Price Targets" col F ?

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"A Romeo Jr" <ARomeoJr@discussions.microsoft.com> wrote in message
news:D05BBFDA-1C53-4969-B1E3-FDF0E4C37A6C@microsoft.com...
> =INDEX('Price Targets'!F:F,MATCH(Summary!E4,'Price Targets'!C:C,0)) is the
> formula I am using. I am pulling in the desired data into the summary
> worksheet from the price target worksheet's column F by matching the
> hard-coded identifier in E4 to the same identifier in the price targets
> worksheet in column F. When I sort this column, it correctly sorts the
first
> row row by bringing the right identifier to the top but it does not bring
up
> the corresponding data. For example, if the identifier in E10 should be at
> the top of the list (in other words, it should move up to row 4) it will
go
> there but all of the linked cells will still be linked to the identifer in
> E10.

I just figured out the problem. If you look at the formula I pasted below, I
reference cell E4 in the sheet titled "Summary" right after MATCH. Since the
formula is in the "Summary" sheet, the formula only needs to go "MATCH(E4," .
.. . but since I was clicking back and forth between worksheets, it added the
sheet name as well. Unfortunately this was the cause of the problem when I
was using AutoFilter. After adjusting to your formula below, my worksheet now
works! Thank you, Max, for your help! Greatly appreciated!

"Max" wrote:

> Is this set-up what you have over there?
>
> http://cjoint.com/?bEomeuvNn5
> ARomeoJr_wks.xls
>
> In sheet: Summary,
>
> In say, F4: =INDEX('Price Targets'!F:F,MATCH(E4,'Price Targets'!C:C,0))
> with F4 copied down
>
> If so, when you sort the look-up values in E4:E6, the INDEX / MATCH formulas
> in F4:F6 would continue to return correctly from "Price Targets" col F ?
>
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "A Romeo Jr" <ARomeoJr@discussions.microsoft.com> wrote in message
> news:D05BBFDA-1C53-4969-B1E3-FDF0E4C37A6C@microsoft.com...
> > =INDEX('Price Targets'!F:F,MATCH(Summary!E4,'Price Targets'!C:C,0)) is the
> > formula I am using. I am pulling in the desired data into the summary
> > worksheet from the price target worksheet's column F by matching the
> > hard-coded identifier in E4 to the same identifier in the price targets
> > worksheet in column F. When I sort this column, it correctly sorts the
> first
> > row row by bringing the right identifier to the top but it does not bring
> up
> > the corresponding data. For example, if the identifier in E10 should be at
> > the top of the list (in other words, it should move up to row 4) it will
> go
> > there but all of the linked cells will still be linked to the identifer in
> > E10.
>
>
>

You're welcome !
Glad to hear it sorted itself out <g>
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"A Romeo Jr" <ARomeoJr@discussions.microsoft.com> wrote in message
news:4CBA3AB7-1B0D-4CA7-B988-2889AFBFDD55@microsoft.com...
> I just figured out the problem. If you look at the formula I pasted below,
I
> reference cell E4 in the sheet titled "Summary" right after MATCH. Since
the
> formula is in the "Summary" sheet, the formula only needs to go
"MATCH(E4," .
> . . but since I was clicking back and forth between worksheets, it added
the
> sheet name as well. Unfortunately this was the cause of the problem when I
> was using AutoFilter. After adjusting to your formula below, my worksheet
now
> works! Thank you, Max, for your help! Greatly appreciated!