Project Manager "Home Page"

[LogMil82]

I am a project manager for a subcontractor in Dallas. I am consistently looking for better ways to manage all of the data that comes into my office. I have to track Purchase Orders, RFI's, Submittals, Action Items &/or Requested Information, Critical Delivery Dates, etc. Right now I have several workbooks that manage each individual item.
I have several things I want to do; however, I want to start with consolidating the information first, and creating a "Home Page". Once I have consolidated all of the workbooks into one, I Need your help in creating the "Home Page" that has a separate section for each category, i.e.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
RFI's (Would show only RFI's that are still pending)
Submittals (Same as above)Action Items (Same as Above)

Attached is an example of what I am talking about. Should be clear.

http://www.4shared.com/file/mUX1yuz8/Home_Page.html


Please help, I cant seem to figure this out.

 

[Akashwani]

Hi,

Does this relate to this?....

http://www.mrexcel.com/forum/showthread.php?t=556412

Ak

 

[LogMil82]

Yes and no.

I played with what you sent me last time for about a week and couldn't figure it out.

Did you look mine and see what I was trying to do? I added one component that I didn't mention last time and the rest I hopefully simplified.

Thoughts? Am I just not understanding what you sent me?

 

[Akashwani]

Hi,

I think we are both misunderstanding one another
So lets start with sheet Homepage.....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	*	*	*	*	*	*	*	*	*	*	*	*	*	*
2	NEW RFI			*	*	*	*	*	*	*	*	*	*	*
3	Last RFI #	*	*	*	*	*	*	*	*	*	*	*	*	*
4	Next RFI #	Subject	Question	Priority (# of Days, Today + this cell)	Cost Impact? (Yes or No)	Drawing #	Spec Section	*	*	*	*	*	*	*
5	*	*	*	*	*	*	*	*	*	*	*	*	*	*
6	*	*	*	*	*	*	*	*	*	*	*	*	*	*
7	NEW Submittal			*	*	*	*	*	*	*	*	*	*	*
8	Last Submittal #	*	*	*	*	*	*	*	*	*	*	*	*	*
9	Next #	Subject	Spec #	Priority (# of Days, Today + this cell)	Drawing #	Vendor/Sub	Item 1	Item 2	Item 3	Item 4	Item 5	Item 6	Item 7	Item 8
10	*	*	*	*	*	*	*	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*	*	*	*	*	*	*	*
12	Action Items			*	*	*	*	*	*	*	*	*	*	*
13	Last Action Item #	*	*	*	*	*	*	*	*	*	*	*	*	*
14	Action Item #	Subject	Vendor/Sub	Priority (# of Days, Today + this cell)	Request?		*	*	*	*	*	*	*	*
15	*	*	*	*	*	*	*	*	*	*	*	*	*	*
16	*	*	*	*	*	*	*	*	*	*	*	*	*	*
17	Pending Items			*	*	*	*	*	*	*	*	*	*	*
18	Number Reference	Subject	Days over Due	Vendor	*	*	*	*	*	*	*	*	*	*
19	*	*	*	*	*	*	*	*	*	*	*	*	*	*
20	*	*	*	*	*	*	*	*	*	*	*	*	*	*
21	*	*	*	*	*	*	*	*	*	*	*	*	*	*
22	*	*	*	*	*	*	*	*	*	*	*	*	*	*
23	*	*	*	*	*	*	*	*	*	*	*	*	*	*
24	*	*	*	*	*	*	*	*	*	*	*	*	*	*
25	*	*	*	*	*	*	*	*	*	*	*	*	*	*
26	*	*	*	*	*	*	*	*	*	*	*	*	*	*
27	*	*	*	*	*	*	*	*	*	*	*	*	*	*
28	*	*	*	*	*	*	*	*	*	*	*	*	*	*
29	*	*	*	*	*	*	*	*	*	*	*	*	*	*
30	*	*	*	*	*	*	*	*	*	*	*	*	*	*
31	*	*	*	*	*	*	*	*	*	*	*	*	*	*
32	*	*	*	*	*	*	*	*	*	*	*	*	*	*
Home Page

What do you want to do with this sheet?

Ak

 

[LogMil82]

First part -

In this example there is 4 sheets in this work book.

1. Home Page
2. RFI Log
3. Action Item Log
4. Submittal Log

Second Part -

RFIs, Action Item and Submittals will be managed from their own sheet. The Section on the Home Page Labeled "New RFI", "New Submittal", & "Action Item" are where you would create the new item of that catagory. For example, (New RFI)

Last RFI # = Shows the largest RFI number from the RFI Log for reference

Next RFI # = Type in that cell the next RFI number you want.
Subject = Type the subject matter
Question = Type the question
Priority = How many days? Lets say I need it back in 7 days. I type 7 here.
Cost impact = type "yes" or "no"
Spec Section = Type Section # here.
​

Third Part -

After this is completed click the button "Send" (This didnt show up on your copy and paste.

Then this information is added to the RFI log as the next line item. (Note in the RFI log the "Date Sent" for this RFI would be the same day as it was created and the "Due Date" equals Date sent + Priority #.)
​

Forth Part -

The Refresh Button will clear the items in part 2 above (Clear info, wont add anything to RFI Log)

The Next RFI # etc shown in the cell would be background text.

​

Sixth Part -

This is the most important part. The "Pending Items" will reference the three other sheets and show only the times that dont show a Returned date in the return Date column.

These items will be sorted by most critical first.
​

 

[Akashwani]

Hi,

First off, I don't know why, but your file keeps causing my Excel to crash/freeze!!
So based on that alone I wont be able to help you further.

IF, I understand you correctly, I think what you want to do is possible, but not straight forward with formulas.
The layout you have makes getting the right solution very difficult because the data you want to display in the "Pending Items" section is in different columns on different sheets, the relevant column headings have different names and some of the data you want to return is not on all the sheets.

It looks to me like you have 2 options....
1. Reorganise your sheets so that the data you want is in the same columns.
2. Start a new thread, something like "VBA Lookup across Multiple sheets" and ask for a VBA solution for this problem. Post some sample data of each sheet and the expected results in "Pending Items". It makes it easier for the people on here to give a suitable answer if they can see the data, for example.....

Excel Workbook
	A	B	C	D	E	F	G	H	I	K
1	RFI Log					*	*	*	*	*
2	*	*	*	*	*	*	*	*	*	*
3	RFI #	Description	Sent	Due	Received	Answer	Subject	Spec REF	Drawing #	Cost Impact (Yes Or No)
4	12345	RFI Log B4	5/1/11	6/1/11	6/5/11	RFI Log F4	Cat	RFI Log H4	RFI Log I4	Yes
5	12346	RFI Log B5	6/6/11	6/20/11	*	RFI Log F5	Dog	RFI Log H5	RFI Log I5	No
6	12347	RFI Log B6	6/22/11	6/30/11	*	RFI Log F6	Bull	RFI Log H6	RFI Log I6	Yes
7	*	*	*	*	*	*	*	*	*	*
RFI Log

Would this be the expected result based upon the above sample data?....

Excel Workbook
	A	B	C	D
17	Pending Items			*
18	Number Reference	Subject	Days over Due	Vendor
19	12346	Dog	5
20	*	*	*	*
21	*	*	*	*
22	*	*	*	*
Home Page

I hope that you find a solution to your problem.

Good luck.

Ak