Hi I have a sheet that shows employee abcences, I require a formula that will look to see if a friday and a monday are populated and auto-populate the saturday and sunday to match the run, if the only the monday is populated then the saturday and sunday need to stay blank, if only the friday is populated then I also need the saturday and sunday to stay blank.
I have attached a sheet with the current display and the desired outcome, any help will be appreciated.
Cheers
J
Does =IF(C6=C9,C6,"") in C7 not do it?
Hope this helps
Sometimes its best to start at the beginning and learn VBA & Excel.
Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
Available for remote consultancy work PM me
I think John wants the solution to be integrated into his current formula.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi Ali, if possible yes please if not then Im open to helper columns.
JD
John - it's actually quite difficult to offer a tested solution without the data to which your current formula refers. Can you not provide a more realistic version of your data?
The sheet may be too big to post.
JD
Yes, thats fine, so in the bit that checks for sat and sunday returning "", you could say if saturday and offset(2,0)=the same as offset(-1,0), then put the name in, and if sunday, then if offset(1,0) is the same as offset(-2,0) then put the name in, otherwise ""
It is only the true condition for Saturday and Sunday that needs altering.
Please Login or Register to view this content.
Not that tricky.
Last edited by nathansav; 05-30-2014 at 05:15 AM.
It doesn't need to be the entire workbook, but it does need to have a working formula that we can play with. Otherwise anyone helping has to recreate the data, and that would be very time-consuming.
Ali, there is no need to see the data for the Saturday Sunday change is there????
Hi Nathan Sav i have tried your formula but it just keeps coming up with you have entered too few arguments.
JD
I havent test it, basically you need to split your or
here it is, example cell C7
=IF(B7="Saturday",IF(OFFSET(C7,-1,0)=OFFSET(C7,2,0),OFFSET(C7,-1,0),IF(B7="Sunday",IF(OFFSET(C7,-2,0)=OFFSET(C7,1,0),OFFSET(C7,-2,0),"REST OF YOUR FORMULA"),"")))
You could even trust saturdays logic and say if on sunday if the cell above is not blank use the cell above
Last edited by nathansav; 05-30-2014 at 05:40 AM.
Hi Guys will try and post a stripped down version of the sheet to let you see where the prblems are occuring.
JD
With a complex formula such as John's things can and do go wrong when trying to implement a suggested solution. Looks like this is precisely what has happened. As soon as you get the updated workbook, you'll be able to sort it out.Originally Posted by nathansav
Hi Guys, here is slightly stripped down version, the user enters the holidays on the booking sheet, I have booked a full years worth to help with the saturday and sunday problem, each month on the calc engine has the date repeated at the end to enable flexiblility for the FYE start and end dates, eveything works ok with the sat and sunday as the exceptions.
Hope this helps
JD
Last edited by john dalton; 05-30-2014 at 07:48 AM.
If you can find an easier way to do any of this im open to suggestions.
J
it will be
=IF(I11="Saturday",IF(OFFSET(J11,-1,0)=OFFSET(J11,2,0),OFFSET(J11,-1,0),""),IF(I11="Sunday",IF(OFFSET(J11,-2,0)=OFFSET(J11,1,0),OFFSET(J11,-2,0),""),"YOUR FORMULA"))
You can put your formula for the extraction of leave etc minus the saturday sunday returning "" in the space where it says YOUR FORMULA
=IF(I11="Saturday",IF(OFFSET(J11,-1,0)=OFFSET(J11,2,0),OFFSET(J11,-1,0),""),IF(I11="Sunday",IF(OFFSET(J11,-2,0)=OFFSET(J11,1,0),OFFSET(J11,-2,0),""),IFERROR(INDEX('Booking Sheet'!$M$3:$M$15,SMALL(IF((($H11)>='Booking Sheet'!$F$3:$F$15)*(($H11)<='Booking Sheet'!$G$3:$G$15)*(($J$9)='Booking Sheet'!$E$3:$E$15),ROW('Booking Sheet'!$F$3:$F$15)-MIN(ROW('Booking Sheet'!$G$3:$G$15))+1,""),1)),"")))
Bold is the solution back at 10 o'clock this morning, let me know if im not getting owt :o)
FORUM_D16.xlsx
Hi,
Is this what you are getting at? In column K?
Hi Nathan, great solution works just as it should, thank you for your help with this as it was a real sticking point to getting this sheet finished.
Cheers again
JD
No worries, JD.
Hi Nathan here is a sheet with the current outcome and the desired outcome, if the friday is booked but the monday is not the saturday and sunday need to be blank even if they are booked on the booking sheet.
Cheers
JD
Hi Nathan, here is the test sheet with the desired outcome.
Thnaks
JD
FORUM_D16.xlsxHI
Please see attached
Hi Nathan, here you go. The Explanation is at the top of the Calculations Engine Sheet, any queries don't hesitate to ask.
Cheers
JD
Have a look at this Nathan.
Cheers
JD
Like this if possible please.
JD
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks