Excel Help

I'm trying to figure out the best way to do something...I've checked with other people in my unit and no one has a solution.

I'm trying to:
create a roster of people...i.e. (in a "master" tab) name in A column, rank in B...etc with some detailed information

Then in a second tab, label it personal info (I'll have 5 or more separate tabs with basic name./rank then specific information), I want to copy the info from column A and B from the master tab (which is easy) , but then when I sort from master tab, it sorts the info in the personal tab, and all the tabs.....

The problem I'm running into....if I'm on the "personal info" tab, and I add information in say cell C5...then go back to the master tab and sort by rank instead of name, the information in "personal info" C5 stays in the same spot(even though the actual person changed, the row information stayed in the same spot)....so even though the names/ranks in the A/B column changed since they referenced the master tab, the cells beyond that stay the same....so nothing matches up.

This is kind of confusing....but if you're an excel guru I'll be glad to work with you!

I've also thought about having one massive master tab then just hiding the columns that aren't used/needed in the specific tab...i.e the master tab would have personal info as well as physical fitness...but if I clicked on the physical fitness tab, I wouldn't see the personal info (because I'd just hide it)....the problem is if I add a column/row I'd have to redo every sheet (if I just referred back to master from everything)

Obviously aggressive management of the data could keep this in check but the goal is lock many of these tabs and allow easy manipulation of data which will propagate throughout the sheet.

What about VLOOKUP

Something like:

VLOOKUP($A1,Master!A:A,X,TRUE)
WHERE:
- $A1 is the ID field (Like the persons name etc..)
- Master!A:A is the ID Field in the master table
- X is the column you want the data from (A = 1, B = 2, C = 3 etc..)
- I always use TRUE for approx matches - it's basically case insensitive.


To get all the ID fields, you should be able to reference them with something like.

Personal!$A1 = Master!$A1

Then copy that all the way down the column.




Lemme know if that makes sense.




Looked over your problem again. If you add data on the Personal sheet it won't be able to go back to the master (easily) as it will create a cyclical reference. Best bet is to use the lookups and enter the data on the master sheet only.

vlookup may work for sorting some information, but I run into the issue of adding columns....If I just reference the master and do everything from there I run into the problem of adding/deleting columns. vlookup and the follow on sorting will still be affected.....

The matching of everything minus column spacing/values from the master is probably the best bet, but if I add or delete a column it causes "#REF" errors. This may be something that I need to work on through access and excel together....I have no experience with access though so I'm seeing if this will work!

Thanks for the help...even little ideas help spur others!

You mentioned one massive master sheet. From the looks of things that's the best idea and simply only add columns to the end and don't delete. If you want to use Excel I fear this is the best option you'll have.

What you're trying to achieve is what a database is designed for (Especially since this Excel version could blow up to huge file sizes)

Best bet would be to learn the basics in access and setup views and reports.
If you had an example of the sheets you wanted I might be able to mix up the data tables for you (Just the headers would suffice) and you'd need to import the data somehow.

Tricky one indeed.