View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
apache007 apache007 is offline
external usenet poster
 
Posts: 36
Default SUMIF function and cell /sheet reference

Dear Excel Experts,

I have the following tables on my sheet

11 Mar 09 SUP1 $30
1 Apr 09 SUP1 $50
2 Apr 09 SUP3 $10
4 Apr 09 SUP2 $20
16 Apr 09 SUP1 $100

I have 2 general excel questions:

1. How do I use SUMIF function using 2 criterias? For an example, I want to
SUM the amount that is purchased from Supplier 1 in April. Or is there
another formula that accomodate this?

2. In excel, you have 2 sheets called Apple and Orange.
In Apple sheet, a cell A1 is referenced to A1's Orange sheet.
Dragging A1 down, will auto-fill the rest of the coloum referencing
Orange Sheet.

Now, if I have 10 sheets (Sheet1-Sheet10).
On Sheet1 - A1 cell, I reffence Sheet2-A1. (=Sheet2!A1)

How do auto-fill automatically, so that it changes betwwen Sheets, and not
the cell.

Result
Sheet1 - A1 Cell refference to Sheet2- A1 Cell
Sheet1 - B1 Cell refference to Sheet3- A1 Cell
Sheet1 - C1 Cell refference to Sheet4- A1 Cell
Sheet1 - D1 Cell refference to Sheet5- A1 Cell

or

Sheet1 - A1 Cell refference to Sheet2- A1 Cell
Sheet1 - A2 Cell refference to Sheet3- A1 Cell
Sheet1 - A3 Cell refference to Sheet4- A1 Cell
Sheet1 - A4 Cell refference to Sheet5- A1 Cell

Thanks a bunch for the help.