Hi all,
I'm trying to copy certain data from one sheet to another sheet meeting a condition. sample sheet attached.
Leave data is list the list of leave taken by the employees. Meeting the Employee No as condition, when entered in 'Leave card' sheet, i want to copy the data automatically in the highlighted cell. this will help me to get the complete leave details of an employee in a prescribed format. i not used with VBA or macros, so want to get it done using formulas.
Kindly help.
I see that you have responded on another thread to which I contributed, and here's another one you can look at from yesterday:
http://www.excelforum.com/excel-form...xed-value.html
Set up a drop-down in cell D12 of your leave card sheet so that you can choose the employee number, and then use a helper column (H) in both sheets similar to the example I've just given.
Hope this helps.
Pete
Hi pete,
This is not what i'm exactly looking for. kindly check the sample sheet which i attached in the thread. help me the workings that could suit to my requirement.
Divya...
Another way
ARRAY formula in A20 and copy down. Change the INDEX reference for the others.
=INDEX('Leave data'!D$2:$D22,SMALL(IF('Leave data'!$B$2:$B$22=$D$12,ROW('Leave data'!D$2:D$22)-1),ROW('Leave data'!D1)))
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Please confirm what is your exact excel version is it 2003? or may be 2007?
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
its version 2003
Please refer the attached file for details... The formula is something same like Fotis1991 suggestion... just added some few more workouts.
Fotis, could you explain me abt the index fuction function used here. it works perfectly bt i need to understand the function to change its reference. this would help to understand better
@ sixthsense.
this really works well and that is exactly what I want do with. Appreciating your help. But if could explain me the workings, that wud be even helpful for my understanding, as i'm a beginner in using excel fucntions
=INDEX('Leave data'!D$2:$D22,SMALL(IF('Leave data'!$B$2:$B$22=$D$12,ROW('Leave data'!D$2:D$22)-1),ROW('Leave data'!D1)))
The small if part of the formula is the part that "tells" to the formula which is the condition. So the condition is in
'Leave data'!$B$2:$B$22, looks for ids that are equal to D12.
The INDEX part, the row Part give the column that formula must search to find the expected results. The -1 part of the formula shows in which row formula must start searching. It's -1 bexause your data start in row 2. If your data start in row 10..this part should be -9 ! Finally the D1 part, which when you copy down becomes D2 ,D3 etc...gives the first instance of maching data the second the third etc...
Hope that be clear
Glad it helps you and thanks for the feedback
Please refer Post #6 of the below thread for details explanation
http://www.excelforum.com/excel-form...-workbook.html
Edit: Fotis1991 already posted the detailed explanation in Post #10
Guys, thanks a ton !
Guys, to add up with this. Could you help to get the same for specific period.
Say i have the leave data of the employees from the day the joined. now how to change the same workings so that i could the result for specific period like leave availed report for the period 1st May to 31st May
Please keep two cells (i.e. for Start Date and End Date) and confirm us which will be that cells?
D9 would be the Start date and F9 would be the end date
=INDEX('Leave data'!D$2:$D22,SMALL(IF(('Leave data'!$B$2:$B$22=$D$12)*('Leave data'!$D$2:$D$22>=$d$9)*('Leave data'!$D$2:$D$22<=$f$9),ROW('Leave data'!D$2:D$22)-1),ROW('Leave data'!D1)))
is that rite?
IF(ISERR(SMALL(IF('Leave data'!$B$2:$B$22=$D$12,ROW('Leave data'!$B$2:$B$22)),ROW($A1))),"",INDEX('Leave data'!$D$2:$D$22,SMALL(IF(('Leave data'!$B$2:$B$22=$D$12)*('Leave data'!$D$2:$D$22>=$D$9)*('Leave data'!$D$2:$D$22<=$F$9),ROW('Leave data'!$B$2:$B$22)-ROW('Leave data'!$B$2)+1),ROW($A1))))
No....This part should come inside the Iserr(Highlited Text) function
=INDEX('Leave data'!D$2:$D22,SMALL(IF(('Leave data'!$B$2:$B$22=$D$12)*('Leave data'!$D$2:$D$22>=$d$9)*('Leave data'!$D$2:$D$22<=$f$9),ROW('Leave data'!D$2:D$22)-1),ROW('Leave data'!D1)))
the workings which i got from sixthsense suits me better, check the attachment in his previous reply. Help to add up the date criteria into that
Fotis1991 suggestion will work fine too... you just made a mistake when you try to add the error handler. Please refer Post #19 to know how to add error handler in Fotis1991 suggested formula
=IF(ISERROR(INDEX('Leave data'!D$2:$D22;SMALL(IF(('Leave data'!$B$2:$B$22=$D$12)*('Leave data'!$D$2:$D$22>=$D$9)*('Leave data'!$D$2:$D$22<=$F$9);ROW('Leave data'!D$2:D$22)-1);ROW('Leave data'!D1))));"";INDEX('Leave data'!D$2:$D22;SMALL(IF(('Leave data'!$B$2:$B$22=$D$12)*('Leave data'!$D$2:$D$22>=$D$9)*('Leave data'!$D$2:$D$22<=$F$9);ROW('Leave data'!D$2:D$22)-1);ROW('Leave data'!D1))))
IF(ISERR(SMALL(IF(('Leave data'!$B$2:$B$23=$D$12)*('Leave data'!$D$2:$D$23>=$D$9)*('Leave data'!$D$2:$D$23<=$F$9),ROW('Leave data'!$B$2:$B$23)),ROW($A1))),"",INDEX('Leave data'!$D$2:$D$23,SMALL(IF(('Leave data'!$B$2:$B$23=$D$12)*('Leave data'!$D$2:$D$23>=$D$9)*('Leave data'!$D$2:$D$23<=$F$9),ROW('Leave data'!$B$2:$B$23)-ROW('Leave data'!$B$2)+1),ROW($A1))))
is that correct now????
Seems to be perfect now....Originally Posted by divi123
Guys, i got this done... thanks...
just asking whethr its possible to hide this formula or to make this smaller.. bcoz the master sheet which i'm working seems to be cozy
Suggestions:
1) Helper columns using regular formulas(not ARRAY)(See Pete's suggestion-Post#2)
2)VBA
And
If possible upgrading to higher (latest) version after 2003 will help you to use
Iferror(YourFormulaHere,"TextToBeShownInCaseOfError")
will reduce the formula length considerably
As i'm a beginner i was not able to understand the workings suggested by Pete and in turn i'm not used with VBA too... the working which you suggested is much better, only thing is to lock the all cells that has the formulas except D12 in which the value is to be entered. so that the formulas will not be changed or disturbed
One way.
Choose D12. >>right click>>format cell>>protection>>uncheck the lock option.
Then protect the whole sheet..
Thanks guys........will catch u soon with my next query.........
Hi , again too add up more with this...
now I have leave credit data similar like leave data.. as we have done with the leave data, i want the leave credit details to be added in the Leave card. attached sheet for your reference, kindly help to generate Leave card when employee number is entered
Please refer the attached file for solution...
I am not sure what is your expected output in Leave Applied Area
it is as simple as that, i want to get the datas from both the sheets i.e) from the sheets 'Leave data' and 'Leave credit' into 'Leave card' when the employee number is entered
Am i making it clear for U?
No it is not the right method, I believe. Because you are retrieving the dates from Leave Credit and showing the Leave Availed which is arrived from Leave Data will be treated as these leaves are availed on the dates which is showing in Column-A.
I hope you understand the situation...
it is not that way.. pls look into the attached file for the format
firstly meeting the condition of employee no. i want to to pull up data from Leave data and leave credit sheets. in the prescribed format.
Thanks for the explanation, please refer the attached file for exact solution
PS: But it is seems to be a hectic method
is there any other solution?
Just want to have the entire leave availed and leave credit report of employee, when his employee number is entered.. and meeting the condition of given date
think i'm bothering you so much... but highly in need of your help
From my point of view Pivot Table is the best way based on your data....
But at present I am having some urgent job needs to be finished if you don't mind please wait so that I can show how it can be done easily. But I need atleast 4 to 5 hours to finish my currently assigned job
hmmmm will wait..... and will come back after 5hrs..........
This is my level best code to mask the formula with UDF...
Please Login or Register to view this content.Formula:
Please Login or Register to view this content.
Refer the B5 cell for details and the formula cells for details...
Refer the attached file for details.
I got this evaluation method with the help of the below thread
http://www.excelforum.com/excel-prog...la-in-vba.html
Heart felt thanks for the interest and efforts you have taken to solve my query. This formula really seems better and easy to understand but when i write up in the actual sheet i'm getting #Name? error.. pls help..
Thanks for your feedback and request you to attach a sample file without any confidential data for better understanding and testing purpose.
Have you copied the given code in a standard module of your workbook?
pls find the attachment
whats that mean by standard module?
My Post #45 assumption is right... you have not copied the provided code in your file
Refer the below link to know how to add code in regular module
http://www.contextures.com/xlvba01.html
Refer the attached file in which I have included the UDF code.
thanks its done now.. also help me how to get the report displayed by ascending the dates irrespective of the data. report format attached
Don't know what needs to be shown in Ascending Order
think i'm confusing... after getting the data from both sheets into leave card, i want the data (date) to be sorted in ascending order automatically
did i made it clear?
I believe the data shown in the same way in post # 49 of the screenshot
the screen shot posted in post #49, is the sample and its done manualy...i want things to be sorted in that way whereas my results are
15-Apr-13 Leave Credit - 1st Month
15-May13 Leave Credit - 2nd Month
29-Apr-13 CL
30-Apr-13 Loss of Pay
13-May-13 Loss of Pay
want the final report to be sorted by date
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks