Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Formula based on Month (2007)

    I need you to assist me with a simple task. I have data in sheet BR1. I need to generate a journal in Sheet Journal based in the date in cell B1 in sheet Journal

    If the date in sheet “Journal” is October, then the data must be extracted from col B in sheet Br1

    I would like a macro to change this as I could have several sheet eg br1, br2, br3 etc where a journal of generated and need the col no. changed based on the data for eg if the formula in the journal is ='BR1'!D10 (this is dec) if it changes to Jan then formula will read 'BR1'!E10


    Your assistance will be most appreciated

    Regards

    Howard
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula based on Month (2007)

    Cell B1 on the Journal sheet merely contains a month name such as "October".
    Row 4 on the BR1 sheet contains date values such as 01-Oct-2008. How do we know which year to use?

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Formula based on Month (2007)

    Hi Hans

    The formula must be based on the month only

    Regards

    Howard

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula based on Month (2007)

    Assuming that you'll never have two columns for the same month in different years, you could put the following array formula in C1 on the Journal sheet (confirm with Ctrl+Shift+Enter):

    =MATCH(B1,TEXT('BR1'!4:4,"mmmm"),0)

    The formula in C5 then becomes

    =OFFSET('BR1'!A10,0,Journal!$C$1-1)

    When you select a different month in B1, the value in C5 will be adjusted automatically.

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Formula based on Month (2007)

    Hi Hans

    Thanks for the help.

    Code is exactly what is required to do the job

    Regards

    Howard

Posting Permissions

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