Find first non-blank cell value combined with =CELL("format"

[CZ20]

Hello all,

Looking to combine =CELL("format" and possibly ISBLANK or INDEX/MATCH in a formula (no macros please).

Looking to find the value of the first non-blank cell in a row that is also not bold (or possibly not coloured).

Example attached, row 6, shows that a typical formula to find first non-blank isn't useful as it would pick up the running balance of the previous account (210) instead of the number I am actually looking for (100).

Have used {=INDEX(range,MATCH(FALSE,ISBLANK(range),0))} before to return first non-blank, but doesn't work in this instance, unless perhaps I can only pick up multiple individual cells in the row (ie not the blue running balances columns).

Thanks for your help!

 

[CZ20]

If it helps, when using =CELL("format",xxx) a bold cell returns a value of F2 and a non-bold cell returns a value of G, as per exceljet's help list here.

 

[NARAYANK991]

Hi ,

In my Excel 2010 , the CELL function remains G irrespective of whether a cell has been formatted BOLD or not.

Narayan

 

[bosco_yip]

Try................

In C3, formula copied down :

=INDEX(D3:L3,MATCH(1,INDEX(($D$2:$L$2<>"Running Balance")*(D3:L3<>""),0),0))



Remark : The above formula/result seem doesn't related to bold or not bold cells testng.

Regards
Bosco

 

[CZ20]

Thanks Bosco!

This is definitely much easier than the workaround I was thinking of.

Do you have any suggestions on how to exclude cell if the heading does not only contain the term "running balance" but might actually contain "111-111 123456 Running Balance A/c"?

 

[bosco_yip]

Thanks Bosco!

This is definitely much easier than the workaround I was thinking of.

Do you have any suggestions on how to exclude cell if the heading does not only contain the term "running balance" but might actually contain "111-111 123456 Running Balance A/c"?

Hi,

It is possible could find formulas to skip the columns of header wording "running balance" or "111-111 123456 Running Balance A/c", but subject to the layout of the input table.

Please upload your file to us for further study.

Regards