0

I'm looking to improve a formulas. The formula is:

=IFERROR(INDEX(IndexColumn,SMALL(IF(MilestoneColumn=TODAY(),ROW(MilestoneColumn)-ROW(MilestoneColumnFirstCellLocked)+1),ROWS(MilestoneColumnFirstCellLocked:FirstCellUnlocked))),"")

My issue is that this returns the index column value for index values that have the assigned milestone date for today. However, I would like to have the index value appear when the milestone date equals today or yesterday or past dates where the adjacent status value is "P" projected.

When I use the =OR() function it does not work. I've tried placing OR() after the IF() function and before. I've tried multiple scenarios. The only progress I have had is by using VLOOKUP() paired with an IF() formula on whether adjacent cell has "P" projected status.

Such as:

=IFERROR(IF(VLOOKUP(INDEX(Utilities'!$E$5:$E$135,SMALL(IF(Utilities'!$O$5:$O$135<=TODAY(),
ROW(Utilities'!$O$5:$O$135)-ROW(Utilities'!$O$5)+1),ROWS(Utilities'!$O$5:$O5))),Utilities'!$E:$P,12,0)="P",
IFERROR(INDEX(Utilities'!$E$5:$E$135,SMALL(IF(Utilities'!$O$5:$O$135<=TODAY(),
ROW(Utilities'!$O$5:$O$135)-ROW(Utilities'!$O$5)+1),ROWS(Utilities'!$O$5:$O5))),""),""),"")

The only issue with this approach is it pulls incorrect data as it gets further down the dashboard column and it populates/stacks the index column values with blank cells between populated cells.

I have milestones managed by dates and an adjacent status column. My goal is to pull what in row/cell has a date today or prior and has a "P" in adjacent status column - just need a cleaner way to do this.

Data source

Dashboard column (Pulls Index column data based on date

Improve this question
5
  • 2
    Please provide a minimum working example and show your data structure, otherwise its difficult to see whats happening.
    – karl
    Commented May 1 at 6:09
  • added. Essentially, I have multiple milestone across column in multiple sheets. I'm looking to formula a dashboard by milestone column where project IDs (index column) would populate in dashboard milestone column when that projects milestone forecast equals today or a previous date & status column has "P" (projected)
    – JD Foshee
    Commented May 1 at 14:13
  • Sounds like you are wanting the FILTER() function. If not you need to edit your ask to make it clearer.
    – gns100
    Commented May 1 at 19:17
  • I'm not familiar with the Filter() function. I'll look into this. My ask is for whatever is the cleanest formula (way) to achieve this dashboard view (for current or past milestone dates)
    – JD Foshee
    Commented May 1 at 20:40
  • Could you add a Table Markdown with some data so we can at least copy something to try on. If your keys are sensitive, use mockup data (xxx, 123, a45 for example), that is still representative (so it contains certain characters, lenght is important or not, ...).
    – Excellor
    Commented May 2 at 7:49

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .