You’re offline. This is a read only version of the page.
I am trying to add a formula to an excel template and it is not working. The formula is to get the "closest milestone date that is not in the past".
this is from the excel document (not yet in Dynamics)
In excel this is the formula,
{=IF(MIN(IF(SSBPIPTable[@[1 mo Milestone]:[48 mo Milestone]]>=TODAY(), SSBPIPTable[@[1 mo Milestone]:[48 mo Milestone]]))=0, "N/A", MIN(IF(SSBPIPTable[@[1 mo Milestone]:[48 mo Milestone]]>=TODAY(), SSBPIPTable[@[1 mo Milestone]:[48 mo Milestone]])))}
When I try to add to the template, I am getting data validation errors on the field.
I have set up ranges "milestone" on the columns 1 mo -48 mo milestones, and used the formula below and getting results in 1/0/1900. I questioned whether I needed the name of the column at the beginning as it is "=[@[Next Milestone]]"
=[@[Next Milestone]]=IF(MIN(IF(Milestones>=TODAY(), Milestones))=0, "N/A", MIN(IF(Milestones>=TODAY(), Milestones)))
This formula without the column name in the beginning: =IF(MIN(IF(Milestones>=TODAY(), Milestones))=0, "N/A", MIN(IF(Milestones>=TODAY(), Milestones))) results in #VALUE!
Can anyone help with what this formula would be written on the template?
Hi BritB,
First of all, please note that the cell format and column headers in the Excel template cannot be modified.
Then, when we create Excel formulas, please don't use column titles or cell numbers. Instead, use the table column names, and define names for cells or cell ranges.
The format of the range name is @[name]. If we want to make a range of cells, please use this format. (Refer to this article.)
Please refer to this article for how to define and use names in formulas: https://support.office.com/en-us/article/define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64
Hope this helps.
Best Regards,
Lu Hao
Hi BritB - Have you visited Microsoft's Tech Community? You can also post your question about Excel or search for solutions related to your issue.
Thank you
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (