How to show start and end of data ranges?
Having trouble figuring out the correct formula to show the start and end of shifts in a staff rota?
Its probably really easy as it seems quite straightforward. I just cant figure it. DOH!!
I have attached a short template of the problem. Any help would be much appreciated.
Last edited by wildeone2010; 02-22-2010 at 08:47 PM. Reason: ITS SOLVED
Hi wildeone,
welcome to the forum.
In one row of the table, for example row 4, put a 6 in column B and in C4 enter
=b4+0.5
drag across to AK
now your start formula is
=INDEX($B$4:$AK$4,MATCH("CS",B9:AK9,0))
and your end formula
=INDEX($B$4:$AK$4,MATCH("CS",B9:AK9,1))+0.5
hth
Thanks, the formula works great...
just one last question, the data value won't always be CS, it could be numerous letters, how can i change the formula to check any value other than blank.
thanks again
Another update.
I have found by changing "cs" value to "*" the start time calculations adjust to recognize the first nonblank cell which is exactly what i require.
The problem is the end times dont adjust the same way. they keep giving an NA value.
I'm stuck.
Help would be appreciated. Have added the updated template.
use
=INDEX($B$4:$AK$4,MATCH("z",B9:AK9,1))+0.5
That will find the rightmost cell with any kind of text in it.
Re: How to show start and end of data ranges?
Thank you very much.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks