how to have a formula build a table! (version 2000)

Topic

New Reply

I have an excel document with multiple worksheets. I want to create a summary worksheet that reflects the current status of all the other worksheets. The idea is to be able to hit a X for example and have the formula read through all the other worksheets and build a table of detail based on certain critieria. To keep it simple, the other worksheets represent individual projects being managed by a PMO. These sheets contain employee info of which End Date for the project is found. I want the formula or function to search all columns representing end date for each project worksheet and when a date is found, return the employee name, end date, resource manager and RM date notified. All the data exist on the same rows as the end date. This returning data is generated into rows on the summary sheet. I certainly hope this is clear, if not let me know.

Thanks

Reply | Quote

Replies

Might be possible via a Pivot table, though I think it would work better with 1 sheet rather than multiple sheets.

Barring that, you might need a macro, though you don’t give enough details to write one.

Steve

Reply | Quote

i am not familar with pivot tables so i didn’t explore that option. The current excel documnet has 7 project worksheets with each sheet containing the same types of data as follows:

Resource Role,Resource Name,Start Date,End Date,Extend Date,Reassigned to Project,Reassigned Date,% Avail,Hourly Rate (Cdn$),Hourly Loaded Cost (US$),Recruiting Status(Employ /Cont),Location,Date Resource to be Notified of Release,Date Res Mgr Notified of Release Date,Resource Mgr,Backup Resource,Transition Reference,Training Reqr.

I want to create an 8th sheet that can produce the following:
Project Name(this is found in the name of the worksheet being searched)
Resource Name(from the worksheet details)
End Date (from the worksheet details)
Revised End Date (from the worksheet details)
Resource MAnager (from the worksheet details)
RM date Notified (from teh worksheet details)

Criteria is to search each project worksheet looking at the field. If it = “Not Yet” then return all above data to summary sheet and put “Not Yet” in this date field otherwise, use the date found in this date field. See, the local PM’s maintain their own worksheet, the summary is to produce a quick snapshot of the resources of all projects. There are two groupings those that have a “Not Yet” in the field and those that have a valid date.

Thanks

Reply | Quote

It would be easier to create 1 master sheet with all the similar data. Include a NEW column which contains the info that would be similar to a SHEET name (project name?)

WIth this setup you can use PIVOT TABLES directly as well as advance filtering, autofiltering with subtotals, Dfunctions, ARRAY formulas, and other techniques much more directly than with separate sheets.

In my opinion, I can see very few benefits to making 7 identically formatted sheets and then try to combine the summaries. With Autofilter and a column for the “sheet” you could essentially “on the fily” make the one sheet “act like the 7 and you get the benifits of easier manipulation with little or even NO coding requirements.

Pivot tables are very powerful and require no coding. Having 7 sheets will be more troublesome to handle.

Steve

Reply | Quote

I appreciate your insight. From a project perspective they want their own worksheets. Maybe a summary worksheet of all the project worksheets is needed then use the built in optiosn you describe. I will also look into pivot tables using the summary sheet.

Thanks Steve.

Reply | Quote

As I said, with Autofilter, they could do a filter to ONLY see their items and not worry about the others.

If you want to continue with multiple sheets, we could help with the macro, but we need more info. providing a sample workbook with several sheets with sample data and the eighth “summary sheet” example, we could provide some help with VB to do what you want.

Steve

Reply | Quote

Could you upload an example of what you are trying to do with dummy data?

Reply | Quote