Adding New rows with date based on cell values

[hyraxx]

Hi,

Trying to figure our how to add new rows to the bottom of the sheet, with the date and a description based on each cell being marked with a value of 1, A or S.

I've included an example worksheet any advice would be warmly welcome if this can be done without vba then even better.

[Glenn Kennedy]

There may well be an easier way to do this - but I couldn't think of one!!

1. dates in December were incorrect: 1/1.2017 changed to 1/1/2017.

2. Can't use 1.0 for Booked-Off days. can you use B instead?

To return the dates:
=IFERROR(1/(1/INDIRECT(TEXT(SMALL(IF(($B$2:$AO$25<>"")*(ISTEXT($B$2:$AO$25)),(ROW($2:$25)-1)*100+COLUMN($B:$AO),10^10),ROWS($1:1)),"R0C00"),)),"")

To return the absence types:
=IFERROR(LOOKUP(INDIRECT(TEXT(SMALL(IF(($B$2:$AO$25<>"")*(ISTEXT($B$2:$AO$25)),(ROW($2:$25))*100+COLUMN($B:$AO),10^10),ROWS($1:1)),"R0C00"),),{"A","B","S"},{"Absent","Booked Off","Sick"}),"")

Both of these are array formulae.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[hyraxx]

You are a star, the b works just as good for my needs.

[BoredWorker]

Hi Glenn,

I impress on and enlighten from your formula, I try to use index but fail in the column section as number of column cannot arrange by small function.
but I not understand the reason why need to have the (1/(1/ in the formula (highlight red in below) as it already have the result from the indirect.

=IFERROR(1/(1/INDIRECT(TEXT(SMALL(IF(($B$2:$AO$25<>"")*(ISTEXT($B$2:$AO$25)),(ROW($2:$25)-1)*100+COLUMN($B:$AO),10^10),ROWS($1:1)),"R0C00"),)),"")

thank you,

[Glenn Kennedy]

Hyraxx. You're welcome...

Boredworker. I had tried INDEX SMALL as well, and also some SUMPRODUCT combos, but I couldn't quite get it right.. In the absence of the eror trap, the formula returns zeros when it runs out of values. 1/(1/formula) returns the original number in all cases, except for zero, when it returns an error (#/DIV0). the IFERROR mops that up.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[BoredWorker]

I understood.

It change the 0 amount to an error (#DIV/0!).

Thank you.