1. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Excel Formula (2000)

=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. ## 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
•