Problems with Array
 

Hi all,

Just wandering if anyone has encountered a similar problem or may have
a solution for me.

I am building a schedule tool. On worksheet 'Schedule' column A is
date, B is time and C onwards are resources. (where the name is in C1,
D1, E1, etc and the array formula sits below it).

So the formula below (kindly provided here!) matches up against info on
the 'Data' worksheet - which is imported from outlook. If the time, date
and resource all match - a '1' is placed in the cell.

=
--ISNUMBER(MATCH(1,(Data!$A$2:$A$1000=Schedule!$A18) *(Data!$B$2:$B$1000<=Schedule!$B18)*(Data!$C$2:$C$ 1000Schedule!$B18)*(Data!DU$2:DU$1000=Schedule!AF $1),0))

Now this works perfectly for todays date (20/12/05) - but does not seem
to work for any future dates.

I'm also noticing the spreadsheet is extremely slow (80 resources x 40
time slots for each day = a lot of formulae!). If anyone can suggest
another approach to reduce the file size and make this a little
quicker...... :):):)

Thanks

Rob

EDIT: Oh...BTW....The obscure reference to range DU on the data
worksheet is correct!


--
systemx
------------------------------------------------------------------------
systemx's Profile: http://www.excelforum.com/member.php...o&userid=29254
View this thread: http://www.excelforum.com/showthread...hreadid=494811

Problems with Array
 

Since this should be updating with any great frequency, using a macro to
populate the table might be more acceptable.

Your formula should work as long as the dates match. Make sure your dates
don't contain time values as well.

Array formulas take a lot of time to calculate as you have observed.

Another possibility might be a pivot table but you might have to add some
dummy columns in your source data to get the time representation you want
(in blocks).

--
Regards,
Tom Ogilvy

"systemx" wrote in
message ...

Hi all,

Just wandering if anyone has encountered a similar problem or may have
a solution for me.

I am building a schedule tool. On worksheet 'Schedule' column A is
date, B is time and C onwards are resources. (where the name is in C1,
D1, E1, etc and the array formula sits below it).

So the formula below (kindly provided here!) matches up against info on
the 'Data' worksheet - which is imported from outlook. If the time, date
and resource all match - a '1' is placed in the cell.

=
--ISNUMBER(MATCH(1,(Data!$A$2:$A$1000=Schedule!$A18) *(Data!$B$2:$B$1000<=S
chedule!$B18)*(Data!$C$2:$C$1000Schedule!$B18)*(D ata!DU$2:DU$1000=Schedule!
AF$1),0))

Now this works perfectly for todays date (20/12/05) - but does not seem
to work for any future dates.

I'm also noticing the spreadsheet is extremely slow (80 resources x 40
time slots for each day = a lot of formulae!). If anyone can suggest
another approach to reduce the file size and make this a little
quicker...... :):):)

Thanks

Rob

EDIT: Oh...BTW....The obscure reference to range DU on the data
worksheet is correct!


--
systemx
------------------------------------------------------------------------
systemx's Profile:
http://www.excelforum.com/member.php...o&userid=29254
View this thread: http://www.excelforum.com/showthread...hreadid=494811