Posts by swillisam

    Re: Index / Match + Sort / Filter Issue

    I believe I've figured out the cause for the sorting issue.

    When the Match lookupvalue references a sheet name, the lookup sticks to the cell as if it were an absolute. ie a formula showing "MATCH('Summary'!$B13" has issues while "MATCH($B13" does not have sorting issues.

    Does this make sense? Is this logic correct?

    Re: Index / Match + Sort / Filter Issue

    Quote from NBVC;779982

    Did you notice that the range in the Match function is not absoluted for the rows... ie.. they are missing the $ before the row indicators...

    Workings!$R10:$R50 s/b Workings!$R$10:$R$50

    That may mess things up...

    Sorry, in the actual formula it is correct. I typed the formula out and forgot to add the absolute. Will edit the original post.

    Re: Index / Match + Sort / Filter Issue

    Quote from AliGW;779973

    Copy and paste the values to a new sheet and do your sorting and filtering there: formulae and sorting don't mix.

    Unfortunately I can't do that. I need the table to be able to be sorted with the formulae intact. Is this possible?


    I have built a spreadsheet with a large data table using Index / Match formulas eg =INDEX(Workings!$GC$10:$GC$50,MATCH($C46,Workings!$R$10:$R$50,0),FALSE)

    When I try to sort the table the formulas do not follow and the table is wrong. Any ideas how I can fix this so that I can sort the data?