Sorting worksheet tabs that are referenced by cells

[huntersp]

Hello All,

I have a workbook with serveral worksheet tabs starting with 1001
through 1100. I made a summary worksheet and used the formula (for
example) =+'100'!B1 to return the cell name and information I need in
my summary sheet--which worked great. However, when I sort my
worksheet tabs in numeric order my summary sheets stays the same which
means I have a list that might look like this 1001 1005 1002 1003. My
question is there any way to sort the =+'1001'!B1 formulas so they will
match the numeric order of my worksheet tabs? I hope this made sense.

Thanks

 

[Jim Cone]

My free Excel add-in "Excel Extras" will add a Table of Contents
to a workbook with each sheet name hyperlinked to the sheet.
Can also sort workbook sheets into true numerical order.
Does other nice stuff...
http://www.realezsites.com/bus/primitivesoftware


in message...
Hello All,
I have a workbook with serveral worksheet tabs starting with 1001
through 1100. I made a summary worksheet and used the formula (for
example) =+'100'!B1 to return the cell name and information I need in
my summary sheet--which worked great. However, when I sort my
worksheet tabs in numeric order my summary sheets stays the same which
means I have a list that might look like this 1001 1005 1002 1003. My
question is there any way to sort the =+'1001'!B1 formulas so they will
match the numeric order of my worksheet tabs? I hope this made sense.
Thanks
huntersp

 

[Dave Peterson]

If you sort your sheets, it breaks these kinds of formulas.

Can you pick out a nice unique key in each row that you can match up on?

If you can, then maybe you can use =vlookup() or =index(match()) to return the
values associated with that key. (Yes, the keys themselves should be
values--not formulas.)

Take a look at Debra Dalgleish's site:
http://www.contextures.com/xlFunctions02.html
and
http://www.contextures.com/xlFunctions03.html