Match/Index first and last populated cell to corresponding column header (Gantt chart formulas)

Mrgoodkat

New Member
Joined
Aug 20, 2014
Messages
5
Hi All,

I am dealing with a document similar to a very large Gantt chart with Dates at the top of each column and non zero values in the grid below recording which projects are worked on.

I am looking for a formula that can read the corresponding date to the first and last entry within each row: thus producing a start and end date on each project. However, Zeros populate the 'empty' cells and this cannot be avoided as the data is copied and pasted by multiple other people, and so I am looking for two formulas that can take this into account.

(A1)09/09/201410/09/201411/09/201412/09/2014Start DateEnd Date
Project 10xx010/09/201411/09/2014
Project 2xx0009/09/201410/09/2014
Project 3000x12/09/201412/09/2014
Project 40xx010/09/201411/09/2014

<tbody>
</tbody>

I have been using the following formulas: (In this example for Project 1)

Start date: {=IF(ISERROR(INDEX(B$1:E$1,MATCH(FALSE,ISBLANK(B2:E2),0))),0,(((INDEX(B$1:E$1,MATCH(FALSE,ISBLANK(B2:E2),0))))))}

End Date: {=IF(ISERROR((LOOKUP(2,1/(1-ISBLANK(B2:E2)),B$1:E$1))),0,(LOOKUP(2,1/(1-ISBLANK(B2:E2)),B$1:E$1))}

This formula does work if the empty cells are actually empty i.e do not contain 0's but as said previously this cannot be avoided and so I am looking for a formula that can ignore 0 values.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Do you actually have "X" in those cells or those are used to represent actual numbers in there?
 
Upvote 0
Do you need something like this


Excel 2010
ABCDEFGH
1(A1)09/09/201410/09/201411/09/201412/09/2014Start DateEnd Date
2Project 101020010/09/201411/09/2014
3Project 230400009/09/201410/09/2014
4Project 30001512/09/201412/09/2014
5Project 403317010/09/201411/09/2014
Sheet4
Cell Formulas
RangeFormula
G2{=INDEX($B$1:E2,1,MATCH(1,IF(B2:E2<>0,1/(B2:E2<>0)),0))}
H2{=LOOKUP(9.99E+307,IF(B2:E2<>0,B2:E2),$B$1:$E$1)}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Hi Momentman,

Thanks for your swift reply, apologies I made such a rushed post, but your assumptions were absolutely right!

Your spreadsheet mock up is also a much better explanation for others wanting a solution to this - and I can confirm that it works flawlessly.

Thanks again for your help!
 
Upvote 0

Forum statistics

Threads
1,211,697
Messages
6,103,360
Members
447,861
Latest member
LllopezXC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top