H
Heliocracy
Okay I hate to ask this, but I'm desperate. I need a macro to replace a long
function which, when placed into as many cells as it needs to go, makes the
workbook extremely slow to open and update.
First, I have a huge (65536 rows) spreadsheet (sheet1 of notes.xls) with
data in columns A:Q. Column B has dates, column F has Place Names, and
column J has People.
A second worksheet (sheet2 of counts.xls) has Place Names in column A,
People in column E, and a column for each week in a year starting with column
H. In each cell (row) of those month columns, I'm calculating how many times
the Person specified in column E of that row was matched with the Place in
column A of that row, during the week whose ending date is specified in the
column header, on the other worksheet (i.e. how many times the specified
person was matched with the specified place, during the given week, on sheet1
of notes.xls).
Here's the formula:
=IF(TODAY()<H$5-6,"",SUM(IF(ISBLANK($A7)=FALSE,IF([notes.xls]sheet1!$F$2:$F$65536=$A7,IF([notes.xls]sheet1!$J$2:$J$65536=$E7,IF([notes.xls]sheet1!$B$2:$B$65536>=H$5-6,IF([notes.xls]sheet1!$B$2:$B$65536<=H$5,1,0)))))))
H$5 contains a week-ending date--it's the header of a week column. This
formula appears in 52 columns and over 1000 rows of the workbook counts.xls.
As you might imagine, it bogs down.
I know it's asking a lot, but what should I do? Is there a macro to save me?
Thanks,
Mike
function which, when placed into as many cells as it needs to go, makes the
workbook extremely slow to open and update.
First, I have a huge (65536 rows) spreadsheet (sheet1 of notes.xls) with
data in columns A:Q. Column B has dates, column F has Place Names, and
column J has People.
A second worksheet (sheet2 of counts.xls) has Place Names in column A,
People in column E, and a column for each week in a year starting with column
H. In each cell (row) of those month columns, I'm calculating how many times
the Person specified in column E of that row was matched with the Place in
column A of that row, during the week whose ending date is specified in the
column header, on the other worksheet (i.e. how many times the specified
person was matched with the specified place, during the given week, on sheet1
of notes.xls).
Here's the formula:
=IF(TODAY()<H$5-6,"",SUM(IF(ISBLANK($A7)=FALSE,IF([notes.xls]sheet1!$F$2:$F$65536=$A7,IF([notes.xls]sheet1!$J$2:$J$65536=$E7,IF([notes.xls]sheet1!$B$2:$B$65536>=H$5-6,IF([notes.xls]sheet1!$B$2:$B$65536<=H$5,1,0)))))))
H$5 contains a week-ending date--it's the header of a week column. This
formula appears in 52 columns and over 1000 rows of the workbook counts.xls.
As you might imagine, it bogs down.
I know it's asking a lot, but what should I do? Is there a macro to save me?
Thanks,
Mike