I am desperately in need of help and am in over my head...
I have a very large summary spreadsheet broken down by region/city/vp etc.
each vp is assigned a region with multiple cities, job codes etc...so i need to be able to pull the values for individual vp's city etc.
i have a source file from which i need to populate the summary spreadsheet.
I need a formula that will go into the source file and locate a specified
city of which there can be multiple occurrences...then look to the right of
the city and look for a specified job code number of which there can be multiple occurrences as well then look to the right and up 1 row and find a specified folder name and return the value below that folder name. the sum if or sumproduct would come in when i have a need to add the sums of multiple folders (i.e., although i only have the heading for interview i will need the value of the interview folder added to the selected folder - we did this in order to keep the headings to a minimum-i think this can be accomplished by using a list.)
Example of data sheet to populate: (in this example i am looking for the city of atlanta and job code 1234. the inbox consists of the sum of the values in both the inbox and prescreen in the source file.)
inbox intv selected in process hired
city: atlanta 96 19 15 14 14
job code: 1234
example of source data:
city job code inbox prescreen intv sel in process hired
aberdeen 5487 22 8 5 1 1 1
albany 6932 17 11 8 3 3 2
atlanta 1234 31 16 9 6 6 6
atlanta 9083 14 10 6 4 3 2
atlanta 1234 29 20 10 9 8 8
Let me know if you need to see an actual example & i can forward you a
snippet of the source data and the worksheet i need populated.
Thanks so much - i am still learning and what i am learning is that i have a lot to learn...so dummy it down for me please
<!-- / message -->
I have a very large summary spreadsheet broken down by region/city/vp etc.
each vp is assigned a region with multiple cities, job codes etc...so i need to be able to pull the values for individual vp's city etc.
i have a source file from which i need to populate the summary spreadsheet.
I need a formula that will go into the source file and locate a specified
city of which there can be multiple occurrences...then look to the right of
the city and look for a specified job code number of which there can be multiple occurrences as well then look to the right and up 1 row and find a specified folder name and return the value below that folder name. the sum if or sumproduct would come in when i have a need to add the sums of multiple folders (i.e., although i only have the heading for interview i will need the value of the interview folder added to the selected folder - we did this in order to keep the headings to a minimum-i think this can be accomplished by using a list.)
Example of data sheet to populate: (in this example i am looking for the city of atlanta and job code 1234. the inbox consists of the sum of the values in both the inbox and prescreen in the source file.)
inbox intv selected in process hired
city: atlanta 96 19 15 14 14
job code: 1234
example of source data:
city job code inbox prescreen intv sel in process hired
aberdeen 5487 22 8 5 1 1 1
albany 6932 17 11 8 3 3 2
atlanta 1234 31 16 9 6 6 6
atlanta 9083 14 10 6 4 3 2
atlanta 1234 29 20 10 9 8 8
Let me know if you need to see an actual example & i can forward you a
snippet of the source data and the worksheet i need populated.
Thanks so much - i am still learning and what i am learning is that i have a lot to learn...so dummy it down for me please
<!-- / message -->