:confused: Auto pick "specific data" and put it in "specific cells" with date

Greetings to everyone

i m stuck into some issue

i hav 2 month & year from ("AE2" to "AI2") & ("AE7" to "AI7")

in front of month Jan with Year 2014 (B3 & B4) i have Dyas from "D3 to AH3" and Dates "D4 to AH4" and from "D5 to AH5" it is filled with H/L, C/L, P, M/L etc. all these ("D3 to AH3, "D4 to AH4" & "D5 to AH5") are in horizontal format and same with case of month Feb with Year 2014 ("AE7" to "AI7").

the problem i m facing i want these all horizontal format data (AE2 to AI2 & D3 to AH3 & D4 to AH4 & D5 to AH5) in vertical format

I want cell "B19 to B23" to pick automatically "M/L" from "D5 to AH5" date wise ("dd-mm-yy" ) & C/L in cell "C19:C23", H/L in cell "D1923"

as you can see in sample attach copy the cell "S3" (day) and "S4" (date) should display in cell "A19" with "dd-mm-yy" according to ("AE2" to "AI2")

Leaves Records2.xlsx


Attendance.jpg

this will help me out to maintain Full year record of (M/L C/L, H/L) taken of specific Person date wise

Best regards

Originally Posted by oeldere

My advice is:

Don't use 2 file's for this kind of work.

Just use an input file and make the reports from the data of this file.

Having said that I would make the inputfile verticaly, so you get (easily) access to the advances of a pivot table.

your kind advice is highly appreciating and i respect your advice

but things are not in my hand as i have to perform according to rules of company & the only reason i need this thing is bcz i need to see per person total (M/L C/L, H/L) with date, so that i can see an overall how many (M/L C/L, H/L) has been availed and left without going other monthly sheets one by one to check how many (M/L C/L, H/L) person "A" has availed

hope u getting it

best Regards

Originally Posted by oeldere

If there are better solution (for the company), which also earns money, the company will be open for the solutions.

You can contact your boss and discuss it with him / her.

i have attach a Sample file i which "some one" has done same thing which i required but it is bit different from my problem. i m not good in Formula thats why i cant figure it out how it will work

in attach file you see 2 sheets called "Area" and "Area Record" if you put the names in "Area Record" sheet of a particular person of "Area" sheet it will auto pic auto update it according to "Area" sheet


Area.xls

can you help it and modify the formula use in it for my solution, you help will be highly appreciated

Bump No Solution from anyone

See attached file.

Originally Posted by boopathiraja

Hi, Check it out the attachment

thanks a lot for the reply and help

superbly done really appreciating. this what i want at some instant

just want little bit changes

1. is it possible that "A19" only display those dates who got the value of "H/L, M/L, C/L". for example in month of "Jan" if their is 1 "M/L" or 3 "H/L" then in "A19" only display these (1 "M/L" or 3 "H/L") with there dates in "A19"

2. How i can make the same formula work on "Feb" month and there "H/L, M/L, C/L"

3. if i add another month suppose "March" then how i make the formula work on month of "March" and also other remaining month and there "H/L, M/L, C/L" in future

Best Regards

See attached file with changed formula.

Originally Posted by kvsrinivasamurthy

See attached file with changed formula.

I have not followed your Explanation clearly, regarding hidden cells. Upload file with all conditions and showing what results are required.

:o this is so brilliant bundle of thanks, Superb

just a bit issues

I have Upload file with all conditions and showing what results are required. this is my Original Sheet.

Attachment 341300

1. can you plz apply your formula on this new Attached excel sheet, this sheet contain month from (Jan-Dec)
1. Cells "D5 to M5" are not working. i mean to say "M/L, H/L, C/L" is not been picked can u plz fix it
2. "regarding hidden cells" i have unhide the cells. now you will see full year from Jan-Dec in my excel file


can you plz update & help me out in it.. i will be very thanks-full and appreciating

High Regards

sorry for the double post... internet speed issue

Pl see attached file.

Almost all month from (Jan - Dec) 23rd date (M/L, C/L, H/L, SB, L) is not been picked by "A72" cell

and

cell from "D5 to M5" both Dates & (M/L, C/L, H/L, SB, L) are also is not been picked by "A72" cell

High Regards

no "P" should not be picked up

plz see the below attach file i hav mention the errors in it

Array Leaves Issues.xlsx

Best Regards

Seee attached file.
I have changed for feb . Do same for all months.

Originally Posted by kvsrinivasamurthy

Seee attached file.
I have changed for feb . Do same for all months.

Same issue of Post#22, i have attach the image file of the issues kindly check

Date & Empty cells errors.jpg

the following date (23 & 24 Jan) format is incorect
23-Jan-00
24-Jan-00

the following date are ok but (M/L, C/L, H/L, SB, L, Comp/L) are showing blank cells, plz see the attach image
23-Dec-13
23-Feb-14
23-Mar-14
23-Apr-14
23-May-14
23-Jun-14
23-Jul-14
23-Aug-14
23-Sep-14
23-Oct-14
23-Nov-14

Regards

Pl see attached file.

Originally Posted by kvsrinivasamurthy

Pl see attached file.

I want to thank you publicly for your hard work and dedication. If you could see my face right now, you'd see a look of feeling of thankfulness and appreciation. Your support has made me a HAPPY person and I will forever be grateful. Your service has been of incomparable value and I wanted to let you know that thousands of miles away from you, I’ll forever be grateful, I appreciate your critical thinking around this project.

Thank you from the bottom of my heart for your commitment. You really a valuable Contributor & Forum Expert of this excelforum.com

If i need any further assistant and Help or updation, can i contact you in future

High Regards

Thanks for the compliments. I am happy that my knowledge has become useful to some extent. You can contact me for further necessaties.
Thanking you once again
KVS