I am using Windows Office, MS Excel 2016.
Please refer the attached excel file.
Daily Closing balance data is entered manually in Sheet CBal
Closing balance of Previous working day will be the opening balance
When a date is entered in D3 ( in OpeningBal Sheet) it should fetch the Closing balance of Previous working day Lot & Item wise
This, copied down:
Formula:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Thanks for the prompt reply.
This formula is working to get only the previous day (one day before).
I am looking for the value of the "previous working" day.
example D3= 05/04/25 or 06/04/25 it should fetch data of 03/04/25
Or any monday date..it should fetch data of previous working date i.e. of Saturday
Hope, i am able to explain the requirement
Last edited by AliGW; 04-06-2025 at 03:16 AM. Reason: Please don't quote unnecessarily - use the Quick Reply button instead. Please review the forum guidelines.
You failed to define 'working day' in your opening post, so we were left to guess.
So by 'working day', do you mean Monday to Saturday inclusive and NOT Sunday?
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Try this:
=INDEX(CBal!$D$5:$BM$12,MATCH($B5,$B$5:$B$12,0),MATCH(WORKDAY.INTL($D$3,-1,11),CBal!$D$3:$BM$3,0)+1)
Given that 5/6 April are Sat/Sun ,with Sun assumed to be non-working? - then expected previous working would be Fri (4th).
Looking at you data suggests that the "previous" day is the one for which there is a balance (non-blank cell), which happens to be 3rd April.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
1) Yes, I failed to define 'working day' in my opening post. Sorry for that.
2) I am actually looking for getting the data of last/previous working day from D3 ( in OpeningBal sheet), where data >0 (where data is available or not blank).
example
If D3= 05/04/25 or 06/04/25 it should fetch data of 03/04/25
or
If D3= 07/04/25 it should fetch data of 05/04/25 ( since 6th has no value & blank)
Or any monday ..it should fetch data of previous working date i.e. of Saturday or Friday
Please refer the new attachment (OpnBal_modi)
3) I have tried,
=INDEX(CBal!$D$5:$BM$12,MATCH($B5,$B$5:$B$12,0),MATCH(WORKDAY.INTL($D$3,-1,11),CBal!$D$3:$BM$3,0)+1)
but values are not fetching.
I have tried, but values are not fetching.
Request to relook & help me please
So you've now added ANOTHER criterion! Please remember that we are not clairvoyant and cannot create solutions for things that you 'forgot' (or simply omitted) to tell us.
And which column "A" or "B" ?
A guess:
=INDEX(CBal!$D$5:$BM$12,MATCH($B5,$B$5:$B$12,0),MATCH(1,(CBal!$D$3:$BM$3<=WORKDAY.INTL($D$3,-1,11))*(CBal!$D$5:$BM$5<>""),0))
Enter with CTRL+SHIFT+ENTER and then copy down.
Ideally column B
Did you try what I suggested in post #11?
Try adding this:
=INDEX(CBal!$D$5:$BM$12,MATCH($B5,$B$5:$B$12,0),MATCH(1,(CBal!$D$3:$BM$3<=WORKDAY.INTL($D$3,-1,11))*(CBal!$D$5:$BM$5<>"")*(CBal!$D$4:$BM$4="B"),0))
tried. not coming
And not even a "thank you". No worries - over to you.
Please note that there is an error in my original formula. It does work with the sample file as the data is in the same order on both sheets but it may not work correctly with live data. I don't think it affects any of the enhancements although, again, only as they relate to the sample file.
This:
Formula:Please Login or Register to view this content.
Should be:
Formula:Please Login or Register to view this content.
tried. ( post#13) :
=INDEX(CBal!$D$5:$BM$12,MATCH($B5,$B$5:$B$12,0),MATCH(1,(CBal!$D$3:$BM$3<=WORKDAY.INTL($D$3,-1,11))*(CBal!$D$5:$BM$5<>"")*(CBal!$D$4:$BM$4="B"),0))
tried. ( post#17)
=INDEX(CBal!$D$5:$BM$12,MATCH($B5,$B$5:$B$12,0),MATCH(1,(CBal!$D$3:$BM$3<=WORKDAY.INTL($D$3,-1,11))*(CBal!$D$5:$BM$5<>"")*(CBal!$D$4:$BM$4="B"),0))
not working.
I am afraid, whether i am making some mistake in putting the formula
Change your filein this way
C5=SUMPRODUCT((CBal!$D$3:$BM$3=AGGREGATE(14,6,CBal!$D$3:$BM$3/(CBal!$D$5:$BN$13>0)/(CBal!$D$3:$BM$3<=OpeningBal!$D$3),1))*(CBal!$A$5:$A$12=OpeningBal!A5)*(CBal!$B$5:$B$12=OpeningBal!B5)*(CBal!$D$5:$BM$12))
Copy down
Thaankzzzz............
It is working fine. How ever tomorrow I will test in actual working sheet to confirm fully.
AGGREGATE is powerful function and I need to learn it
Last edited by AliGW; 04-06-2025 at 07:30 AM. Reason: Please don't quote unnecessarily - use the Quick Reply button instead. Please review the forum guidelines.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Marked as solved. & reputation added
You're welcome. Thanks for the rep.
Glad you got a working solution
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks