Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using AutoFilter to Sort Data Pulled in by INDEX / MATCH
... 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 --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using AutoFilter to Sort Data Pulled in by INDEX / MATCH
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 --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using AutoFilter to Sort Data Pulled in by INDEX / MATCH
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" wrote in message ... I just checked and it is set to automatic. Thanks for the suggestion, though! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using AutoFilter to Sort Data Pulled in by INDEX / MATCH
=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" wrote in message ... I just checked and it is set to automatic. Thanks for the suggestion, though! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using AutoFilter to Sort Data Pulled in by INDEX / MATCH
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" wrote in message ... =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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using AutoFilter to Sort Data Pulled in by INDEX / MATCH
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" wrote in message ... =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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using AutoFilter to Sort Data Pulled in by INDEX / MATCH
You're welcome !
Glad to hear it sorted itself out <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "A Romeo Jr" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to sort data in worksheet by specific date | Excel Discussion (Misc queries) | |||
Match and index functions: corrlating data from 2 worksheets | Excel Worksheet Functions | |||
Index and Match issues | Excel Worksheet Functions | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Data > Sort function amnesia? | Excel Discussion (Misc queries) |