Excel sort column with indirectly referenced dymanic worksheet

M

missy.cissell

I have searched for help everywhere, please please help me!!!

In my excel file, a column is populated by an indirect
formula that pulls data from different worksheets based on dynamic
info (if A=0, then WorksheetA, if A=1 WorksheetB, etc).
I have then setup a macro to sort a table on this column (just by
doing record macro, data sort etc- no programming involved)
When i created the macro, the indirect pulled from WorksheetA.
However, when the Indirect formula pulls from WorksheetB the data is
still sorted it by WorksheetA after executing the macro, but the cells
are displaying what i expect-from worksheetb. Therefore the indirect
formula is correct.

Is it possible for the macro to understand the sorting based on
different worksheets?

The column that Im sorting on has this formula:
=IF(W11>0,(SUMIF(INDIRECT(""&$T$5&""&""&$AY$5&""),$H11,INDIRECT(""&$T
$5&""&""&$AY$4&""))),0)
.........If something isnt 0, sumif, dynamically pulling the sheetname
and column to get data from.

Then the code that was created that relates to that sort in the macro
is this:
Range("A10:BB109").Select
Selection.Sort Key1:=Range("AK10"), Order1:=xlDescending,
Key2:=Range( _
"H10"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=True, Orientation:=xlTopToBottom


-desperately seeking help.
-Missy
 
J

JLGWhiz

It makes no difference where the data comes from, if it ends up in the
location you are sorting, it will get sorted. In you case, Range A10:BB109,
will be sorted based on the data in columns AK (Descending) and H (Ascending)
respecively. The macro is doing its job.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top