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

Tables and Formulas problem

DonMinter

Member
I have a table that is about 2 lines long (to show sample data). This table's sheet is referenced from another sheet. The problem is, when I add a column to the table, the formulas on the referenced sheet auto-update and destroy the reference, sometimes even changing to "Ref!" errors. I will email the sheet to someone who elects to help, but I don't want to post it because it is based on one of Chandoo's purchased sheets.


When I wrote the code, I had 150 lines of data. I deleted the data, and got the table correct.


The problem becomes, how do I cause the linked sheet to update its formulas to correspond to the table, or prevent them from updating at all, and referencing the absolute cells?


PS. This is not an "Absolute" vs "Relative" problem. It has to do with Excel updating formulas automatically when references are changed elsewhere.


Thanks for any suggestions, offers, or input,

Don
 
Hi Don ,


I am available to help out , but my response will get to you only tomorrow because it is past 10 in the night here in India.


If you don't mind this , you can email me at narayank1026[at]gmail[dot]com


Narayan
 
Thanks, Narayank and Faseeh. Please check your emails. Faseeh, this is a bug in the GanttHourly that I think you already have. It is a problem with the "results" sheet.


Don
 
Hi Don,


That GanttChart looked cool on the first instance, I just dragged down "Task-1" to fill cell beneath and it was Ok, will look again to it for that "Bug" :)BTW you mail did't reached my inbox!!


Faseeh
 
Let me send it again. I sent it from my gmail account this time, Narayank.


I noticed in my original post that I said "add a new column." It should have read "When I add a new row" by tabbing at the end of the table. It inserts or otherwise screws up the table references on the "results" sheet. Those references are supposed to be fixed and absolute, and changing the other page should NOT have any effect on this sheet.
 
Hi Don ,


I have received two emails with attachments :


1. sheetthatdoesanddoesntwork.zip


2. attachments.zip


The file sizes in these two attachments are different ; which one should I consider ?


Narayan
 
Hi Don ,


I think the job is being complicated more than necessary.


1. The GANTT tab in the workbook GanttHourly.xlsx has two columns labelled "Start Hour" and "End Hour" ; are these two columns different from the two columns labelled "Start Hr" and "End Hr" in the TASKS tab ?


2. What is the function of the RESULTS tab ? If the raw data is in the TASKS tab , and the final output chart is on the GANTT tab , why is the RESULTS tab necessary ?


Narayan
 
Narayan,


The Gantt table is only for display and update. It shows the data from the other charts.


The Tasks table is where the data is entered and manipulated.


The Results table is to generate the lookup for any day. This data is all calculated, and it was the only way I found to get the Gantt to be able to grab the needed data off the Tasks table. The whole "Results" table is only for working columns to grab out the data for the Gantt table to display.


I didn't send those attachments. I would guess they are from Faseeh. My files should have been: GanttHourly.xlsx and 20120224Workscope.xlsx. I'll send them a third time, from yet another email address. I don't know why they are not going through.


Thank you very much for the effort you've already expended. I honestly appreciate your help.


Regards,

Don
 
Hi Don ,


I already have those two workbooks from one of the zip files. No need to send them again.


I will refer to one example :


=IFERROR(OFFSET(Tasks!$F$2,Results!$B3,0),"..")


is used in cell D6 in the GANTT tab of the GanttHourly.xlsx workbook ; why can this not be made just :


=Tasks!J3


I suggest the following :


Can you send me the TASKS data for whatever tasks you have already written down ?


I will send you an hourly GANTT chart workbook , with data from your TASKS workbook. I am still not able to understand the reason for the RESULTS tab.


Narayan
 
Narayan,


The "Workscope" file already has data on the Tasks! table. If you change the number in RED on the Gantt sheet, the Gantt will automatically update for that day. If I used an absolute address, it would only pull up that line of data. I need only the tasks for any given day to be shown. Change the number in red "Day to Display" and watch what happens to the display.


The results table is what is figuring out which tasks belong on the Gantt table. If I did the whole project on one chart, it could be dozens of sheets long. I need only one day to display at a time.


Don
 
Hi Don,


Sorry for the inconvenience you might have been facing. I think i am unable to pick-up the central idea of your post. You meant to say:


-"That when you hit the tab the refrences on the Sheet named Result changes"? Is it so?


Please explain the problem in plain word, I am going to add some 200 more steps in your sheet and see why it is malfunctioning.


Thanks,

Faseeh
 
@ Don, I think now i have understood it. Lets say for Day 01 The chart must show only till task "Remove SCR 3 Bridge" for the cumulative hours by that task is 22. The next task must be plotted in the last 02 hours of the day and the next days 02 hours is it so?


Faseeh
 
Thanks, Narayan! That was perfect! Now, I get to figure out how to do 12 hour days using the "Input" table. :)


No, I'm not asking for help there... it's just the next step to get this sheet to the functionality we need for "real world" usage.


You should be given one of the "guru" or "hero" tags here.


Don
 
@ Don, Glad to see your problem is resolved, I have planned to build my own gantt chart and will share with you once it is complete, Thumbs up for Narayan! He is a real guru ;)


Faseeh
 
You never change anything on the "Gantt" page/sheet, except the "Day to Display." When you change that, it should automatically update the Gantt Chart to display only tasks that will occur on that day.


When you input data on the Task page, you only input through the "dependency" column. Also, the "Task number" is automatically generated (column A). All the "start hours" and such are calculated. If you change a dependency, that task should not start until the task it "depends" on starts.


Let's use "saving a file" as an example. To save a file, you first must press the "file" menu option. After this, you press "menu" and then you navigate to the location, and enter a file name. As tasks, you cannot enter a file name before you press the "Menu" option. Therefore, starting the "enter file name" task depends on completing the "press menu option" task. This chart keeps track of when you can start jobs, and how many men you are giving tasks at any given time.


When I first did the chart with the dependencies, I had 10 men doing tasks the first hour, because there were enough tasks that had zero dependencies to allow for that many men to work. The problem is that my job won't allow for ten men. I only have 4 men to use. I changed one task's dependency to an unrelated task, and now the original task won't start until that other task is done. This way, I don't need "extra" men, and can schedule their workloads more accurately.


Likewise, as I do the job, I can update the "duration" from my present guess to the "really took" time. This will then adjust all tasks that depend on this task, and will allow me to easily see if I am on schedule or not, and what my new completion date/time will be.


All of this can be done in other Project programs. The main difference is that my program/spreadsheet allows for detail down to an hour, and not a day. Also, it doesn't "lock you in" to certain data entries before it will display your chart. It's "quick and dirty," allowing me to quickly get the project plan I need without a ton of details I may or may not need for the given project.


Narayan sent me the formula to use for the first column on the results sheet. I have not yet dissected it, so I'm not sure how it functions differently from yours.


Don
 
Back
Top