• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Scheduling Tile chart from work sheet's info

M

Me-Ted

New Member
Hello All!!! I’m using a work sheet to track the progression of a system from the beginning of engineering to the final transfer of custody and care of the constructed system. The reason being is there are several business units that need to know when it is there turn to work on their portion of the process. The work sheet has the system name in the “A” column and there are two other columns that show the dates two other groups need to make staff available. In the office we use a tile chart for scheduling. The column headers represent the end of the work week, if the system needs to be walked down within the 7 days prior to this date the system needs to be shown in the column. So I need to look for systems within my work sheet that need to be walked down within the 7 day time period. I have to recreate the tile chart weekly and it takes me hours to transfer the information. Can someone please help with the formula needed to extract the dates from the work sheet relative to the system (Column A) and return it under the associated tile chart column!!!! Need help! Thank you so much!!!!


Example of Tile chart column Example Of Work sheet column

02-Jun-12 - Header System - Header WD - date column

90-106-800-8204 - System # name 90-106-800-8204 System # name 02-June-12

Group Transfer - System name
 
https://hotfile.com/dl/161290741/897103d/2012-06-01-Pads_106_and_116_skyline-_for_review-1.xlsx.html


Here is the tile chart
 
Hi, Me-Ted!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


And about your question...


Would you please clarify this points?

a) which is the worksheet your refer as "has the system name in A column and there are other two columns..."?

b) what should be done with FWD, MC and TCCC worksheets?

c) besides, can you explicit two examples of desired output?

Thanks.


Regards!
 
Thanks SirJB7!!! I'm very pleased this site exists!!! OK, so I introduced myself and read the netiquette, and posting files. Bare with me as I learn.

A) Please see new up loadable example. The GSR sheet is an example of the 3 columns that need to be captured on the tile chart (FWD).

B) For example FWD needs to show that System 90-106-800-8014 needs to have it's "Final walk down (FWD) June 27th and it needs to be shown in the column showing June 30th as this represents the end of the week.

C) I use the GSR for my meetings and collect all my data here. I need to submit the FWD tile chart weekly in the attached format is there are multiple groups who use it so the formatting must remain the same. What I would like to have done is when I fill out my GSR sheet the applicable data is automatically transferred to the tile chart (FWD) with a formula. I will then only have to populate 1 work sheet and I can forward the Tile chart onto my colleagues without changing the format.


https://hotfile.com/dl/161307679/eab517e/Example.xlsx.html


I hope I haven't confused the situation anymore and have shown some light on my situation and needs. Thank you for looking at my question
 
=IF(COUNTIFS(GSR!C2:C6,">="&DATEVALUE("9-jun-12"),GSR!C2:C6,"<="&DATEVALUE("16-jun-12")),GSR!A2,"")


OK, now I have the formula working to return a value if my date requirements are met, but I need the formula to find all the dates within a column between the time frame and return its' column "A" value if it has not yet been listed. What I need to find all the dates within my criteria and list them all one after the other in a column but only once.!!
 
Hi, Me-Ted!


I didn't forget you, just been a little busy, your issue been another little hard, and myself been another little lazy, as usual I should say.


Give a look at this file:

https://dl.dropbox.com/u/60558749/Scheduling%20Tile%20chart%20from%20work%20sheet%27s%20info%20-%20Example%20%28for%20Me-Ted%20at%20chandoo.org%29.xlsm


The idea is that you fill sheet GSR with your data (I copied it from sheet FWD, I left the formulas there, just fyi), and new sheet FWD2 gets automatically populated: no macros, no VBA code, just formulas and CF (conditional formatting) -I left you the customization of the system name cells backcolor, I just use one, remember my laziness?-.


I still have one issue to solve that is related to helper column G in sheet GSR and that's why you see chained stacked instead flat stacked fulfilled cells. I hope to solve it by tomorrow, but no promise at all.


Just advise if any issue or comment.


Regards!


PS: I believe that this is a quote from Napoleon Bonaparte, "dress me up slowly, I'm in a hurry"... we're all in a hurry I think. Just a humble and little reminder for the 13th. paragraph of the 3rd. green sticky post at this forums main page. :)
 
Hi, Me-Ted!

Please download again the file from the same link. It's updated and I guess that now does the job.

Regards!
 
SirJB7, Your work is greatly appreciated and let me make note that I really enjoyed your comments. It really added some personality to the overall experience on this site and your comprehension to problem solve was truly excellent. Regards-
 
Hi, Me-Ted!

Glad if it helped you. Thank you very much for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top