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