OK, multiple sheets with individual software package use being tracked. One
sheet to roll it all up into.
In the individual sheets you have an ID in column A. We will presume that
all users have an ID assigned, and that each ID is unique.
On the 'rollup' sheet you want ID, secondary ID if available, and name and
then out across the sheet you want to mark which software each user has
access to.
This would work best if you had another sheet which we will call UserSheet
that listed all users, with ID in column A, secondary ID (where there is one)
in B, and name in C. This sheet provides you with a 'controlled' list that
you should use to get entries from for all other sheets; software and
roll-up. You can also use it to make sure that all user IDs in column A are
unique and that you haven't entered someone onto two different software
sheets with 2 different IDs.
We will presume you've created such a UserSheet and that you have 50 users
and so your list of IDs/2ndary IDs and names goes from A2 over and down to
C51. You may want to look into Named Ranges to set things up on that sheet
to make adding to the list/deleting from it/editing in it easier in the
future - and so that you don't have to do any formula changes on the rollup
sheet when adds/deletes occur. But for now we'll just work assuming no named
ranges.
To get your secondary IDs and names into the rollup sheet you will put a
person's ID number into column A. On that same row in column B put this
formula (adjust for the real 'UserSheet')
=VLOOKUP(A2,'UserSheet'!A$2:C$51,2,0)
That will bring over the secondary ID for that user from column B on the
UserSheet.
in column C of the same row use
=VLOOKUP(A2,'UserSheet'!A$2:C$51,3,0)
which will bring over the name.
You can fill those formulas on down the worksheet as you add user IDs into
column A.
For each column from D on over, one column for each software package/sheet,
you need to use formulas similar to this, but you'll need to change the sheet
name in each column to go along with the sheet related to that software
package:
=IF(ISNA(VLOOKUP($A2,'SWareSheet1'!A$2:A$100,1,0)),"","X")
this says to try to get a match to the UserID in column A of the rollup
sheet in column A (assumes entries from row 2 to 100 on the specific software
sheet - change as needed), if there is no match (an #N/A error happens),
then display an empty cell, but if a match was found, then show an X in the
cell.
You could use the MATCH function instead of the VLOOKUP in that formula like
this (might be a touch faster)
=IF(ISNA(MATCH($A2,'SWareSheet1'!A$2:A$100,0)),"","X")
