Follow up question about consolidating dups and sums

I used the pivot table and it worked like a charm. 
However, the data is going to be transferred into another program 
and it needs to be in the same format as the original with headers like: 

State   County   Tons   Commodity 

I have played around with the pivot table some, but it is not right.
Perhaps a formula or function would work better?  Thanks! 

  
-- 
taz0923
0
Utf
4/3/2010 5:31:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

11 Replies
928 Views

Similar Articles

[PageSpeed] 31

This would be really easy to do with MS Access, but challenging in MS Excel.

In Excel, you could list all counties and products on a seperate worksheet 
then use the sumproduct formula to lookup the sums.  Not a very elegant 
solution but it might work.

Tom

"TriciaZ" wrote:

> I used the pivot table and it worked like a charm. 
> However, the data is going to be transferred into another program 
> and it needs to be in the same format as the original with headers like: 
> 
> State   County   Tons   Commodity 
> 
> I have played around with the pivot table some, but it is not right.
> Perhaps a formula or function would work better?  Thanks! 
> 
>   
> -- 
> taz0923
0
Utf
4/3/2010 6:18:03 PM
I was thinking that too.  I could open Access and import the data from excel.
Then run a query??  Then send it back to excel.  I have 10 separate sheets 
of data with thousands of rows of data...any more detailed suggestions would 
be great! Thanks 
-- 
taz0923


"tompl" wrote:

> This would be really easy to do with MS Access, but challenging in MS Excel.
> 
> In Excel, you could list all counties and products on a seperate worksheet 
> then use the sumproduct formula to lookup the sums.  Not a very elegant 
> solution but it might work.
> 
> Tom
> 
> "TriciaZ" wrote:
> 
> > I used the pivot table and it worked like a charm. 
> > However, the data is going to be transferred into another program 
> > and it needs to be in the same format as the original with headers like: 
> > 
> > State   County   Tons   Commodity 
> > 
> > I have played around with the pivot table some, but it is not right.
> > Perhaps a formula or function would work better?  Thanks! 
> > 
> >   
> > -- 
> > taz0923
0
Utf
4/3/2010 6:41:01 PM
Well, you have not provided much detail with which I can be more specific.  
Where does the data come from originally?  Why is it in Excel.?  Why is it on 
ten separate worksheets?  Is the format of the ten worksheets the same?  Why 
do you want to put it back into Excel when you mentioned something about 
sending it to another application? Maybe you could skip Excel altogether.  
Keep the data in a table in Access, set up the query, then export the data in 
a format that can be used by your other application.  Do you regularly append 
data to the existing?  Do you get a completely new set of data periodically?  
So many questions!

Tom


0
Utf
4/3/2010 7:49:01 PM
The data is from a spreadsheet created in Excel.  I am helping someone 
manipulate their data.  The data contains information from 7 different states 
and many many counties within those states.  The format of the 10 worksheets 
is the same, yes.  The end user needs the data in Excel in a specific 
arrangement in order to send it to another application for analysis.  I do 
not know if Access would make this process any easier, in fact, the more I 
think about it perhaps not.  I do not know if the end user regularly 
manipulates this data or not.  

Basically, I have 10 spreadsheets full of data all set up like this: 

State        County           Tons         Commodity 
IA	ADAMS	    143.97	    AMMONIUM NITRATE
NE	ADAMS	    97	    AMMONIUM NITRATE
OK	ALFALFA	    78.08	    AMMONIUM NITRATE
OK	ALFALFA	    101	    AMMONIUM NITRATE
IA	ALLAMAKEE   72.88	    AMMONIUM NITRATE
IA	ALLAMAKEE   109.25	    AMMONIUM NITRATE
KS	ALLEN	    1014.69	    AMMONIUM NITRATE
LA	ALLEN	    118.78	    AMMONIUM NITRATE


The sheet needs to read: 
State        County         Tons          Commodity 
IA            ADAMS        143.97       AMMONIUM NITRATE
NE           ADAMS         97            AMMONIUM NITRATE 
OK           ALFALFA      179.08      AMMONIUM NITRATE 
IA            ALLAMAKEE  182.13      AMMONIUM NITRATE
KS           ALLEN           1014.69   AMMONIUM NITRATE 
LA           ALLEN            118.78    AMMONIUM NITRATE 
 
Hopefully this is a better explanation.  
This is VERY simplified, but this is what I'm trying to help him do.  
Thanks! I initially thought maybe a nested function - VLOOKUP and IF, or 
even a visual basic program.  I don't know what to do.  Thanks for helping. 

-- 
taz0923


"tompl" wrote:

> Well, you have not provided much detail with which I can be more specific.  
> Where does the data come from originally?  Why is it in Excel.?  Why is it on 
> ten separate worksheets?  Is the format of the ten worksheets the same?  Why 
> do you want to put it back into Excel when you mentioned something about 
> sending it to another application? Maybe you could skip Excel altogether.  
> Keep the data in a table in Access, set up the query, then export the data in 
> a format that can be used by your other application.  Do you regularly append 
> data to the existing?  Do you get a completely new set of data periodically?  
> So many questions!
> 
> Tom
> 
> 
0
Utf
4/3/2010 8:16:01 PM
I have done this for clients. Here is a formula that uses a list of sheets 
from sheet LU col F.
=SUMPRODUCT(SUMIF(INDIRECT(OFFSET(LU!F1,,,COUNTA(LU!F:F))&"!B1"),"PMI",INDIRECT(OFFSET(LU!F1,,,COUNTA(LU!F:F))&"!A3")))
It can also be done using a defined name for the sheets.
=SUMPRODUCT(SUMIF(INDIRECT(ms&"!$a1:$z1"),"ttl",INDIRECT(ms&"!a"&ROW(A2)&":z"&ROW(A2)&"")))
or you can use macros
-
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"TriciaZ" <TriciaZ@discussions.microsoft.com> wrote in message 
news:788163CD-828A-48CB-9C12-7E278234E88A@microsoft.com...
> The data is from a spreadsheet created in Excel.  I am helping someone
> manipulate their data.  The data contains information from 7 different 
> states
> and many many counties within those states.  The format of the 10 
> worksheets
> is the same, yes.  The end user needs the data in Excel in a specific
> arrangement in order to send it to another application for analysis.  I do
> not know if Access would make this process any easier, in fact, the more I
> think about it perhaps not.  I do not know if the end user regularly
> manipulates this data or not.
>
> Basically, I have 10 spreadsheets full of data all set up like this:
>
> State        County           Tons         Commodity
> IA ADAMS     143.97     AMMONIUM NITRATE
> NE ADAMS     97     AMMONIUM NITRATE
> OK ALFALFA     78.08     AMMONIUM NITRATE
> OK ALFALFA     101     AMMONIUM NITRATE
> IA ALLAMAKEE   72.88     AMMONIUM NITRATE
> IA ALLAMAKEE   109.25     AMMONIUM NITRATE
> KS ALLEN     1014.69     AMMONIUM NITRATE
> LA ALLEN     118.78     AMMONIUM NITRATE
>
>
> The sheet needs to read:
> State        County         Tons          Commodity
> IA            ADAMS        143.97       AMMONIUM NITRATE
> NE           ADAMS         97            AMMONIUM NITRATE
> OK           ALFALFA      179.08      AMMONIUM NITRATE
> IA            ALLAMAKEE  182.13      AMMONIUM NITRATE
> KS           ALLEN           1014.69   AMMONIUM NITRATE
> LA           ALLEN            118.78    AMMONIUM NITRATE
>
> Hopefully this is a better explanation.
> This is VERY simplified, but this is what I'm trying to help him do.
> Thanks! I initially thought maybe a nested function - VLOOKUP and IF, or
> even a visual basic program.  I don't know what to do.  Thanks for 
> helping.
>
> -- 
> taz0923
>
>
> "tompl" wrote:
>
>> Well, you have not provided much detail with which I can be more 
>> specific.
>> Where does the data come from originally?  Why is it in Excel.?  Why is 
>> it on
>> ten separate worksheets?  Is the format of the ten worksheets the same? 
>> Why
>> do you want to put it back into Excel when you mentioned something about
>> sending it to another application? Maybe you could skip Excel altogether.
>> Keep the data in a table in Access, set up the query, then export the 
>> data in
>> a format that can be used by your other application.  Do you regularly 
>> append
>> data to the existing?  Do you get a completely new set of data 
>> periodically?
>> So many questions!
>>
>> Tom
>>
>> 

0
Don
4/3/2010 8:28:36 PM
Step One: All data must be in one table.  If the ten worksheets cannot be 
consolidated into one worksheet then the only option is to consolidate it 
into one table in Access.  For this example the worksheet should be named 
“All”.

Step Two:  Create a new worksheet with columns State, County, Tons and 
Commodity.  Row 1 should have these names.

Step Three:  Key in each possible combination of State, County and Commodity 
in the rows of the new sheet (Assuming Columns A, B and D).  Something like:

State        County         Tons      Commodity
IA            ADAMS                     AMMONIUM NITRATE
NE           ADAMS                     AMMONIUM NITRATE 
OK           ALFALFA                   AMMONIUM NITRATE 
IA            ALLAMAKEE               AMMONIUM NITRATE
KS           ALLEN                       AMMONIUM NITRATE 
LA           ALLEN                       AMMONIUM NITRATE


Step Four: Enter this formula in column C (Tons) Row 2, and then copy it 
down to the end of the used rows.


=SUMPRODUCT(--(All!$A$2:$A$65000=A2),
--(All!$B$2:$B$65000=B2),
--(All!$D$2:$D$65000=D2),All!$C$2:$C$65000)

Can’t get it on one line, but it is one formula.

That should do it.  You can then filter to exclude zero tons if that helps.

Tom

0
Utf
4/3/2010 9:39:02 PM
OP sent me one sheet so I wrote this for ONE sheet.

Option Explicit
Sub consolidateSAS()
Dim lr As Long
Dim i As Long

Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row

 Range("A1:d" & lr).Sort _
 Key1:=Range("B2"), Order1:=xlAscending, _
 Key2:=Range("A2"), Order2:=xlAscending, _
 Header:=xlGuess, OrderCustom:=1, _
 MatchCase:=False, Orientation:=xlTopToBottom

For i = lr To 2 Step -1
If Cells(i - 1, 2) = Cells(i, 2) And _
Cells(i - 1, 1) = Cells(i, 1) Then
Cells(i - 1, 3).Value = Cells(i - 1, 3) + Cells(i, 3)
Rows(i).Delete
End If
Next i
Application.ScreenUpdating = True
End Sub
-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"tompl" <tompl@discussions.microsoft.com> wrote in message 
news:E0673E5E-FB57-48A0-8345-0223AD35DA6D@microsoft.com...
> Step One: All data must be in one table.  If the ten worksheets cannot be
> consolidated into one worksheet then the only option is to consolidate it
> into one table in Access.  For this example the worksheet should be named
> “All”.
>
> Step Two:  Create a new worksheet with columns State, County, Tons and
> Commodity.  Row 1 should have these names.
>
> Step Three:  Key in each possible combination of State, County and 
> Commodity
> in the rows of the new sheet (Assuming Columns A, B and D).  Something 
> like:
>
> State        County         Tons      Commodity
> IA            ADAMS                     AMMONIUM NITRATE
> NE           ADAMS                     AMMONIUM NITRATE
> OK           ALFALFA                   AMMONIUM NITRATE
> IA            ALLAMAKEE               AMMONIUM NITRATE
> KS           ALLEN                       AMMONIUM NITRATE
> LA           ALLEN                       AMMONIUM NITRATE
>
>
> Step Four: Enter this formula in column C (Tons) Row 2, and then copy it
> down to the end of the used rows.
>
>
> =SUMPRODUCT(--(All!$A$2:$A$65000=A2),
> --(All!$B$2:$B$65000=B2),
> --(All!$D$2:$D$65000=D2),All!$C$2:$C$65000)
>
> Can’t get it on one line, but it is one formula.
>
> That should do it.  You can then filter to exclude zero tons if that 
> helps.
>
> Tom
> 

0
Don
4/3/2010 10:16:04 PM
Wow!
0
Utf
4/3/2010 11:06:01 PM
My concerns are 1; it only addresses one sheet and not the ten that OP has 
indicated and 2; it rather mutilates the data.  My philosophy is to 
manipulate data on a separate sheet so that new data can be pasted into the 
old data sheet.

I suspect that a complete solution would be needed rather than detailed 
support on specific issues in this case.

Tom

0
Utf
4/3/2010 11:32:01 PM
I got one sheet. I did one sheet..... Had I gotten 10 I very well may have 
consolidated FIRST.....

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"tompl" <tompl@discussions.microsoft.com> wrote in message 
news:636F8325-562B-4E16-A3AE-7E3C7F2AC261@microsoft.com...
> My concerns are 1; it only addresses one sheet and not the ten that OP has
> indicated and 2; it rather mutilates the data.  My philosophy is to
> manipulate data on a separate sheet so that new data can be pasted into 
> the
> old data sheet.
>
> I suspect that a complete solution would be needed rather than detailed
> support on specific issues in this case.
>
> Tom
> 

0
Don
4/4/2010 12:06:31 AM
If I could solve this I would, but I don’t think I can.

Tom

0
Utf
4/4/2010 2:15:01 AM
Reply:

Similar Artilces:

Report writer line length question
I am modifying a report and I am adding fields and trying to create the appearance of boxes using lines and the lengths do not adjust a pixel at a time. Using the mouse they move about 8 pixels at a time. This is very frustrating trying to line everything up correctly. I would appreciate any solutions to this problem. Al, Try highlighting the line (or click on the line) then press the CTRL or SHIFT Key then use the arrow keys. Hope this helps. Gerald "Al" wrote: > I am modifying a report and I am adding fields and trying to create the > appearance of boxes using lin...

Questions About CRM Data Copying and Synchronization between Serve
We have a 2 CRM3.0 Servers installed at disparate locations. The 2 servers contain both same and different data at the same time. For example, one server might contain the same user names and contacts as the other server, but it also contains users and contacts that do not exist on the other server and vice versa. The same also applies for other things: Leads, Opportunities, Products, .... etc My questions are as follows: Q1: If we are to keep the 2 servers as they are now, is there a way to schedule synchronizing the data between the 2 servers intelligently, so that both servers even...

question -- using a template
Hi, I downloaded the free "Our Organization's Telephone List" template for Excel from the Microsoft site. Once I get the information in, I would like to export this file to Outlook, as a group in the address book. From there, I want to export this group into my address book in Outlook Express (which I believe can easily be done). It seems like a lot of work to get this file into OE, but I don't know of any other way, or if this 'round about way will even work. The reason I want the information on this template is so I can share this list with another board member of ...

Question on Process.Start
I have a program that starts a second program with Process p = Process.Start(...). I expect p.Handle to hold the handle of the started process, which I then use in a series of SendMessage Calls, eg, SendMessage (p.Handle, 0x800+1, 0, 0). However, p.Handle is not the handle of the started process. I know this because, when the new process starts, it writes its own handle in the caption of the window, and it does not match p.Handle. Also, if I jigger the first program in the debugger, and use what I know to be the handle in the SendMessage calls, it works fine. What is going wron...

Excel 2003 - bar chart
Hi - I've got an Excel 2003 Bar Chart that's currently being "fed" with the data in a simple 2-column Table (Table 1) - column A is the last day of a given month, and column B is the total number of Events that happened in that month. The chart is simple: the X axis data points represent the months, by last day, and the Y axis data points represent the number of Events. I want to change this. I have a second, unrelated table (Table 2) from which I have been manually extracting or deriving the data that I've been inputting into Table 1. Table 2 is a multi-column list o...

Questions about Strange ReCalc Behavior
I'm currently struggling with an MOAS (that's "Mother of All Spreadsheets") that someone else at work has given to me. It's 28 MB and takes several seconds to re-calculate. I'm in the process of trying to diagnose ways in which I can make it more efficient (Ultimately I'll need to run it as part of a Monte Carlo simulation--25,000 recalcs, so every second is important). I've run into a couple of peculiar Excel behaviors. Can anyone explain these? 1) I can do an F9 recalc on any sheet. However, there are a couple of sheets (out of maybe 15) which, if I...

Double byte (Japanese) character question
Access 2003 I have a primarykey column. I am using text as primary key (ok not the best decision). The PK field contains Japanese double byte and single byte characters. For some reason, C and C are considered the same ("C") so I get a PK violation. C = double byte character C = single byte character Is there any way around this? I am listing file names in a folder, and using path + file name as PK. Basically in Explorer as file names, they are considered different. Being MS product, I don't know why it is not consistent in Access. JB ...

Outlook 2007
In Outlook 2003 you could view the 7-day work week calendar in a 2- column display instead of the 7 day (side-by-side calendar). Does anyone know if this is not available in Outlook 2007? Not everyone cares for the 7-day vertical calendar. Thanks, Linda. outlook 2007 does not have that view. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Outlook Tips: http://w...

refresh excel data weekly question
I have a multi-user spreadsheet that needs to get refreshed with new and updated excel data on a regular basis. My team keep notes and updates some fields/columns. My desire is to bring in the wholly new rows and the updated existing rows and not overwrite the fields/columns that my team is writing into. There is a column of unique data elements that would be considered a key field in a dbms. I could do this in 30 seconds with a database but I'm having trouble figuring it out in Excel (which I have to use). From what I've been reading it appears I have to set up the incoming new data ...

Question about User Form Window Size on Chip Petersons Site
I have a User Form that I need to adjust to fit the users computer. So far I not found anyway to get the User Form Window to Adjust. I can adjust the User Form sixe, but the Main Window the Form located is in is still bigger than the Screen. I saw on Chip Petersons site exactly what I need. His code allows complete control over the User Form just as if it was a regular window. Can anyone help me with this code? http://www.cpearson.com/Excel/formcontrol.aspx There are various ways, this is probably the easiest although it may hide the task bar Private Declare Function Se...

colors of bar charted data don't follow data after sorting
I created a bar chart that has 14 variables. I changed the color of 2 bars to yellow, 5 bars to blue, and 6 bars to red. When new data is ploted and sorted in a desending order. the colored bars don't follow the variable, they stay in their original location. I have to manualy change the colors to match my ledgend. Hi, The columns will not be sorted along with you data. You will be to change the colour of the columns after the sorting. You can do this manually or with code or with the use of formula and multiple data series. For the formula/multi series approach see this explanatio...

Exch 2003 "Directory Access" question
In our domain we have 3 domain controllers, 2 in our main site and 1 in a remote site. When I go to Exchange System Manager->Servers->EXCHServer and right-click it and then go to Properties. On the "Directoy Access" tab it shows the various domain controllers used by Exchange. None of the two domain controllers in the our main site are listed, just the one at the remote site.The exchange server is IN the main site. I could add them "manually", but the warning message indicates that this is not a suggested method. Anyone have any ideas of what is happening here...

dialog based implementation
The member function OnExtraInit will place two dialogs (dlg1 and dlg2) side by side. dlg1 refers to the dialog pertaining to the class trs_lsr_cmd_dlg while dlg2 refers to the dialog for class trs_lsr_status_dlg. So now: trs_lsr_cmd_dlg::trs_lsr_cmd_dlg(trs_lsr_ctrl_dlg_main* pParent /*=NULL*/) : CDialog(trs_lsr_cmd_dlg::IDD, pParent) { m_hIcon = AfxGetApp()->LoadIcon(IDI_LSR_TRS_ICON); VERIFY(ptr_trs_lsr_status_dlg = new trs_lsr_status_dlg(pParent)); VERIFY(ptr_trs_test_dlg = new trs_test_dlg(pParent)); m_pParent = pParent; } trs_lsr_cmd_dlg::~trs_lsr_cmd_dlg() { if (ptr_trs...

Exchange 5.5 to 2003 co-exist question
Hello. We've been performing testing and doing research on a migration procedure from Exchange 5.5 to 2003 and have some odd results. The current system is a straightforward single org, single site 5.5 system with about servers and 2000 users. We will be performing an intra-org migration. The NT domain will not be upgraded, instead trusts will be set between the NT and 2003 domains. It's not going to be a big bang approach but instead a rolling migration over a couple of months. Documentation and postings point out not to enable ADC created place holders, no problem with tha...

Licence question 02-14-10
Hi, I just bought a new laptop to replace my old one since it had some faluts. For this reason I would like to install office 2007. I know that you can install ms office in 3 pcs, which I already have, my question is: If I uninstal office from my old PC, can i install ut in my new PC or should I buy office again. I dont want that my licence is catalogued as pirate. The answer depends on which edition of Office 2007 you have now. If you have Home and Student, you can install it on three computers. If you have Professional, I believe it is one desktop and one laptop. If it i...

How to SUM comma separated list?
I have a form where users can select any number of transactions from a datasheet. As they select the transactions, a coma separated list of Transactions IDs is created, like this: 3349, 3221, 503, 30, 21893, 8320, Each Transaction ID has a corresponding currency "Amount" in the Transactions table. I need to calculate a total value for all selected transactions. I thought about keeping a running total as the selections are made, but the list can be edited (in a text box) before the "Calculate" button is clicked, so that doesn't work. Can I somehow put the lis...

Mail Merge List Question
Is there a way to export to Excel the "mail merge recipients" list? Paul wrote: > Is there a way to export to Excel the "mail merge recipients" list? Publisher will not allow you to export to Excel. How did you bring your current data into Publisher? Are you using the built in Publisher Address List feature? If so, that is an *.mdb file, which can be opened in Access. You could then export that to Excel. By default, this is stored in the My Data Source folder. But when you initially create this database, you are given the option to choose which ever location you ...

Money 06 Budget issues and questions... I'm new and lost here.
I've been trying to set up the budget features of Money '06 using the Advanced budget. I started using Money 06 this month with a new data file, and downloaded transactions from my FIs back to the first of the year. I've been seeing weird issues... For my mortgage payment... it's set up as a recurring bill that happens monthly on the 5th of each month. However, in the budget for Feb. it shows that there's nothing budgeted for it (and thus, I'm over budget). For March, the budgeted amount for mortgage is double. I have a weekly expense on Sunday... February&...

Doc/View question
I have reached a point where I need input on the following: For some time I have used MFC created apps with Doc/View and made my base view class as CFormView. It was convenient and quick, but I was putting all of my data variables in the ViewClass since that was where the class wizard put all my control variables and it seemed convenient in my ignorance. If I had to access files I used ofstream from within my view class. So now it has become apparent that I have things backwards (so it appears from what I read ) in that I should have been creating data variables in the CDocument...

routing questions
New to 2003 and I'm not sure how to handle routing. Our current 5.5 environment has 2 bridgehead servers - passing mail to about 26 sites, little over 8,000 mailboxes worldwide. Currently we have only 1 Exchange 2003 server for mailbox and routing purposes. As we add these other sites (have not done any yet), I'm concerned that this one box can't handle the load of mail traffic. What is the best practice for this - my management won't bring any "professional" help in and sent me to one class, over a year ago so I'm floundering here. Our current server is go...

Canadian Payroll Year End Question
We did the year end on our system today for Canadian Payroll. Everything went fine – no error messages. However, I just saw that one of the current tables was not cleared out after the records were moved to the history table. The two tables involved are CPY30210 and its history table CPY60210. Everything moved over to CPY60210 okay so can I manually delete all the 2009 records out of CPY30260? Thanks -- Pieter CPY30210/CPY60210 (Cheque Master for current year) CPY30210 should be empty after year end procedures are completed. Your second part on the note indicates CPY...

Sorting Question #4
I would like to read data on one page, automatically sort it, and print it on another worksheet in the same workbook without changing the data on the original page. This is information for an announcer at a sporting event, and I do not want the announcer to have to do manual excel operations, but I could use a button for a VB sort if I had to. I would prefer not to. Here is the scenario. I have several columns of data on the second worksheet in a workbook. The data is in columns C, Column D, Column Y, and column Z. The data in each of these columns has been calculated from data entered ma...

SMS Gateway for Exchange -Brief questions
Hi, Not looking for anything in-depth just a general outline of an SMS Gateway for Exchange. I've been asked to look into it in our company so we can email a text message so it goes to user.mobile@companyname.com or similar. We could use this for when servers go down etc (we currently have something similar but it is very basic and limited and sends to one address - we want something that integrates with Exchange 2003 and is all professional - and without any lag/delay if possible. My questions are: Where do you set it up - on the actual Exchange Server (2003)? What are the costs ...

simple question
I want to display a value from one of many cells Q1-395 in cell F13, depending the value in cell E13 example: E13=60 so F13 displays whats in Q61 F13 = Q(E13+1) How do I write this correctly? appologize, if this could be found in a search, I tried but had no idea what to search for -- SnowPlow ------------------------------------------------------------------------ SnowPlow's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23785 View this thread: http://www.excelforum.com/showthread.php?threadid=374432 Try the INDIRECT() function In F13 type: =INDIRE...

Question about sumproduct
I understand the standard use of "SUMPRODUCT". But the following application confused me. =SUMPRODUCT((A1:A10="Ford")*(B1:B10="June")) What this function does is to count the number of Fords sold in June. I have no idea how it works based on my understanding of basic use of the function. Any help will be appreciated. Jason (A1:A10="Ford") becomes a logic function equaling true for each cell A1 to A10 which has Ford in it. and false when it does not the same logic for (B1:B10="June") in calculations True is treated as a 1 and false is...