I have 2 rows in my spreadsheet
A B C
Time Account Entitlement Remainder
1 Other Leave 11 xx
2 Annual Leave 15 xx
What I wants is that if I take a leave it will start deducting from Other Leave first until the "Other Leave" drops to zero then will it start deducting from the "Annual Leave"
Last edited by gloom52; 09-10-2009 at 04:05 PM.
A B C Time Account Entitlement Remainder 1 Other Leave 11 xx 2 Annual Leave 15 xxSounds good. Click GO ADVANCED and use the paperclip icon to post up your workbook. We can suggest something when we see where/how you want these calculations in regard to where you're "taking a leave".Originally Posted by gloom52
Make sure your sample workbook includes all the information about where you indicate leave.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
heres the attachment a bit messy but I've placed comments on the "Personal Data" and "Aug09" sheet so that u could explain the solution a bit more easily.
I've streamlined all the formulas in I21:I26 including the two you needed help with. I put a BUNCH of "Leave" entries in AUG09 so you'll want to fix those later.
Also, SEPT09 and NOV09 sheet tabs had extra spaces in the sheet name I had to remove, be careful of those.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
The formulas worked very well but now there seems to be a new problem..
If I removed all the Leaves, the "Yearly Entitled Leave" remainder becomes zero which should have been the same as the number under "Entitlement"
Erm forget this last post. I've managed to fixed the problem by changing the False section under the "IF" function to the entitled leave and it works well
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].
(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks