Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Listing names and id and corresponding software
I have a workbook with several worksheets. Each worksheet is divided by the
software they have access to. There's overlapping names in each worksheet as some names have access to more than one software. How can I make this into a report listing the names on the left column, the names of the software listed across the top row, and a mark below each column if that person has access to that software. There's a login ID down Column A, and in some instances there's a secondary ID down Column B, then the names in Column C. Each sheet represents a different software as mentioned previously. Thanks!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Listing names and id and corresponding software
First job is to decide which piece of information on the individual software
sheets it is that you're going to use to identify a person; ID? secondary ID? Name? or some combination of the three? The preference would be to have whatever item or combination available on all software sheets in use. If it is a single item, then it's easy to get the mark in the particular columns with a MATCH() or even VLOOKUP() formula. If it is a combination of entries on the software sheets, then a SUMPRODUCT() formula will probably be the one to use. A little more information about how you're going to determine who is who over on the summary sheet will help make a final decision as to what type of worksheet function will best meet your needs. "NeedExcelHelp07" wrote: I have a workbook with several worksheets. Each worksheet is divided by the software they have access to. There's overlapping names in each worksheet as some names have access to more than one software. How can I make this into a report listing the names on the left column, the names of the software listed across the top row, and a mark below each column if that person has access to that software. There's a login ID down Column A, and in some instances there's a secondary ID down Column B, then the names in Column C. Each sheet represents a different software as mentioned previously. Thanks!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Listing names and id and corresponding software
I would like the output to include the Id and name and then if there's a
secondary ID, to list all 3. Thanks for the help. "JLatham" wrote: First job is to decide which piece of information on the individual software sheets it is that you're going to use to identify a person; ID? secondary ID? Name? or some combination of the three? The preference would be to have whatever item or combination available on all software sheets in use. If it is a single item, then it's easy to get the mark in the particular columns with a MATCH() or even VLOOKUP() formula. If it is a combination of entries on the software sheets, then a SUMPRODUCT() formula will probably be the one to use. A little more information about how you're going to determine who is who over on the summary sheet will help make a final decision as to what type of worksheet function will best meet your needs. "NeedExcelHelp07" wrote: I have a workbook with several worksheets. Each worksheet is divided by the software they have access to. There's overlapping names in each worksheet as some names have access to more than one software. How can I make this into a report listing the names on the left column, the names of the software listed across the top row, and a mark below each column if that person has access to that software. There's a login ID down Column A, and in some instances there's a secondary ID down Column B, then the names in Column C. Each sheet represents a different software as mentioned previously. Thanks!!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Listing names and id and corresponding software
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") "NeedExcelHelp07" wrote: I would like the output to include the Id and name and then if there's a secondary ID, to list all 3. Thanks for the help. "JLatham" wrote: First job is to decide which piece of information on the individual software sheets it is that you're going to use to identify a person; ID? secondary ID? Name? or some combination of the three? The preference would be to have whatever item or combination available on all software sheets in use. If it is a single item, then it's easy to get the mark in the particular columns with a MATCH() or even VLOOKUP() formula. If it is a combination of entries on the software sheets, then a SUMPRODUCT() formula will probably be the one to use. A little more information about how you're going to determine who is who over on the summary sheet will help make a final decision as to what type of worksheet function will best meet your needs. "NeedExcelHelp07" wrote: I have a workbook with several worksheets. Each worksheet is divided by the software they have access to. There's overlapping names in each worksheet as some names have access to more than one software. How can I make this into a report listing the names on the left column, the names of the software listed across the top row, and a mark below each column if that person has access to that software. There's a login ID down Column A, and in some instances there's a secondary ID down Column B, then the names in Column C. Each sheet represents a different software as mentioned previously. Thanks!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make a report listing Names and corresponding programs? | Excel Worksheet Functions | |||
Listing variable names for 3 greatest values in a column? | Excel Worksheet Functions | |||
Listing the names of your worksheets | Excel Discussion (Misc queries) | |||
listing all names in a worksheet | Excel Discussion (Misc queries) | |||
Listing of Sheet names | Excel Worksheet Functions |