| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a function to match up jobs
I have a master sheet with about 50 different job descriptions, then I have
about 20 more worksheets that could have between 20 and maybe 40 job descriptions with the number of people against them. I want to put them all on the master sheet against the appropriate job description but keeping them in columns against their appropriate worksheet without having to go through and enter everything manually. Master Sheet Worksheet1 Worksheet2 Accountant Accountant 40 Bookkeeper Bookkeeper 15 Bookkeeper 20 Finance Manager Finance Manager 12 General Clerk General Clerk 20 Etc. I hope this all makes sense. Thanks in advance Lynda |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need a function to match up jobs
If I were you I wouldn't repeat job names in master sheet, so I suggest this
layout: Master Worksheet1 Worksheet1 Accountant formula- Bookkeeper ˇ Finance General and the formula is this (job names and numbers being in columns A:B in worksheets1,2,...): =IF(ISERROR(VLOOKUP($A2,INDIRECT(C$1&"!A:B"),2,FAL SE)),"",VLOOKUP($A2,INDIRECT(C$1&"!A:B"),2,FALSE)) Fill it to the right and down as required! Regards, Stefi Lynda ezt *rta: I have a master sheet with about 50 different job descriptions, then I have about 20 more worksheets that could have between 20 and maybe 40 job descriptions with the number of people against them. I want to put them all on the master sheet against the appropriate job description but keeping them in columns against their appropriate worksheet without having to go through and enter everything manually. Master Sheet Worksheet1 Worksheet2 Accountant Accountant 40 Bookkeeper Bookkeeper 15 Bookkeeper 20 Finance Manager Finance Manager 12 General Clerk General Clerk 20 Etc. I hope this all makes sense. Thanks in advance Lynda |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need a function to match up jobs
Thank you Stefi, I will try this when i get back to work.
Cheers Lynda "Stefi" wrote: If I were you I wouldn't repeat job names in master sheet, so I suggest this layout: Master Worksheet1 Worksheet1 Accountant formula- Bookkeeper ˇ Finance General and the formula is this (job names and numbers being in columns A:B in worksheets1,2,...): =IF(ISERROR(VLOOKUP($A2,INDIRECT(C$1&"!A:B"),2,FAL SE)),"",VLOOKUP($A2,INDIRECT(C$1&"!A:B"),2,FALSE)) Fill it to the right and down as required! Regards, Stefi Lynda ezt *rta: I have a master sheet with about 50 different job descriptions, then I have about 20 more worksheets that could have between 20 and maybe 40 job descriptions with the number of people against them. I want to put them all on the master sheet against the appropriate job description but keeping them in columns against their appropriate worksheet without having to go through and enter everything manually. Master Sheet Worksheet1 Worksheet2 Accountant Accountant 40 Bookkeeper Bookkeeper 15 Bookkeeper 20 Finance Manager Finance Manager 12 General Clerk General Clerk 20 Etc. I hope this all makes sense. Thanks in advance Lynda |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel print jobs | Excel Discussion (Misc queries) | |||
| jobs | Excel Worksheet Functions | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| Need help on my Jobs applied too SS | Excel Discussion (Misc queries) | |||
| print jobs | Excel Discussion (Misc queries) | |||
Linear Mode
