Excel Formula (2000)

Topic

New Reply

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

Replies

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

Reply | Quote

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

Reply | Quote

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

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

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

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

Thanks Yoyo and kjktoo!

It worked out great.

James

Reply | Quote

Thanks Yoyo and kjktoo!

It worked out great.

James

Reply | Quote

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

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

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