Populate a Table From Formula

  • Thread starter Thread starter Karl Burrows
  • Start date Start date
K

Karl Burrows

I have a link to an external Access database to track my time. I use the
Excel workbook (2003) to track a few details from the database. What I
would like to do is create a formula (probably an array?) that would fill in
a table/rows below with the data that matches the formula criteria of date
range. Right now, I have to create formulas to match each row of data in
the imported sheet, so if the data grows beyond the number of rows I created
the formulas, then I have to edit the worksheet again. Most of the time, it
has already passed the last row before I realize I need to expand the rows
of formulas.

It just seems there must be a better way of pulling data to populate rows.

Thanks!!
 
Karl
Have you set up a query in excel to pull in the data from your Access
database? If you have you can make the query use a date range from
you excel spreadsheet using the start date in one cell and the end
date in another.

Regards

Dave
 
I have the query running, but there are several worksheets that use this
database import, so asking for a date each time is asking for trouble from
the people that open them. Also, some formulas look at current dates and
some look at the entire history, so that wouldn't work. It still does solve
the formula problem, just potentially reduces the number of data rows that
are imported.

Thanks!
 
...
I have the query running, but there are several worksheets that use this
database import, so asking for a date each time is asking for trouble from
the people that open them.
Click to expand...

Use a second query e.g. if the query that all the worksheet use looks
like this:

SELECT * FROM MyTable;

then create another that looks like this:

SELECT * FROM MyTable
WHERE MyDate BETWEEN
#01 JUN 2004# AND #30 JUN 2004#;

This is the kind of thing databases do to earn their living. Better to
hit the database twice than mess around with slow worksheet formulas.

Jamie.

--
 
Back
Top