Generating a column value until another column is filled in

Good afternoon, I am looking to see if there is even a formula for what we are looking to do. We have an "HRS Left" column that projects the hours left on a scope item with this formula

=SUM(BAC@row - HTD@row)

However!, until we actually start working on that scope we have a "BAE"column that carries our Budget At Estimate hours for that scope item.

Is there a formula that will tell smartsheet to carry the "BAE" column value in the "HRS Left" column UNTIL the "BAC" and "HTD" columns have values?

Please reference attached PDF

Column Formula.pdf

Find more posts tagged with

Accepted answers

Mark Cronk

Hi @BESP10 ,

You BAC and HTD columns have 0s in them so they're not blank. Change your formula to look for ISBLANK or =0.

Help?

Mark

Mark Cronk

Try:

=IF(OR(ISBLANK(BAC@row), ISBLANK( HTD@row), BAC@row=0, HTD@row=0), BAE@row, BAC@row-HTF@row)

The OR(...) says that if BAC or HTD are blank or =0, then return BAE. Otherwise return BAC-HTF.

Mark

All comments

Mark Cronk

Hi @BESP10 ,

Try:

=IF(OR(ISBLANK(bac@row), ISBLANK(htd@row)), bae@row, SUM(BAC@row - HTD@row))

Work?

Mark

BESP10

Hi @Mark Cronk

This one is giving me a value of 0 in the "HRS Left" column even though the "BAE" is showing a value of 16 and no work has been done yet.

Thoughts?

Thanks

BESP10

Hi @Mark Cronk

Sorry to bother , just checkin in to see if you any insight as to what could be giving a value of 0 instead of 16?

Thank you

Mark Cronk

Hi @BESP10 ,

You BAC and HTD columns have 0s in them so they're not blank. Change your formula to look for ISBLANK or =0.

Help?

Mark

BESP10

Thank you

I have been trying for the last hour ... I am struggling with formulas @Mark Cronk

But thank you for your help on this

Mark Cronk

Hi @BESP10 ,

Did you get it to work?

Mark

BESP10

I did not,

I have no idea where to out the or =0, I am very novice on the formulas and their syntax

I understand the logic but the actual placement is getting to me

Thank you for checking in @Mark Cronk

I actually posted another topic looking for the formula, did not want to bother you again! #truestory!

Mark Cronk

Try:

=IF(OR(ISBLANK(BAC@row), ISBLANK( HTD@row), BAC@row=0, HTD@row=0), BAE@row, BAC@row-HTF@row)

The OR(...) says that if BAC or HTD are blank or =0, then return BAE. Otherwise return BAC-HTF.

Mark

BESP10

BINGO!

THANK YOU @Mark Cronk

Mark Cronk

Glad you finally found a solution. Thank you for being patient and persistent

Mark

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.