[AccessD] An Excel question

Jim Lawrence accessd at shaw.ca
Sat Apr 27 21:08:37 CDT 2013


Hi James:

I will do some testing with SUMPRODUCT() and see if I can get a hang of the
function. 

I did discover that if you referred to a worksheet not in the spreadsheet of
which you are working Excel is smart enough to ask you to identify the
required spreadsheet with the appropriately named worksheet...with a browse
and search window. It will work great for client as a super simple work
around...automate later.

After that a set of cascading worksheets created a complete ten years window
with graphics. (Client should be happy. ;-))

Thanks for your help. One note; a very useful function SUMIFS is only
available in Excel versions 2007 and greater and the client only has 2003.

Jim  

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of James Button
Sent: Saturday, April 27, 2013 1:32 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] An Excel question

SUMPRODUCT() may do

Note operands should be numeric
so either use * between them to force multiplacation, or prefix each with --

Or, if you are after the ability to alter the entire range being viewed then

there is the use of names
=SUMIF(Income!C3:C7, >0, Income!B3:B7)";
define name
incomevl= =Income!C3:C7
=SUMPRODUCT(--(Incomevl>0)*Incomevl)"

(NOT TESTED - NO EXCEL HERE)

For a better answer, please give more detail of your data setup and needs

JimB

----- Original Message ----- 
From: "Jim Lawrence" <accessd at shaw.ca>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Friday, April 26, 2013 10:59 PM
Subject: [AccessD] An Excel question


> Hi All:
>
> I have an Excel question, probably not too difficult but time is of the
> essence so if someone knows the answer, off the top, excellent.
>
> To pull an amount from an adjacent or cascading sheet, the code, in a 
> cell,
> would be similar to "=SUM(Income!D26)". This is a single amount from a 
> sheet
> named "Income" from one cell "D26". And a single cell criteria can easily
> set like: "=SUMIF(Income!C3:C7, >0, Income!B3:B7)"; range, criteria,
> sum_range.
>
> Question: Is there are way to pull a range of cells, given a criteria, 
> from
> an adjacent sheet...without having to do a major chunk of programming?
>
> I am looking to create a number of year worksheet which pull detail from 
> one
> master worksheet which has a 5 to 10 year spread of data. It needs to be
> totally automated.
>
> TIA
>
> Jim
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list