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

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

CZ20

New Member
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!
 

Attachments

CZ20

New Member
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

Excel Ninja
Hi ,

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

Narayan
 

bosco_yip

Excel Ninja
Try................

In C3, formula copied down :

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

64855

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

Regards
Bosco
 
Last edited:

CZ20

New Member
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

Excel Ninja
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
 
Top