• 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


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

Progress Chart for Road works

Not open for further replies.

I understood that the placement of box is correct as per your attached screen shots.

But i am experiencing same problem, i am attaching the screen shots of the same Sl no 1 and I am using MS Office 2010.

Attaching the file also.2.PNG 1.PNG



Excel Ninja
Would I say interesting again ... ?
Good that You sent that file too, but the active cell was in "O1" and
it's possible that then I open that file it automatic deletes all boxes and sets fixed rowheights.
Screen Shot 2016-02-24 at 09.42.39.png
Please, next time, save file then active cell isn't any 'button' K1,M1 nor O1.
So, I run 'DO IT' and everything was Okay...
There are possibilities ...
Did You anything else than 'DO IT'? Like write some text on sheet?
Rows height have to be same all the time!
If You press 2nd time 'DO IT', is there same case?
Are all 'boxes' in wrong place or just this number 1?
... or this could have 'connection' with same kind of case few days ago...
If You change Si.No. start from 1000, do it work right or do it have same challenge?
Of course, it's possible to make those 'coordinates' with 'boxes' too...
I did many questions, I hope that I'll get as many answers and save file too.


Excel Ninja
Here is a quick test with 'output' AB-column '100m-boxes' ...
You can see 'original values' behind '100m-boxes' too.
Where do it start (000+000)?
do it go smoothly all the time (same height with rows)?

>>> SORRY <<<
<<< Try this version ! >>>
Boxes have to 'delete' in correct order or it will take looonger.


Last edited:

Thanks for spending your precious time.

Did nothing other than DO IT

Row heights of all are 14 (checked)

All boxes are in wrong place.

I checked with starting Si NO from 1000, but same problem occurs.

i am attaching the file.

I am using MS office 2010. is there any connection with version?

100m boxes starts at exactly 000+000, but the remaining are placed at some distance ( the distance is increasing with chainage)





Excel Ninja
This is 'interesting' ... someway.
I have still few ideas...
1) I use Excel 2011 ... few things are totally different
1) Sometimes 'fonts' make challenges. I changed 'output'-sheets font to 'Arial'.
Test this version ... again.
2) I can calculate 'right place to Your PC'. I don't have this 'challenge' but You could set one value to set boxes to right place. Now, it depends from rowheight, but it's possible to set manually too for example 13.975?
You can test this with Range[AB1]. Change that range and press [DO IT].
There are 'extra' value below distance value. Save file for me.
3) I can make enough 'coordinate'-boxes, maybe no need every 10m?



Excel Ninja
Guess what?
I tested this with Excel 2010 and ...
I really think that I found the solution - output-sheets AB1 has 'Excel 2010'-tricky number 13,5. Now, those boxes looked to be in the right positions.
I noticed 'something same' with my another project which I'm changing to work with Excel 2010. Test this version and say YES!



I need to add some more details to the strip chart.

I realized my expertise in excel is null (tried code you had provided to modify) and requesting you to extend your help.

In attached file, Another sheet "Structure" is added where input data is available.

In "output" sheet, i had added a text box at 9+480 to 9+490 to show the present details of Structure.

I need to add the boxes like that for all the remaining.

Thanks once again



Excel Ninja
Okay ... that's possible.
1) These are new S.No.'s ... no connection with 'data input' Si.No.s?
2) 'Structure Details'/'Code' => HPC@Km 9+480 and Drawing -Not submitted ..
no need to write twice same text => 'output'-box would have text like in sample.
3) Colors are fixed.. if Code is 'In Progress' then ALL 'In Progress-boxes are yellow?
I could try to start to do after Your answers...
and when I'll have time to do/test this.

thanks for your reply.

1. No connection with data input Si. Nos (can be changed to different if required for macro).

2. In Structure details "HPC @ Km 9+480" and code is "drawing - not submitted".

In the Object box text "HPC @ Km 9+480 - Drawing - Not Submitted"

3. Yes Colors are fixed.

4. The Boxes of Structures shall be on top of remaining all boxes (Bring to Front)



Excel Ninja
overlapping ... overlapping ... I copied You file's data to here ...
1) I changed that mystic 13,5 to 'tmp!A1' - nice to know
2) That file has ... overlapping ... and I gotta change code then hiding boxes.
3) Check 'Structures' layout
4) Remember, if take/change ... anything to
Si.No. to press [Boxes] before press [DO IT] - safer.
>> Ideas ... Questions?



Working fine.

Understanding the visualization procedure you had adopted to create charts and applying this to my project.

Will inform you if any further ideas or questions.


I am successfully adopting it to my project. while implementing i tried to create a report listing the top layer (in the order given) of work in respective chainages from my input data and couldn't.

I request you to extend your help for ideas to create the same.

attached the file for your reference.




Pl refer to reply #41, In that you had shown the way what i had wanted to convey you (You had shown it as "vertical").

The result you had shown is more than enough for me.

I am attaching the same file for your reference.

In the attached file "input data" is having the data for LHS only. But the actual data will contain LHS & RHS (I had changed the input data with realistic data).

In the previous road show (Pl refer #36), We had created the "boxes" for all the layers of input data.

Now we want to to show the "From chainage, To chaiange & Codes from input data" & boxes for "only Top layers" of respective chainages.




Excel Ninja
1) Max Vertical layout is 16'384m. Is it enough?
2) Did You watch/read/think #42 reply?
3) Watch again 'merge cells 10.xlsb' Sheet 'output' columns 'A' and 'BC'.
Those shows 'only Top layers'. It's possible to move side-by-side too!
4) If You need also same with text-format, that's possible too.



I had read the post #42, and you had used horizontal layout. The sequence of layers has to be like "SH-granular" is on Top and C&G is the bottom most layer.

1) Max vertical layout 16'384m is not enough (our max number is 100000).
2) seen a & bc columns are showing top boxes, but i don't know how to use them for listing chainages.

I hope that i had not explained properly in my previous post. Sorry.

The file "Merge cells 10" is being used for "Detailed Strip chart" in which all the layers executed are shown.

By using the "Detailed Strip Chart" Overlap chainages & the missing layers (the layer(s) where top layer is shown and bottom layers are not available) are being noted manually to correct them in the project.

With input data which is being used in the same file, presently i am preparing the top layers abstract Manually for preparing the following.

1) Find out the chaianges & total Length of each layer constructed (i.e. for LHS or RHS separately). (For ex. How much length "C&G" on LHS and in which chainages c&g is executed)

2) Find the chainages where DBM or BC layers has been constructed on both sides (LHS & RHS) (for ex. from Km 1+000 to Km 1+510 DBM is executed on LHS & RHS)

3) The Online Strip chart i.e. Only Top layers is being shown in respective chainages (LHS & RHS). This one is being used for sending report to Top level officials as they can't see a report bearing 50 to 60 pages.

I am attaching the procedure being adopted. (you may laugh after seeing).

Note: I had learned by saving a xlsx to xlsb the file size is being reduced dramatically. I don't know this *.xlsb format previously.

Thanks for every thing what i am learning and for your precious time being spent for me.

Thanks once again.



Excel Ninja
You want to do double work? That's Okay! ... sorry, waist of time!
You had all numbers already in 'Merge Cell' and now You have done same manually .. ? Oh,no!
As I tried to tell that You had those 'top layers' ready, with all details!
Only make needed movement, even to another sheet!
Screen Shot 2016-03-09 at 12.19.25.png
If You have a lot of extra free time,
You can do this kind of job manually,
You just need to do it two three times.
If You have 'somewhere' misdata (wrong, gotta fix),
You maybe should do it again.
If horizontal -vertical layout is challenge ... You can always turn screen 90degrees.
And as I tried to wrote,
it's possible to make shorter version (show only top layers) from 'data input'.
But, You want to do all of those manually ... and many times.
You wrote '(you may laugh after seeing).'
No, I won't laugh ... I should do something opposite!

I didn't mean that "wanted to do manually". Sorry for my bad English.

I just wanted to show the laborious procedure i am doing for reports.

I need your help to create a report for the following.

1) I need to report the chainages of top layers of LHS & THS in separate sheets (attached the file as per post #39 which you had modified.)

2) Max number in chainage is 100000m.




I had checked the "preview".

1) One Line Strip Chart is in columns BI-BM, and working fine.
2) Top layer chainages in columns CO-DB, are working fine.
3) It is observed that the macro "Solve_TopL" is creating chainages up to 33940. But The maximum chainage in input data is 84920.
4) While "Doit" with option " Valid Overlapping" to "No", The boxes being created from Column "J" to show overlaps are not showing data when we click the Boxes.



Excel Ninja
@ashokkumarkolla - PreView is always ONLY preview!
3) "Solve_TopL" was still in test-version! I updated it.
4) I had marked all 'overlapping' valid, check 'tmp'-sheet, then I don't have always act (372 times) then it asks about 'overlapping'.
Many 'overlappings' are correct/valid, maybe some are incorrect!
All 'overlaps' will show in 'output'-sheet.
ONLY other 'error' as 'Code' won't show in 'output'-sheet; now 98 rows.


Not open for further replies.