• Excel Formula (2000)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Excel Formula (2000)

    Author
    Topic
    WSjrw5180
    AskWoody Lounger
    May 14, 2004 at 7:18 pm #404941

    Hi!! Want the formula to search multiple tabs and ranges. I need it to look for a certain name in one column but on every tab and then check a range of dates. If the dates are within x range, then input value associated with the line the value and name match up with. When you look at the attachment, look to the reorder tab. On the reorder tab, under each month, I would like it to fill in the retail amount by “book”(listed in column A reorder tab). So, the formula would need to look on all tabs for the specific book and then also make sure it only fills in the retail if the dates fall within the specific month. Hope this makes sense! Thanks!

    Reply | Quote
    Viewing 2 reply threads
    Author
    Replies
    • WSAndrewO
      AskWoody Lounger
      May 14, 2004 at 9:33 pm #827464

      What reorder tab? – sorry – I’m struggling to understand the question – especially as most of your data tabs were empty

      Reply | Quote
    • WSAndrewO
      AskWoody Lounger
      May 14, 2004 at 9:33 pm #827465

      What reorder tab? – sorry – I’m struggling to understand the question – especially as most of your data tabs were empty

      Reply | Quote
    • WSsdckapr
      AskWoody Lounger
      May 15, 2004 at 1:33 am #827573

      Are you looking for perhaps this in cell E3 of RECAP, not reorder):

      =VLOOKUP($A3,INDIRECT(E$1&"!"&"A:J"),10,0)

      It may be copied down the rows and across the columns.

      It will lookup the book in col A of that row, in the sheet named in row 1 of that column, and place the amount from col J.

      Steve

      Reply | Quote
      • WSjrw5180
        AskWoody Lounger
        May 17, 2004 at 1:32 pm #828291

        Steve – thanks for the formula. This missing part now is that I need the formula to also look into column M on every tab – the formula should only put the amount from column J into Recap column E if the dates fall within May. And this would extend out – it needs to put the amount in June if the dates are June, etc.

        Thanks!
        James

        Reply | Quote
        • WSyoyophil
          AskWoody Lounger
          May 17, 2004 at 11:18 pm #828621

          I added to Steve’s formula and I admit there is probably a shorter formula that might work but if placed in E3, it works. You can copy it down. If you copy it across you manually have to change the month in the date ranges and then copy down. Maybe someone can fix that glitch.

          =IF(ISERROR(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:J”),10,0)),””,IF(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:m”),13,0)>=DATE(2004,5,1),IF(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:M”),13,0)<=DATE(2004,5,31),VLOOKUP($A3,INDIRECT(E$1&"!"&"A:j"),10,0),"")))

          -yoyophil

          Reply | Quote
          • WSkjktoo
            AskWoody Lounger
            May 18, 2004 at 5:22 am #828710

            To take up Yo’s challenge how about this:

            =IF(ISERROR(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:J”),10,0)),””,IF(TEXT(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:m”),13,0),”MMM”)=E$1,VLOOKUP($A3,INDIRECT(E$1&”!”&”A:j”),10,0),””))

            Reply | Quote
          • WSkjktoo
            AskWoody Lounger
            May 18, 2004 at 5:22 am #828711

            To take up Yo’s challenge how about this:

            =IF(ISERROR(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:J”),10,0)),””,IF(TEXT(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:m”),13,0),”MMM”)=E$1,VLOOKUP($A3,INDIRECT(E$1&”!”&”A:j”),10,0),””))

            Reply | Quote
        • WSyoyophil
          AskWoody Lounger
          May 17, 2004 at 11:18 pm #828624

          I added to Steve’s formula and I admit there is probably a shorter formula that might work but if placed in E3, it works. You can copy it down. If you copy it across you manually have to change the month in the date ranges and then copy down. Maybe someone can fix that glitch.

          =IF(ISERROR(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:J”),10,0)),””,IF(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:m”),13,0)>=DATE(2004,5,1),IF(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:M”),13,0)<=DATE(2004,5,31),VLOOKUP($A3,INDIRECT(E$1&"!"&"A:j"),10,0),"")))

          -yoyophil

          Reply | Quote
      • WSjrw5180
        AskWoody Lounger
        May 17, 2004 at 1:32 pm #828292

        Steve – thanks for the formula. This missing part now is that I need the formula to also look into column M on every tab – the formula should only put the amount from column J into Recap column E if the dates fall within May. And this would extend out – it needs to put the amount in June if the dates are June, etc.

        Thanks!
        James

        Reply | Quote
    Viewing 2 reply threads
    Reply To: Excel Formula (2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    March 2025
    S M T W T F S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.