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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Do you actually have "X" in those cells or those are used to represent actual numbers in there?
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Mrgoodkat

New Member
Joined
Aug 20, 2014
Messages
5
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!
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
You are welcome. Glad to help
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,652
Messages
5,838,597
Members
430,557
Latest member
MK15

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
Top