re post range check

hi
i have a worksheet with a lot of data on it
column A contains the date
column B contains an area
column C contains the fault
column D contain a quantity
              A                        B                          C
D
           1/4/04                 stores                damaged
10
           7/4/04                 bins                    rust
60
             --                        --                      ---          
                --
             --                       ---                     - --          
                --
         31-12-04            floor                     damp
100
what i want to do is    return  quantity for a date range(one week) that is
both damaged and in the stores or any other combination(it could be any
combination)
there are 10 areas and 12 faults plus 52 weeks this is a lot of formulas
i have had the formula below posted by Frank Kabel, this works fine for an
idividual event

=SUMPRODUCT(--(A1:A100>=DATE(2004,4,1)),--(A1:A100<=DATE(2004,4,7)),--(
B1:B100="stores"),--(C1:C100="damaged"),D1:D100)
problem is i have 10 areas 12 faults and 52 weeks
that makes over 6000 formulas
is there a way either in VB code or in a formula to check the array over a
range


0
kcarte10 (31)
4/9/2004 6:32:42 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
523 Views

Similar Articles

[PageSpeed] 48

Hi
try using a pivot table then:
- use your columns as row items
- group by the date

See the following sites:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm



--
Regards
Frank Kabel
Frankfurt, Germany


kevin carter wrote:
> hi
> i have a worksheet with a lot of data on it
> column A contains the date
> column B contains an area
> column C contains the fault
> column D contain a quantity
>               A                        B                          C
> D
>            1/4/04                 stores                damaged
> 10
>            7/4/04                 bins                    rust
> 60
>              --                        --                      ---
>                 --
>              --                       ---                     - --
>                 --
>          31-12-04            floor                     damp
> 100
> what i want to do is    return  quantity for a date range(one week)
> that is both damaged and in the stores or any other combination(it
> could be any combination)
> there are 10 areas and 12 faults plus 52 weeks this is a lot of
> formulas
> i have had the formula below posted by Frank Kabel, this works fine
> for an idividual event
>
>
=SUMPRODUCT(--(A1:A100>=DATE(2004,4,1)),--(A1:A100<=DATE(2004,4,7)),--(
> B1:B100="stores"),--(C1:C100="damaged"),D1:D100)
> problem is i have 10 areas 12 faults and 52 weeks
> that makes over 6000 formulas
> is there a way either in VB code or in a formula to check the array
> over a range

0
frank.kabel (11126)
4/9/2004 7:34:51 AM
Frank
thankyou for your reply
i have never used a pivot table
but tried very quickly this morning and run in to trouble(got myself
confused)





"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:#UnGlUgHEHA.3656@tk2msftngp13.phx.gbl...
> Hi
> try using a pivot table then:
> - use your columns as row items
> - group by the date
>
> See the following sites:
> http://www.cpearson.com/excel/pivots.htm
> http://peltiertech.com/Excel/Pivots/pivotstart.htm
> http://www.contextures.com/xlPivot02.html
> http://www.ozgrid.com/Excel/excel-pivot-tables.htm
>
>
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> kevin carter wrote:
> > hi
> > i have a worksheet with a lot of data on it
> > column A contains the date
> > column B contains an area
> > column C contains the fault
> > column D contain a quantity
> >               A                        B                          C
> > D
> >            1/4/04                 stores                damaged
> > 10
> >            7/4/04                 bins                    rust
> > 60
> >              --                        --                      ---
> >                 --
> >              --                       ---                     - --
> >                 --
> >          31-12-04            floor                     damp
> > 100
> > what i want to do is    return  quantity for a date range(one week)
> > that is both damaged and in the stores or any other combination(it
> > could be any combination)
> > there are 10 areas and 12 faults plus 52 weeks this is a lot of
> > formulas
> > i have had the formula below posted by Frank Kabel, this works fine
> > for an idividual event
> >
> >
> =SUMPRODUCT(--(A1:A100>=DATE(2004,4,1)),--(A1:A100<=DATE(2004,4,7)),--(
> > B1:B100="stores"),--(C1:C100="damaged"),D1:D100)
> > problem is i have 10 areas 12 faults and 52 weeks
> > that makes over 6000 formulas
> > is there a way either in VB code or in a formula to check the array
> > over a range
>


0
kcarte10 (31)
4/9/2004 9:26:28 AM
Reply:

Similar Artilces:

Analytical Accounting posting to GL, but sometimes does not post t
Has anyone found a way to address the issue where transactions sometimes post to the GL, but not to AA? When we try and use the Analytic Adjustment Entry to make adjustments we get an error message that says: "The journal entry number you've entered doesn't exist, hasn't been posted, or is in history. Enter or select a valid journal entry number." These GL entries exist, have been posted, and are not in history. As a result the error message doesn't make sense. Any ideas? We did not have this issue with GP9, but are now having it with GP10. Andrew: I&#...

Checkbook Posting Journal
There is a report that prints when I post payroll checks that I need to modify. It is the Checkbook Posting Journal. I need to add the Employee ID and Employee Name to it. Can anyone tell me how to do this? Thanks, Jocelyn Jocelyn, Ok, it sounds that this is a job for Report Writer. However, the Checkbook Posting Journal already displays the Paid To/Received From field which should already be displaying the employee name. All you should have to do is drag the CMLinkID field from the CM Transaction table onto the report. Once that is done, go back to GP and grant security to the mod...

Disable Spell Check
Is it possible to disable the spll check feature in Word 2002? It depends what you mean by 'disable'. You can prevent the spell checker from checking a document by selecting all of it and applying the no proofing language parameter to the selected text. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>><...

Subject Spell Check
How do I set Outlook 2002 (SP-2) to spell check the subject line when I hit F7 to force a spell check? Please advise! Thanks. ~jl YOu can't - it will only spell-check the message body. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, J. E. Lewis asked: | How do I set Outlook 2002 (SP-2) to spell check the | subject line when I hit F7 to force a spell check? | ...

Command button
[Excel 2003] I have command buttons on the userform which access other userforms (which drive the spreadsheet data entry) I have a listbox on the userform displaying a number. I would like to enter code into the command button activations such that if the number in the listbox does not equal the number stored within the code of the command button then a mesage box is displayed and the command button does not activate its sub routines. Can anyone help? Thanks, Roger Something like this: Option Explicit Public Property Get MayProceed() As Boolean Const M...

Printing Checks #4
Do I have to buy checks that already have my account and routing number printed to use Money 2003 Standard? Yes. "Tom P" <anonymous@discussions.microsoft.com> wrote in message news:06b601c3aef9$dc7264c0$a301280a@phx.gbl... > Do I have to buy checks that already have my account and > routing number printed to use Money 2003 Standard? ...

Concise Payable Check Voucher Listing
Is there a way (besides modifying the existing Check Edit List - this is needed as well) to get a concise pre check run listing of the all of the Vouchers in a batch prior to posting for easy review? If I can copy the Edit List and create a custom report then the user cant parameterize to limit to a given batch, so this is not ideal. My AP Manager wants to see a tabular listing with just a few basic fields (like VendorID, Voucher#, Date, AmountBeingPaid for this voucher), much more concise than the edit list. I dont see unposted check batches in SmartList, and I can't figure out ho...

Sheet range reference
I have a workbook with a number of sheets, let's say named sheet01, sheet02 ......sheet10. These sheets are of the same structure and are representing some development in time. So now I would like to make a chart (or separate table) the where the X coordinate is the sheet number and the Y some cell on each of the sheets. Is this possible with a simple reference like the ranges within the same chart or do I really have to create a macro or do it by hand? Jens. Each series must reside on a single sheet. You can create a summary sheet which has something like: A1: =Sheet1!A1 A2: ...

NOTIFYICONDATA and posting message?
Hello guys, how to call NOTIFYICONDATA and post message to the icon in MFC extension dll? I can create the icon, but i can not catch the message which point to the icon. my code in DllMain: CreateDialog((HINSTANCE)AfxGetApp(),MAKEINTRESOURCE (IDD_DIALOG1),NULL,SetDispDlgProc); dlgTimer.Create(IDD_DIALOG1); SetResolutionMenu (dlgTimer); m_hIcon = AfxGetApp()->LoadIcon(IDI_DLLSTATUS); StatusAreaIcon(NIM_ADD,1006,m_hIcon,"running"); by the way, the callback function SetDispDlgProc never be called, why? thank you! delu On Mon, 15 Dec 2003 06:17:11 -0800, delu wrote: &...

Incorrect information on Check Stub
When printing a batch of checks, on the check stub in the field that we have designated for PO Number the information is incorrect/overwritten. In this case I have 18 invoices to pay on one check, nine of them with legitimate PO Numbers and the other nine with notes pertaining to the Invoice number that have been entered in the PO Number field in GP. Seven of the nine PO numbers have been overwritten with the nine notes that were related to the nine invoices directly under the top nine, i.e. the seven PO Numbers have been replaced and are no where on check stub the other two printed f...

Advanced Search Criteria
Just an FYI, since there doesn't seem to be a solution posted anywhere on the web, at least that I could find. If you want to search using dynamic dates, at least in the case of finding items older than a certain relative date, such as more than 30 days old, I used the following criteria successfully in Outlook 2007: Advanced Find > Advanced (tab) > Field: Sent Condition: On or after Value: 30 days ago ...

Purchase Receipt posting to closed period
We have a problem with the invoice matching activity in the system in regards to closed G/L periods. For example: - The receiving is done in period 2 (February) - Two weeks later the supplier invoice comes in with a different cost, it is now period 3 (March). Period 2 is now closed The system will flag these transactions and prevent the posting because the period is closed but will "place" them in the master posting with the date from the previous period. So it is smart enough to identify that the period is closed but dumb enough to generate the transactions wi...

Pivot Table Data Ranges
I just upgraded to excel2000, and am finding out that keyboard shortcuts used in 97 do not exist in 2000. Does anyone know of a KEYBOARD shortcut to modify, expand and or create data ranges in the dialog box for entering the range for the pivot. I have many data 'lists' which are added to each month, and when I want to update the corresponding pivot table, I used to simply hit "shift" + "end" + "down arrow" to automatically go to the last populated cell in the list. In 2000, I either have to enter in the cell addy's manually or use the mouse...

Re: money
"CCrusher" <invalid@domain.com> wrote in message news:... > How many versions of money will there be? When will people get to stop > buying the same old program year after year. If Microsoft was into > landscaping, they would sell the same tree they planted in 1990 to the > buyer year after year.....Microsoft says "mam, you owe us 40 dollars".., > mam says " but you charged me for that tree 3 years ago"...Microsoft > says.." yes, but the tree has grown, and with increased ability of the > tree, you must constantly pay for the...

?-Change data series range as data is entered?
I have a chart which shows a prediction curve and an actual curve. The prediction curve runs out until the end of a project. The actual curve is populated as data is entered. I will be generating a number of these charts from a datatable. I would like to plot the prediction curve for the duration of the project (easy). I would then like to plot the actual curve with only the existing data and not have the line drop to the x-axis at the end. Can I put a formula in the data series "x values" field? I hope that my description makes sense..... Thanks, Carl -- isofuncurves ---...

post date benefit changes
Hello: In either GP 9.0 or 10.0, is it possible to post-date changes to employee benefit codes in payroll for changes in amounts? There is the Start Date field in Employee Benefit Maintenance. But, this cannot be used for post-dating changes as the next payroll run that you conduct (if before the start date) will not use the benefit code. Is there a third-party that will do this? childofthe1980s GP Payroll does not support post-dated deductions/benefits. The Advanced HR module provides the ability to do effective dating on HR benefits and, by extension, Payroll deductions and benefi...

total of a range of times
This should be simple for some of you, but not for me. I am a runner, I have 4 ind. cells w/my split times. How do I get a total for the tallied times. I'm far from being experienced but am trying to learn..any and all help will be much appreciated. Thanks, Roger Roger To help you in your quest to "try to learn" I will direct you to Chip Pearson's site so's you can learn just about all you'll ever need for Time Calculations. http://www.cpearson.com/excel/datetime.htm#AddingTimes Gord Dibben Excel MVP On Sat, 13 Nov 2004 15:19:02 -0800, "Roger WG" &...

How do I automatically plot different ranges of data in different.
I need to chart parameters that are a function of three variables in the 2002 version of Excel. I have used the x and y axis to represent 2 variables on an 'xy chart', but require an automatic method to represent the value of the third variable. I have tried the 'bubble-plot', but cannot get sufficient resolution between the data. Is their any way that I may plot the third variable in different colours/shades, that automatically changes for different ranges of the the third variable? THis might help you: http://www.peltiertech.com/Excel/Charts/format.html#CondChart &q...

Re-install
I am running Office 2003 Standard. Outlook was crashing on startup so I uninstalled it using the CD. When trying to reinstall the process gives an error message that it cannot find the file PCW_CAB_PATCH and will not proceed. There is no option to enter a location for that file. The file is not on the CD nor is it on my computer. Does anybody know what this means Thanks ...

Getting the longest lengh in range of cells
I am trying to get the length of the longest cell in a range and use the array {=len(a1:a1)}. What happens is that it picks up the length of cell A1. Is there a way in a formula to determine the longest cell length in a range, or through VBA. Thanks in advance Hi Try this formula array (please amend with last row number): {=MAX(LEN(A1:A7))} HTH Cordially Pascal "Geoff" <gh@bob.com> a �crit dans le message de news:uMKoCQiSFHA.1176@TK2MSFTNGP10.phx.gbl... > I am trying to get the length of the longest cell in a range and use the > array {=len(a1:a1)}. What happen...

Re: Cannot Insert Object #3
Hi all I'm having trouble inserting a .msg outlook file into an Excel workbook, can anyone please advise as to why I'm getting this error. Many thanks Badger -- BadgerMK ------------------------------------------------------------------------ BadgerMK's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31406 View this thread: http://www.excelforum.com/showthread.php?threadid=511014 ...

Complex coding question re: recordset
If I have an open form and am positioned on a new record with no bound field having been changed (dirty = false) and I call a public function which opens a recordset on the same table that my form is bound to and then do a series of movenexts , can my form become dirty? I'm getting strange results and am wondering if this could be the cause. What I am trying to do is to programatically retrieve a given record by doing an applyfilter. However, what seems to be happening is that somehow my record has become dirty and therefore the form is wanting to do a save before executing t...

Spell check not working in Outlook 2001 for Mac in OSX
Hello, When I try to use the spell check for messages I get the error message: "The spelling operation could not be completer. The spelling checker could not be started. Some components are either missing or incorrectly configured. See your administrator." Thanks, Virginia ...

Force TRUE in a check box as result from an option button??
I have a set of option buttons and a set of check boxes. One of the check boxes is for a certain value that is similar to one of the option buttons. Essentially, what I want is for that check box to automatically go to a "TRUE" (checked) state when the certain option button is selected. When the other option buttons are selected, the check box will remain in it's normal state which will allow the user to select it or not depending on other variables. But, if the option button is on that certain selection, I would like to make it so that check box MUST remain on TRUE, and ...

Select Range
Having trouble selecting several non-contiguous ranges. The spreadsheet I am working with is apprx. 300 rows by 110 columns. The sheet is subtotaled. I need to copy data and move to a summary worksheet. There is a subtotaled section entitled 'Risk'. Within this section, the only data required is Country, Customer, 3Q (orders, revenue, gross margin), and 4Q (orders, revenue, gross margin); columns B, E, AQ, AR, AS, BK, BL, and BM - respectively. The section title is in cell A162 and the data is in A163:CY200. I would prefer to select all of the data from the section at once, but ...