Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    vlookup to pick dates

    Hi All,

    I am trying to use the vloop up function to get the dates if have one years dates suppose i need to get feb data how can i reterive in other sheet?


    Thanks
    farrukh

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Farrukh

    It is not clear what you want.
    A Vlookup formula will retrieve a single result.
    Do you want to extract all records that have a February date from one sheet to another?
    Give us an example and we can help.

    zeddy

  3. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Lightbulb

    Respected Zeddy ,

    Problem i have in one sheet i reterive the data from database suppose one year data always.
    in sheet jan,feb,march april and so to dec i have given the references of dates to each sheet suppose from reterived sheet range a1 to a31 refer to d8 to ah8 for jan sheet ,from a32 to a59 refer to d8 to ae8 to feb sheet and a60 to a 91 for march sheet d8 to ae8. now the problem is when i reterive the data of this year which contains 366 days the reference out on sheet march sheet due to including date at a60 date comes like 2/29/2012. This problem can be deal manaully but problem zeddy i have i need to run this template which contains yours made formula block . I am attaching the excel file which contains jan to dec sheets and "reterive DB" which is refering every first date of the month to jan to dec sheets if the year changes like in "Previous Year DB reterive" sheet and when i click to refresh data in jan sheet the 1st march data comes in feb sheet because of cell a61?

    Sorry if i cannot make cannot under stand well
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Farrukh

    See attached file.

    The answer is simple:
    For each of your Month sheets, don't define the first day of the month based on the record row of the imported data.
    Instead, all you need to really know is what year the imported data relates to.
    This will always be given by the entry in cell [A2] of your imported data.

    So, for each of your month sheets, we know what the month is, and what the year is (based on the first entry of the imported data). So we just put a simple formula along the date headers in row8 of each sheet.
    Apart from Feb, we always know how many days there are for each month.
    For Feb sheet, we just put an 'extra' formula in cell [AF8], which will show 29-Feb only for leap-years, and blank otherwise.

    So now your existing [Refresh data] will work properly.

    zeddy
    Attached Files Attached Files

  5. The Following User Says Thank You to zeddy For This Useful Post:

    farrukh (2012-03-10)

  6. #5
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Zeddy ,


    simple great how easy you solve the issues :-)


    One thing if you do not mind will you send me your picture i want to show all that he is my teacher?

    email : farrukhhameed786@gmail.com


    THANK YOU

    FARRUKH

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Farrukh

    Thank you for your kind words.
    There are many other persons helping on this website.
    We all learn from each other.
    I cannot send a picture as I don't wish to offend anyone.

    zeddy

  8. #7
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    yes you are right all are helping and kind to us many big names


    Sir steve , retired greek, martimM etc


    Thank you

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Farrukh,

    Zeddy ask me to correct your mis-understanding. I'm not Greek but a Geek {and proud of it} and Retired thus RetiredGeek.
    Thanks for including me in such lofty company.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Farrukh

    In a 2007 interview on The Colbert Report, Richard Clarke said the difference between nerds and geeks is "geeks get it done."[




Posting Permissions

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