formula help PLEASE PT 2
in [General]
|
Prev: missing styles
Next: Appointments won't open
From: Don Guillett on 12 Apr 2010 08:39 To add to this. Unless there is something in xl2007 I would still suggest limiting the rows to only those necessary and use a named defined range to determine the block. I think it's especially important using vlookup. I once had a client that did need to use lots of rows so I figured out a way to have a macro re-define the range names. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "JLatham" <JLatham(a)discussions.microsoft.com> wrote in message news:1A6F2A43-18F1-4B80-842B-07AF1AF9170D(a)microsoft.com... >I know you've seen this already in the other discussion, but I'll repeat >here > to 'close' this one: > ===== > Probably my fault, I was probably working in Excel 2007 when I built up > the > formula. You'll need to specify the start and end rows for the column > references in OUTBOUND, like: > =SUMPRODUCT(--(OUTBOUND!$A$1:$A$100>=Sheet1!$C$4),--(OUTBOUND!$A$1:$A$100<=Sheet1!$D$4),--(OUTBOUND!$B$1:$B$100)) > > You may want to increase the $A$100 and $B$100 row numbers to go well down > below where the list ends now to keep from having to revise it for a > while. > In any case, that last row number needs to be the same for both the $A$### > and $B$### references. > > My apologies for the headache. > > "Norm" wrote: > >> Thank you for the help on the other discussion it worked fine >> On this discussion I entered the formula and adjusted the "Sheet 2" to >> "OUTBOUND" to reflect the name on the sheet >> >> =SUMPRODUCT(--(OUTBOUND!$A:$A>=$C$4),--(OUTBOUND!$A:$A<=$D$4),--(OUTBOUND!B:B)) >> >> This results in a "#NUM in the cell >> What have I missed? >> >> >> "JLatham" wrote: >> >> > Norm, first - I think I provided a fix to the problem in the other >> > discussion >> > on VLOOKUP(). Check it out. >> > >> > Now, for this problem. If Row 16 on Sheet2 is the one where you have >> > Apr11-17 shown in your example, that's probably not a real date and >> > while you >> > could use VLOOKUP() to return those values just as before, another way >> > to get >> > the values from a column on Sheet2 associated with dates would be like >> > this. >> > >> > Assumptions: >> > Sheet2 has dates in column A >> > In sheet 1 you have the start date in C4 and the end of period date in >> > D4 >> > (could be any other cell, but D4 for this example). Then >> > =SUMPRODUCT(--(Sheet2!$A:$A>=$C$4),--(Sheet2!$A:$A<=$D$4),--(Sheet2!B:B)) >> > would give you the total of values in column B as a result. The way >> > I've >> > set up the formula, you can then drag it to the right on your sheet and >> > the >> > final column will change from B, to C, to D, to E, etc depending on how >> > far >> > across the sheet you drag or fill it. >> > If you put 11-Apr in C4 and 17-Apr in D4, then it would give you the >> > totals >> > for that period. >> > >> > >> > "Norm" wrote: >> > >> > > I need to get the information from sheet 2 row 16 using the date in >> > > sheet 1 >> > > C4 and the same date in sheet 2 A16 >> > > sheet 1 >> > > >> > > REPORT DATE 4/12/2010 >> > > APR 11 - 18 >> > > KPI No Genesis KPI's UOM >> > > >> > > sheet2 >> > > >> > > 15-Apr >> > > 16-Apr >> > > 17-Apr >> > > Apr11-17 11 19 10 >> > > 18-Apr >> > > 19-Apr >> > > >> > > or can I lookup a sum of cells using a range of dates? >> > > Thanks >> > > |