I am trying to work out a formula for excel I know what I want it to do just can't seem to figure out the correct way of putting it. I think it's an "if" formula.
I'm trying to create a daily planner for staff working from a rota so if the staff member is working it picks them up in the planner and if they're not it won't, also I have morning shifts and afternoon shifts so the formula would need to recognise the difference. Can anyone help?
Last edited by SeanBigger; 03-07-2016 at 07:05 AM.
Have a look on the Web as there are free Excel planners available.
Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.
To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
File now added
File now added
This ones better
More information needed:
Start/end times of your morning/afternoon shifts
Date on your Daily Planner PLUS Week number to determine sheet to extract data from.
Who are managers? Entries in rows 4 & 5 ?
Assume first table is AM and Second PM on Daily Planner
Are "breaks" fixed times?
Last edited by JohnTopley; 03-07-2016 at 07:25 AM.
....
Will there be more than one Daily planner e.g. one for each day of the week?
Or a week's planning in one sheet?
Looking more like a VBA (Macro) solution.
See attached as "prototype" VBA solution: logic needed for assigning to correct shifts as it currently puts everything in AM shift. I have modified some of the sheets.
Please Login or Register to view this content.
This is great and in normal circumstances would work brilliantly. Is there any way this can be done with a non VBA/Macro option as going to be using in google sheets and it wont accept macros or VBA.
See the attached: sheets "Any Week" and "Planner Week 1 (2)"
In "Any Week" I have taken the liberty of changing the layout and included a dropdown for "Position". There is a named range in Sheet2 called "Positions"
In "Planner Week 1 (2)":
Again slightly modified table:
in A6
=IFERROR(INDEX('Any Week'!$B$4:$B$52,SMALL(IF(INDEX('Any Week'!$H$4:$AB$52,,MATCH($B$3,'Any Week'!$I$2:$AB$2,0))>0,ROW($A$4:$A$50)-ROW($A$4)+1,""),ROWS($A$4:A4))),"")
Enter with Ctrl+Shift+Enter (array formula)
in B6
=IFERROR(VLOOKUP($A6,'Any Week'!$B$4:$C$50,2,0),"")
in C6
=IFERROR(VLOOKUP($A6,'Any Week'!$B$4:$AB$52,MATCH($B$3,'Any Week'!$B$2:$AB$2,0)-1,0),"")
in D6
=IFERROR(VLOOKUP($A6,'Any Week'!$B$4:$AB$52,MATCH($B$3,'Any Week'!$B$2:$AB$2,0),0),"")
Copy all formulas down the columns
This simplification hopefully will make it Google compatible.
This is excellent I'm now trying to create a 53 week rota with individual sheets for each week. Do I need a separate planner for each week or can there just be on planner for all. I have copied the two sheets into a separate workbook and am now having difficulty in getting it to work. please see attached
Hello again! I thought this had died!
See the attached which has the following changes:
A blank sheet called "Rota WK n": this a "dummy" and does not need to be completed.
A sheet called "Planner Template" which has all the formulae inserted.
For a new weekly plan:
Copy the "Planner Template" and rename as "Planner Wk n" where n is the week number. Change the Week in B5 and then do a "Find/Replace" of "Rota WK n" with "Rota Wk 5" if you are doing week 5. (Hence the need for the dummy "Rota Wk n" sheet to avoid Excel looking elsewhere for "Rota Wk n").
"Planner WK 2" has already been set up.
Re a yearly planner: yes that is possible but it will obviously need new formulae: and I haven't investigated what that involves.
Last edited by JohnTopley; 04-14-2016 at 09:14 AM.
The attached has "Planner Wk n" based on "Year Rota".
Each week is a block of 30 columns: there are two weeks in the sheet.
Change week number ( 1 or 2) and dates to get results.
In A5
=IFERROR(VLOOKUP($B5,OFFSET('Year Rota'!$B$5,,($B$1-1)*30,50,2),2,0),"")
in B5
=IFERROR(INDEX(OFFSET('Year Rota'!$B$5,0,($B$1-1)*30,50,1),SMALL(IF(INDEX(OFFSET('Year Rota'!$H$5,,($B$1-1)*30,50,22),,MATCH($B$2,OFFSET('Year Rota'!$I$3,0,($B$1-1)*30,1,21),0))>0,ROW($B$5:$B$50)-ROW($B$5)+1,""),ROWS($B$5:B5))),"")
in C5
=IFERROR(VLOOKUP($B5,OFFSET('Year Rota'!$B$5,,($B$1-1)*30,50,21),MATCH($B$2,OFFSET('Year Rota'!$B$3,,($B$1-1)*30,1,21),0)-1,FALSE),"")
in D5
=IFERROR(VLOOKUP($B5,OFFSET('Year Rota'!$B$5,,($B$1-1)*30,50,21),MATCH($B$2,OFFSET('Year Rota'!$B$3,,($B$1-1)*30,1,21),0),FALSE),"")
Thanks for all your help so far!!! So this still won't die!! I've got a template sheet of planner and real sheet of planner the template works fine but the real one will only show data for every other day when entering the date my eyes are going blurry from checking formulas to make sure theyre all correct which they are so i'm a little confused to say the least.
I attach the file I hope you can help to put this to bed once and for all!!
The problem was the inconsistent ranges in this formula:
=IFERROR(INDEX('Any Week'!$B$4:$B$52,SMALL(IF(INDEX('Any Week'!$H$4:$AB$52,,MATCH($B$2,'Any Week'!$I$2:$AB$2,0))>0,ROW($B$4:$B$52)-ROW($B$4)+1,""),ROWS($B4:B$4))),"")
The above is the correct one.
In you your formula the ROW ranges were $B$3:$B$38 / B3 / $B$3:B3
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks