Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Formula (2000)

    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!

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (2000)

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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Formula (2000)

    Are you looking for perhaps this in cell E3 of RECAP, not reorder):
    <pre>=VLOOKUP($A3,INDIRECT(E$1&"!"&"A:J"),10,0)</pre>


    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

  4. #4
    New Lounger
    Join Date
    Sep 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (2000)

    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

  5. #5
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (2000)

    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,INDIREC T(E$1&"!"&"A:j"),10,0),"")))

    -yoyophil

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (2000)

    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),""))

  7. #7
    New Lounger
    Join Date
    Sep 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (2000)

    Thanks Yoyo and kjktoo!

    It worked out great.

    James

Posting Permissions

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