+ Reply to Thread
Results 1 to 8 of 8

Macro Instead of Formula

  1. #1
    Registered User
    Join Date
    12-04-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    91

    Macro Instead of Formula

    Friends,

    I have an excel sheet for employee attendance. Now I am using it for monthly base. But I would like to make it for an year. I am using the following formula to extract data from a worksheet. But this formula is capable only for a month. Can you please advice me a Macro or substitute formulas which can use for the whole Year.

    =CONCATENATE(IF(Sheet1!$F$7=TODAY(),Sheet1!F8,IF(Sheet1!$G$7=TODAY(),Sheet1!G8,IF(Sheet1!$H$7=TODAY(),Sheet1!H8,IF(Sheet1!$I$7=TODAY(),Sheet1!I8,IF(Sheet1!$J$7=TODAY(),Sheet1!J8,IF(Sheet1!$K$7=TODAY(),Sheet1!K8,IF(Sheet1!$L$7=TODAY(),Sheet1!L8,IF(Sheet1!$M$7=TODAY(),Sheet1!M8,IF(Sheet1!$N$7=TODAY(),Sheet1!N8,IF(Sheet1!$O$7=TODAY(),Sheet1!O8,IF(Sheet1!$P$7=TODAY(),Sheet1!P8,IF(Sheet1!$Q$7=TODAY(),Sheet1!Q8,IF(Sheet1!$R$7=TODAY(),Sheet1!R8,IF(Sheet1!$S$7=TODAY(),Sheet1!S8,IF(Sheet1!$T$7=TODAY(),Sheet1!T8,IF(Sheet1!$U$7=TODAY(),Sheet1!U8,IF(Sheet1!$V$7=TODAY(),Sheet1!V8,IF(Sheet1!$W$7=TODAY(),Sheet1!W8,IF(Sheet1!$X$7=TODAY(),Sheet1!X8,IF(Sheet1!$Y$7=TODAY(),Sheet1!Y8,IF(Sheet1!$Z$7=TODAY(),Sheet1!Z8,IF(Sheet1!$AA$7=TODAY(),Sheet1!AA8,IF(Sheet1!$AB$7=TODAY(),Sheet1!AB8,IF(Sheet1!$AC$7=TODAY(),Sheet1!AC8,IF(Sheet1!$AD$7=TODAY(),Sheet1!AD8,IF(Sheet1!$AE$7=TODAY(),Sheet1!AE8,IF(Sheet1!$AF$7=TODAY(),Sheet1!AF8,IF(Sheet1!$AG$7=TODAY(),Sheet1!AG8,IF(Sheet1!$AH$7=TODAY(),Sheet1!AH8,IF(Sheet1!$AI$7=TODAY(),Sheet1!AI8,IF(Sheet1!$AJ$7=TODAY(),Sheet1!AJ8))))))))))))))))))))))))))))))))

    Now,So this formula will search which is TODAY's date on 31st columns based on monthly dates which has already entered in the sheet. And will show the result accordingly.
    I have 500 employees and I copied this formula in to 500 cells (ie From B4 to B500). now what I need is I have to make this for an YEAR. friends kindly suggest me a solution this..please

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Macro Instead of Formula

    can you attach a small sample file for testing ?
    If solved remember to mark Thread as solved

  3. #3
    Registered User
    Join Date
    12-04-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    91

    Re: Macro Instead of Formula

    Sir, The file contains a lot of official datas. So i am extremely sorry that I cannot upload that file.

    Sir, I think HLOOKUP will help me. But I dont know how to use that.

    What I need is I have entered the whole years date from Sheet1 F7: AJ7. I need the Data from 8th row of the Current date's column in Sheet 2. Also the row number has to be increased when I am copying or dragging that to each cell. Kindly help
    =HLOOKUP(TODAY(),F7:AJ500,2,TRUE)
    This one helps, but it is not giving the result when I am dragging down.

  4. #4
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: Macro Instead of Formula

    Sir, The file contains a lot of official datas. So i am extremely sorry that I cannot upload that file.
    So.. to help YOUR cause further... perhaps create a sample Workbook.. illustrating your issue with dummy data...

    That will save the people who could potentially help you the time to do that (for you) and instead concentrate on a solution for you..

  5. #5
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Macro Instead of Formula

    Quote Originally Posted by aneshdas View Post
    Sir, The file contains a lot of official datas. So i am extremely sorry that I cannot upload that file.
    small sample file = 5 employees with names aaa bbb ccc

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Macro Instead of Formula

    try
    =HLOOKUP(TODAY(),$F$7:$AJ$500,rows($a$1:a1),false)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  7. #7
    Registered User
    Join Date
    12-04-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    91

    Re: Macro Instead of Formula

    Thanks a lot @martindwilson sir. This works!!! but i did a modification like this
    =HLOOKUP(TODAY(),$F$7:$AJ$500,row(A1)) is there any problem with this sir?

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Macro Instead of Formula

    yep why did you change to row? row(A1) it will work but i just wondered
    and =HLOOKUP(TODAY(),$F$7:$AJ$500,row(A1)) why did you remove FALSE from the end? you need FALSE for an exact match
    unless your data is sorted ascending in f7:aj7(which it maybe),you might get odd results

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Macro: Plugging an Array-Formula into a Cell and Dragging Formula Down Columns
    By Brianandstewie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-20-2013, 02:57 PM
  2. Replies: 1
    Last Post: 01-27-2013, 11:05 AM
  3. Macro: Applying formula to multiple cells in excel (formula editing)
    By city in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-27-2012, 06:41 AM
  4. Formula/macro - Apply certain formula as per duplicate cells in column L
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-10-2011, 12:28 AM
  5. Replies: 1
    Last Post: 07-20-2006, 03:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1