# Require a formula to look at previous and following days to see if they are populated

1. ## Require a formula to look at previous and following days to see if they are populated

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

2. ## Re: Require a formula to look at previous and following days to see if they are populated

Does =IF(C6=C9,C6,"") in C7 not do it?

3. ## Re: Require a formula to look at previous and following days to see if they are populated

I think John wants the solution to be integrated into his current formula.

4. ## Re: Require a formula to look at previous and following days to see if they are populated

Hi Ali, if possible yes please if not then Im open to helper columns.

JD

5. ## Re: Require a formula to look at previous and following days to see if they are populated

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?

6. ## Re: Require a formula to look at previous and following days to see if they are populated

The sheet may be too big to post.

JD

7. ## Re: Require a formula to look at previous and following days to see if they are populated

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 ""

``Please Login or Register  to view this content.``
It is only the true condition for Saturday and Sunday that needs altering.

Not that tricky.

8. ## Re: Require a formula to look at previous and following days to see if they are populated

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.

9. ## Re: Require a formula to look at previous and following days to see if they are populated

Ali, there is no need to see the data for the Saturday Sunday change is there????

10. ## Re: Require a formula to look at previous and following days to see if they are populated

Hi Nathan Sav i have tried your formula but it just keeps coming up with you have entered too few arguments.

JD

11. ## Re: Require a formula to look at previous and following days to see if they are populated

I havent test it, basically you need to split your or

here it is, example cell C7

You could even trust saturdays logic and say if on sunday if the cell above is not blank use the cell above

12. ## Re: Require a formula to look at previous and following days to see if they are populated

Hi Guys will try and post a stripped down version of the sheet to let you see where the prblems are occuring.

JD

13. ## Re: Require a formula to look at previous and following days to see if they are populated

Originally Posted by nathansav
Ali, there is no need to see the data for the Saturday Sunday change is there????
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.

14. ## Re: Require a formula to look at previous and following days to see if they are populated

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

15. ## Re: Require a formula to look at previous and following days to see if they are populated

If you can find an easier way to do any of this im open to suggestions.

J

16. ## Re: Require a formula to look at previous and following days to see if they are populated

it will be

You can put your formula for the extraction of leave etc minus the saturday sunday returning "" in the space where it says YOUR FORMULA

17. ## Re: Require a formula to look at previous and following days to see if they are populated

=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)

18. ## Re: Require a formula to look at previous and following days to see if they are populated

FORUM_D16.xlsx

Hi,

Is this what you are getting at? In column K?

19. ## Re: Require a formula to look at previous and following days to see if they are populated

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

20. ## Re: Require a formula to look at previous and following days to see if they are populated

No worries, JD.

21. ## Re: Require a formula to look at previous and following days to see if they are populated

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

22. ## Re: Require a formula to look at previous and following days to see if they are populated

Hi Nathan, here is the test sheet with the desired outcome.

Thnaks

JD

23. ## Re: Require a formula to look at previous and following days to see if they are populated

FORUM_D16.xlsxHI

24. ## Re: Require a formula to look at previous and following days to see if they are populated

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

25. ## Re: Require a formula to look at previous and following days to see if they are populated

Have a look at this Nathan.

Cheers

JD

26. ## Re: Require a formula to look at previous and following days to see if they are populated

JD

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1