Formula help: VLOOKUP in a combination...

[j razz]

Here is my issue. I have a spreadsheet made up of three sheets.


BACKGROUND:
Sheet one is a skeleton sheet that information is dumped into on a
daily basis.

Sheet two tabulates the data into totals and such based on the
criteria set up for this spreadsheet in weekly increments with a total
of 56 weeks.

Sheet three takes the totals from sheet two and lists them in a
concise manner.

ISSUE:
I have several variables to contend with to finish sheet 3:
1. It is date dependant. (It must fall within this range of dates-
eg 3-18-2007 to 3-24-2007)
2. It is criteria specific. (It must be under a certain category- eg
all items for the week specified above that fall under CKT)
3. Sheet 2 has all the start dates in column A and all the end dates
in column B and all the criteria specific information in column AT
4. The user needs to be able to specify the date range they want to
view the totals for. (Eg Cell C2 would contain the start date-
3-18-2007 and cell E2 would contain the end date 3-24-2007)
5. Cell B4 needs to populates with the correct total for that date
range specified.

GOAL:
I would like to have it to where if the user typed in the date range
in cells C2 and E2 on sheet 3 the date range would be found on sheet 2
and then the accurate number would be pulled from the corresponding AT
cell on sheet 2 and plugged into cell B4 on sheet 3. (In this
instance the date 3-18-2007 is in cell A3, the date 3-24-2007 is in
cell B3 and the corresponding total is in cell AT3- all this is on
sheet 2)

I would very much appreciate the help on this. Thanks

j razz

 

[Guest]

Hi,

try this:

in the cell AT3 Sheet3 enter this formula:

=SUMPRODUCT(--(SHEET2!A1:A100=C2),--(SHEET2!B1:B100=E2),--(SHEET2!AT1:AT100)).

Thanks,

PS: you can change the ranges "A1:A100", "B1:B100" and "AT1:AT100"

 

[Guest]

Hi,

Sorry the correct formula is:

=SUMPRODUCT(--(SHEET2!A1:A100>=C2),--(SHEET2!B1:B100<=E2),--(SHEET2!AT1:AT100))

Thanks,

 

[j razz]

Hi,

Sorry the correct formula is:

=SUMPRODUCT(--(SHEET2!A1:A100>=C2),--(SHEET2!B1:B100<=E2),--(SHEET2!AT1:AT1­00))

Thanks,
--

Farhad Hodjat








- Show quoted text -

Thank you, it works great for one variable. How do I combine or nest
these formulas together?

=SUMPRODUCT(--(Tabulation!$A$3:$A$54>=$C$2),--(Tabulation!$B$3:$B$54<=
$E$2),--(Tabulation!AU$3:AU$54)) with =SUMPRODUCT(--(Tabulation!$A$3:$A
$54>=$C$2),--(Tabulation!$B$3:$B$54<=$E$2),--(Tabulation!AV$3:AV$54))
with =SUMPRODUCT(--(Tabulation!$A$3:$A$54>=$C$2),--(Tabulation!$B$3:$B
$54<=$E$2),--(Tabulation!AW$3:AW$54)) with =SUMPRODUCT(--(Tabulation!$A
$3:$A$54>=$C$2),--(Tabulation!$B$3:$B$54<=$E$2),--(Tabulation!AX$3:AX
$54)) with =SUMPRODUCT(--(Tabulation!$A$3:$A$54>=$C$2),--(Tabulation!$B
$3:$B$54<=$E$2),--(Tabulation!AY$3:AY$54))

 

[j razz]

One other thing, how can I get it to update once the dates are entered
in C3 and E3? It does not refresh unless I change a variable on
sheet2 (Tabulation)?

Thanks.

j razz

 

[j razz]

Here is the spreadsheet in case it helps you to see what I am trying
to do.

http://www.jrazzcreations.com/Spreadsheets/Coverkids_Log_w_Tabulations_w_Totals1.xls

I need all the blocks on Sheet 3 to populate according to the date
range selected. They will get their information from sheet2 which in
turn gets its information from sheet1.

Also, the formulas that are in each blank on sheet3 are hard coded for
one set weeks worth of information as if I can't get this to work, I
will be using sheets that equal out to a weeks worth of data per sheet
that covers a whole year. This hard coded code can be erased and
replaced with whatever will work. Thanks!

j razz

 

[j razz]

I have been staring at this for way too long. Sorry for the
additional posts. I believe I have got it- there is no need to nest
or combine formulas. I just got confused. Thanks again.

j razz

 

[j razz]

Although, with what I said in the last post,

I have found that this part of the formula does not cause a stop in
what the formula looks for as it still shows anything past this end of
search date: --(Tabulation!$B$3:$B$54<=$E$1)

The first part works fine with the start date and nothing showing up
before it, but that part does not keep it from looking past the end
date. Any ideas?

j razz