Hi,
I use a rota spreadsheet I use to keep track of holidays and sickness.
One cell has a full list of the days holiday a person has booked, represented as
20-Jun,21-Jun,22-Jun,23-Jun,24-Jun etc..
I have a formula which looks at this field and splits the days at the comma and then lists them in individual rows. So i get a list of days instead of all in one cell.
This works fine if the number of booked days is 33 or below. If it goes to 34 I get a #VALUE error.
=TRIM(MID(SUBSTITUTE($L$9,",",REPT(" ",999)),(ROW(1:1)-1)*999+1,999))
L9 is the cell containing the list of days.
I'm assuming it might be that i've reached the maximum number of data for that cell but not sure how to fix.
Any help is appreciated!
thanks.
Bookmarks