Vlookup maybe???

[Tom K]

I have about 200 files that have a spreadsheet of active jobs (each job is saved as it's own name). The spreadsheets list the project manager and the total # of hours he expects to work on that job. I need to gather that information into one master sheet. On the source spreadsheets the project managers are listed in column B and their respective hours are in column D. On the master spreadsheet I have all the managers listed in row 1 from left to right (approx 30). In column A I have all the jobs listed going down. I need to put a formula in each cell so it will show the corresponding hours for that project manager and job.

I tried using Vlookup but I get a N/A.

Any help or suggestions would be greatly appreciated.


I'm using Excel 2003 on Windows XP.

If you need clarification please email me [email protected] and I can send you a copy of the files to look at.

Thanks.

[via135]

hi!

200 files of what *type*?!

-via135

I have about 200 files that have a spreadsheet of active jobs (each job is saved as it's own name). The spreadsheets list the project manager and the total # of hours he expects to work on that job. I need to gather that information into one master sheet. On the source spreadsheets the project managers are listed in column B and their respective hours are in column D. On the master spreadsheet I have all the managers listed in row 1 from left to right (approx 30). In column A I have all the jobs listed going down. I need to put a formula in each cell so it will show the corresponding hours for that project manager and job.

I tried using Vlookup but I get a N/A.

Any help or suggestions would be greatly appreciated.


I'm using Excel 2003 on Windows XP.

If you need clarification please email me [email protected] and I can send you a copy of the files to look at.

Thanks.

[Tom K]

They are Excel files.

[Tom K]

Help Please.

[Pete_UK]

Will you have to do this quite often?

If so, then I think you would be better with a macro which opened each
of your 200 files in turn and copied the data (and job name) into a
second sheet. Imagine this sheet had a column for manager's name, hours
and job name - you can then define these as 3 named ranges by
highlighting the data (plus headings) and Insert | Name | Create and
ensuring that only Top Row is ticked then OK.

In your main sheet, with managers' names in B1 to AE1 (30 of them) and
job names in A2 to A201, you can enter this formula in B2:

=SUM(IF(($A2=Job)*(B$1=Manager),Hours,0))

and comit with CTRL-SHIFT-ENTER instead of just SHIFT, as this is an
array formula - if you do this correctly then Excel will wrap curly
braces { } around the formula (do not type these yourself).

The formula can be copied across to AE2, and then the cells B2:AE2 can
be copied down to row 201 - this could take some time to calculate,
depending on how much data you have in Sheet2. In a cross-tabulation
like this, I like to apply Conditional Formatting to turn any zero
values to blank (by choosing white foreground colour).

Of course, the latter stages that I have described here could also be
included in the macro.

Hope this helps.

Pete