CHALLENGE! Issue sorting data containing relative references on a separate tab

[lax828]

Hello -



I have a challenge for some MVPs or Excel gurus out there (who knows -- maybe this is easy...).

I have an example sheet (which you can download below). The problem involves sorting table data on tab 2.

http://dl.dropbox.com/u/2625864/Foru...ampleData.xlsx

On tab 1 (Data), there are two columns (Name and Completed?). There is also a table where sorting works fine. When I sort any of the columns, the data moves with the appropriate rows as expected.

On tab 2 (Data2), the same table from tab 1 is there, in addition to another sammple table.

When I sort items in either of the two tables, the rows within each of the tables DO NOT move appropriately. For example, if I sort the names, the names sort fine but the data in the other columns does not move. If I sort the columns with the numbers, the numbers don't even sort correctly (an ascending sort sorts the data in a very random order).

I'm decent at Excel (or so I thought) but this has got me baffled.

Any help would be great!!!

[mcjohn]

I think this is correct now. It seems a bit pointless having the two sheets though? And I assume the bottom table on Data2 where it just shows Name and # Assigned it just for display purposes and isn't meant to be in in any calculations, correct?

What I changed was that the formulas in Data2 were not being associated to the adjacent person's name so of course when sorting occured things would not match. Also, and this has no effect but just an FYI, your Data2 had formulas for Data!A2 instead of just A2, which usually highlights itself in blue in the formula itself you will notice on the original you uploaded.

[lax828]

Thanks for your help! First off, yes, all the data was for demonstrational purposes -- the real problem is on a work file. I wanted to display the metric data (whatever that happens to be) on a separate tab, separate from the raw data.

It's still really weird. Basically, if you take the "Data2!" out of the formulas on the Data2 tab, it works. When I initially created the formulas, the "Data2!" was automatically populated by Excel.

Anyways, it's working and I'll just have to keep a note of this Excel quirk.

[mcjohn]

I'm sorry I made a typo which might be confusing. My FYI should have said there were unnecessary references to Data2!A2 (for example) in the Data2 sheet. The sheet name doesn't need to be referenced when refering to a cell within the sheet the formula is in... Anyway you know all that.