CODEWITHSUNDEEP
excelexcel-formulaoffice365
zukakog
zukakog

Reputation: 3

How do I find the value of the previous cell containing specific text in a table column?

Cells in the "Schedule" column should alternate between "A" and "B". This year, we want to show weekends as blank and include any special days in the "Schedule" column. Since D3 is "B", D6 should be "A" and D8 should be "B" and so on. Picture of current table in Excel

Desired Table:

Schedule Weekday Special
A Thursday
B Friday
Saturday
Sunday
A Monday
Teachers Only Tuesday Teachers Only
B Wednesday

I started by using Indirect, but for that to work I need to be able to find the number of cells to count back to the previous "A" or "B". Here's the code in the Schedule Column:

=IFS(
OR([@WeekDay]="Saturday",[@WeekDay]="Sunday"),"",
[@Special]<>"",[@Special],
ROW()=2,"A",
INDIRECT(ADDRESS(ROW()-1,COLUMN()))="A","B",
INDIRECT(ADDRESS(ROW()-1,COLUMN()))="B","A")

Results:

Schedule Weekday Special
A Thursday
B Friday
Saturday
Sunday
#N/A Monday
Teachers Only Tuesday Teachers Only
#N/A Wednesday

I've searched for various solutions, but they all seem to show how to find the LAST cell, not the PREVIOUS cell containing either "A" or "B".

Other solutions welcome. Please no "shift+enter arrays" as this spreadsheet is used with tools that don't support them.

Thanks!

Upvotes: 0

Views: 382

Answers (2)

P.b
P.b

Reputation: 11468

Instead of using INDIRECT you could choose to use INDEX:

=LET(x,E$1:INDEX(E:E,ROW()-1),IFERROR(IFS(WEEKDAY([@Date],2)>5,"",[@Special]<>"",[@Special]),IFERROR(TAKE(TOCOL(IFS(x="B","A",x="A","B"),2),-1),"A")))

First x is declared to start from the first row and run to the current row minus 1. This is used in IFS(x="B","A",x="A","B") This results in an error value for any cell's value not equal to "A" or "B". Wrapped in TOCOL() with second argument 2 will filter out the errors and leaves us with the A's and B's only. Since I switched the "B"'s to "A"'s and vice versa, the last found value will be the value we need to show, therefore Take with second argument -1 will take the last value of this array. If none of the rows prior to the current row contain either an "A" or "B", the TOCOL will result in an error and the IFERROR will force it to show an "A" (this is to deal with the possibility if the table starts with a weekend as an alternative to your ROW()=2

I also made another modification: WEEKDAY with second argument 2 will start counting from Monday = 1 .. Saturday = 6, Sunday = 7. So each weekday >5 is weekend.

Upvotes: 0

Suraj Shourie
Suraj Shourie

Reputation: 2605

If you're problem boils down to find "the PREVIOUS cell containing either "A" or "B".", an easy solution would be for you to add a dummy/temp columns in column "G" and "H" With something like this cell "G5": =IF($D5="A",ROW(),G4). and "H5": =IF($D5="B",ROW(),D4) and drawdown for the entire column.

This will give you the row number for the previous cell containing A or B and you can use this in INDIRECT.

This assumes you can add new columns (hide it if needed) and the number of rows are not too large to slow down the spreadsheet with adding two new columns

Upvotes: 0

Related Questions