• 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.

Dynamically add a row to a table using a formula

D

DonMinter

Member
I want to do a daily Gantt chart. I have a single sheet that has among its variables:


Task Name | Start Day | End Day | Start Hour | End Hour


On another sheet, I have tables set up for day 1, day 2, etc. Each table is set up with the columns:


Task Name | 0:00 | 1:00 | 2:00 | ... | 23:00 (That's 24 hours.)


My main problem is how to dynamically add rows to my table so that its conditional formatting can take place to display a Gantt Chart style of set up.


So... How do I add a row to a table using a formula?


Once again, I'm trying not to re-learn VBA, unless I absolutely have to do so.


Any suggestions? I'm still web searching, but I'm not sure how to do this one without VBA.
 
Hi,


You mean that for every new task you add bellow the last task the formitting must apply to result in an activity shown on the gant chart??
 
Don,


I'm relatively certain that conditional formatting works with dynamic ranges. So, assuming you set one up, it should be easy to just put the row on your list and have the formatting just appear automagically.
 
Or wait: maybe I'm misunderstanding the requirement here.


Why are we using a formula to add the row in the first place?
 
Faseeh,


Thanks for the inquiry. Short answer to your question: Yes, although it need not be ONLY the end (but "added to the end" will work).


The reason: The table has conditional formatting applied. Essentially, any non-blank cell is shown as a solid color. Therefore, I need more rows added to account for days with many or few tasks. Whether they go at the end, the middle, or the beginning does not matter. What matters is that I can add rows to my table with a formula.


Right now, I'm just going to make the tables 50 rows long manually. I found a VBA snippet somewhere with rows added to a spreadsheet. I don't know if they can be added to a table.


I'm open to other suggestions if you have any.


Thanks again,

Don
 
Dan_l,


Thanks for your time and effort, as well.


I have a single table with all of my tasks. Any one task has a "day" associated with it. Which "day" is determined according to other formulas. I just set up a column where that part is calculated, so I now have a single "day" integer for any given task.


I have other tables for each "day." I want all tasks from the "task table" to go to the appropriate "day" table. Then, the start and end hours will be used on that table to generate the look and feel of a Gantt Chart, but done hourly for one day - not daily for one project.


I'm going to see if I can figure out how to do conditional formatting with a dynamic range without a table. I can easily do a separate sheet for each day, then pull out the correct number of days for any project.


Thanks for the lead/idea.


Don
 
Emmmmmm......


Just a thought:


If you're trying to avoid doing vbaish things, this might be a better job for a single sheet with filters applied. There are techniques using formulas which would allow you to pull in certain rows based on criteria onto another sheet, however, you might find those techniques 'reliability prohibitive' for a non-disposable project.
 
Well, crud! That was easy. I thought conditional formatting only applied to tables. I can set up an entire sheet to make any cell with frzzlmx in it look the way I want. Now all I have to do is copy the tasks to the appropriate sheet, and format all sheets to respond appropriately to formula-generated values on that sheet. My labels won't be affected, as I'll pick an appropriately obscure value (like "frzzlmx") that will never occur randomly on the sheet.


Thanks for the help. I think I can get it from here, Dan_l. That'll teach me not to read things in to something that just isn't so. I'll let you all know if it works later. I am now leaving to go home.


Don
 
Dan_l,


I don't need to grab the whole row. I'll grab the Task Number and Task Description cells. Using that data, I'll look up the start day/hour and end day/hour, then set up the appropriate blocks to be filled with my obscure fill value. That fill value will then trigger the conditional formatting to make it look like a solid block.


I know I'll eventually be forced back into coding, but until that happens, I'll stick with my formulas. This spreadsheet file should allow me to generate my hourly Gantt's for any future project where the client has such a tight schedule to necessitate an hourly-detailed project.


Thanks for your input, both of you. Your help was invaluable.


Don
 
Happy to help. Don't be a stranger around here either, we have a quite a few people trying to implement project management stuff in one way or another.....
 
Hi Don,


Is it possible to get a sample file?? Whether we are able to find an exact solution or not atleast we can try for it?? I have used gantt charts but i donot know whether they were as much complex as you are describing them... Are u making a saperate sheet for each day?? A master sheet will contain activities? Act will be trasfered to respective sheets?? Sheets will have a header row for days? Left most column for time??


To sumup plz upload a sample file :D


Thanks,

Faseeh
 
I'm having yet another problem this evening:


I am working on a formula to grab the "task #" of every row that has a specific value in a given column. The number of rows in the table I'm searching will vary.


Imagine a table with the following columns:

Task # | Task Description | Start day


That's enough for this illustration. I have to come up with a way to look through all of the rows - there could be hundreds - and grab the "Task #" for any row where "Start day" is equal to some given value. I then populate a row on a different sheet with the


task # | Task Description | more data from the table above


I need to grab all of the tasks that have the same given value for "start day" to make my new table. The start days will all be integers. They may not be in numerical order.


Any suggestions? Am I forcing myself into vba by trying to make this too automatic and dynamic? Honestly, I am running out of internal ideas on how to do this one.


Note that I don't use Excel all that much. I have done quite a bit of programming, but not much vba, and very little excel in general. I am currently looking on the 'net and searching the excel reserved words for what might help here. It's late, but I really need to build my new tables by COB tomorrow, as I have other things to do, and I wanted this done today.


Any suggestions? Thanks for reading this far, regardless.


don
 
Faseeh, I don't have an upload site (and yes, Dan_l, I read your sticky post; I just am used to ftp sites and I don't like folks putting conditions on me. If I find the need, I'll grab some webspace and do my own ftp location for file reference). EDIT: I was talking about the upload sites putting conditions on me to upload my files. I was NOT talking about any user here putting conditions on me. When I re-read this, it looked like I was accusing Dan_l's post/suggestion of putting conditions on me.


I'll post the finished file when I'm done. Right now, it has three sheets:


GanttChartByHour

Inputs

Tasks


GanttChart will be the display sheet, so I put it in the front

Inputs will be for constants to define work days, number of shifts, length of shifts, etc.

Tasks is the large table


Tasks columns are:


Task #

Task Description

Duration (in hours)

Number Men

Dependencies

Start

End

Start Day

End Day

Start Hour

End Hour


Task # autoupdates and starts with "1"

Task Description is entered by the user

Duration is entered by the user and is the number of hours needed for the task

Number men is the number of men needed to do the task

Dependencies is entered by the user and is the task number of the last task that has to be completed before this task can be started.

Start and end are calculated values that are the absolute hours for any given task.


The formula for start is:

'=IF(ISBLANK(E3),0,INDIRECT("G"&(E3+2)))'

This says "if the dependency is blank, then the start is hour 0. If it is not blank, then the start hour is the end hour of its dependency pointer."


Start day and End day are just the absolute hours manipulated into days.

Start hour and End hour are calculated within the given day using some modulo arithmetic.


Anyway, once I'm done, I'll send the file to someone who can post it, or I'll figure out a way to post it myself. I am to the point of building my Gantt tables. The Tasks table is complete and working.
 
You want to grab task no. For every day? If i am correct then u may proceed with this as it is not that difficult as u are thinking.. I have an idea!! Go to the sheet where u want to bring in the data, dedicate a column to find if the the day is same as u are looking for use this formula and fill the column:


if(CellUrLookingInto=Day,row(cell),"")


this will give u series of row nos then add another column and apply this formula throughout:


Large(ColWithRowvalue,row(a1))


this will give u consective values of row that matches ur criteria for a specific day!! Now use offset to fatch the data and use the second col i mentioned to offset any specific row!! Got it??


Making this post from my handset will upload a sample file 2mrow my local time is 12:30 pm :) hope it will be helpfull.....


regards,

Faseeh
 
Faseeh,


I am sorry to say I don't get it. It is nearly midnight. I've looked up the "row()" function, and think I have the first part figured out. My column will give me the row number of any cell that contains the checked criterion. Many of the cells in this column will be blank.


The second formula eludes me. I think I need to get some sleep. I'll look into this again in the morning. Thanks for the input. Once I have this working, it is just a matter of getting the chart to display, then doing the offsets using my input sheet data.


don
 
Hi DonMinter,


Kindly check following file. This was the concept i refereed to in my last post.

http://dl.dropbox.com/u/60644346/Project_Templete.xlsx


Thanks,

Faseeh
 
Faseeh, that is fantastic. I was ready to give up. I'm working on a different task right now, but when I get back to this in the evening, I'll adapt it for my uses.


I glanced at your formulas... I have to say I'm completely lost. Give me some time to get my head around it. When I complete the final document, I'll send you a copy for your uses.


At a glance, I noticed you have hours 0 to 24. I changed this to 0 to 23. Also, you checked for <= for each of your gantt fills. I changed the second to < for the same reason that the hours' starts and ends both must be counted (23 - 0 is 23, but that's 24 different "hours").


Lastly, I changed the fill and the text colors to the same for the gantt, so now they just look like filled blocks.


Once I merge your formulas with my file, we're going to have a working "hourly gantt chart" for any day desired.


Thanks LOADS,

Don
 
Hi Don,


Good to see that it is what you required. :D I am waiting for the final templete when you will finish your working. Best wishes for your project!


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