zookeepertx
Well-known Member
- Joined
- May 27, 2011
- Messages
- 568
- Office Version
- 365
- Platform
- Windows
Hello all!
I have a workbook with 3 sheets in it. The first sheet summarizes certain data from the other 2 sheets. Other people regularly add and subtract rows on the data sheets and will sometimes insert blank rows; I don't know why, though.
I need the formulas on the summary sheet to refer to ALL of the data in - for example - column F on sheet 2. How can I get the formula to look at all the rows with data, not just down to the first blank row? If I just use a reference to the entire column - ($F:$F) - the formula runs really slowly. When I evaluate the formula, when it shows the result of ($F:$F), it shows (8,8,4,8,8,4,4,8,4,4,6,4,1,1,1,1,1,1,1.......) and the 1s go on FOREVER! (I assume for however many rows are available on a spreadsheet) Then, another part of the formula references another column and it happens again!
I'm a little afraid to make the data into named ranges because I know the people using the workbook could find SOME way to throw a wrench into it. I'd make a macro for it, but it'd just be so nice to use formulas on the summary and have it always be up-to-date!
Here's an example of the formula in C5 on the summary sheet:
It gets the month in C3 of the summary sheet, finds all the rows on the data sheet (called "COMPLETE2") where A has the same month. Then looks at column F on the data sheet and counts how many rows for the given month say "ALLOCATION" in F. How can I get the formula to find the LAST occupied row of data, not stop at the first blank row?
Thanks!
Jenny
I have a workbook with 3 sheets in it. The first sheet summarizes certain data from the other 2 sheets. Other people regularly add and subtract rows on the data sheets and will sometimes insert blank rows; I don't know why, though.
I need the formulas on the summary sheet to refer to ALL of the data in - for example - column F on sheet 2. How can I get the formula to look at all the rows with data, not just down to the first blank row? If I just use a reference to the entire column - ($F:$F) - the formula runs really slowly. When I evaluate the formula, when it shows the result of ($F:$F), it shows (8,8,4,8,8,4,4,8,4,4,6,4,1,1,1,1,1,1,1.......) and the 1s go on FOREVER! (I assume for however many rows are available on a spreadsheet) Then, another part of the formula references another column and it happens again!
I'm a little afraid to make the data into named ranges because I know the people using the workbook could find SOME way to throw a wrench into it. I'd make a macro for it, but it'd just be so nice to use formulas on the summary and have it always be up-to-date!
Here's an example of the formula in C5 on the summary sheet:
Code:
=SUMPRODUCT(--(IFERROR(MONTH(COMPLETE2!$A:$A),0)=MONTH(DATEVALUE(C$3 & "1")))*(COMPLETE2!$F:$F="ALLOCATION"))
It gets the month in C3 of the summary sheet, finds all the rows on the data sheet (called "COMPLETE2") where A has the same month. Then looks at column F on the data sheet and counts how many rows for the given month say "ALLOCATION" in F. How can I get the formula to find the LAST occupied row of data, not stop at the first blank row?
Thanks!
Jenny