Create List with External Data

  • Thread starter Maxime Maugeais
  • Start date
M

Maxime Maugeais

Hi,

I've create queries that are linked to Access. So some columns have
external data, and others are formulas that use the external data.

I've applied filters to the Headings so I could sort the data. I tried
creating a list, but that meant I could not refresh the data coming from the
queries.

I wanted to use the TOTAL feature that come with the list. So I could get
the Average, Count of the showing data.

So how can I have at the same time, queries to the database, and
Averages/counts of filtered data?

Many thanks.

Max
 
F

Frank Stone

hi.
not sure exactly how you have set up things but
Microsoft Query is real picky about the exteranal data.
All of the space on the sheet that is the external data is
reserved by the querytable/
You cannot add columns for formulas but you can put formula
along the side of the query table and at the bottom.
you cannot add rows to the querytable and insert subtotals
or formuals.
Using some of the built in excel features on the querytable
does cause refresh probems.
It is best to leave the querytable alone and so everything
else all to the side or like me...another sheet.
You can put fomuals at the bottom but you will have to go
into the querytable's property sheet and check copy down
formulas.
this probably don't answer your guestion. this is just to
tell you that you have to treat query tables different than
regular data.
regard
Frank
 
D

Debra Dalgleish

You could link the cells to another sheet, and create a list there.

Or, insert a couple of rows above the external data, and calculate the
totals there.
 
M

Maxime Maugeais

Hi Debra,
Or, insert a couple of rows above the external data, and calculate the
totals there.
Click to expand...

That's what I had in mind, but how shoudl I write a formula that will only
consider the values that are shown.
For example, Column B is a query on that return profit in $. Right now I
have 50 rows of data, but this will increase every day. Hence the need for
the query. Column A are dates. I would like to be able the filter the dates
and have only "MOnday" appear and calculate the avg of column B. How can I
do this? Right now, even if I display only the "Monday" profits, my formula
calculates the Avg for all the rows.

Thanks
 
D

Debra Dalgleish

If you use the SUBTOTAL function, instead of SUM or AVERAGE, it will
only include the visible rows after you apply a filter. The first
argument specifies which calculation should be done. For example,

=SUBTOTAL(9, A5:A1000)

will SUM the visible rows.

and =SUBTOTAL(1, A5:A1000) will AVERAGE them.

There's information on SUBTOTAL in Excel's Help.
 
Top