Thanks:  0
Likes:  0

# Thread: Referencing a complete column

1. I use a 2 sheet workbook. The first sheet has a list of sales figures associated with salesmen. On the 2nd sheet I have a summary of the sales - total, average order value etc. I use a simple formula

That works fine on a regional basis but on a national basis the sales could very in number form 1000 to 20,000. How can I alter the formula so that it looks at the whole of columns C and F?

Dave

2. Dave,

C:C and F:F should do it.

However, this will look at all 65,000 cells in both cases and may slow down the calc

Have a look at dynamic ranges : Dave has some good examples at Ozgrid

http://www.ozgrid.com/Excel/DynamicRanges.htm

_________________
Hope this helps,
Chris

[ This Message was edited by: Chris Davison on 2002-03-17 06:44 ]

3. On 2002-03-17 06:21, daveray wrote:
I use a 2 sheet workbook. The first sheet has a list of sales figures associated with salesmen. On the 2nd sheet I have a summary of the sales - total, average order value etc. I use a simple formula

That works fine on a regional basis but on a national basis the sales could very in number form 1000 to 20,000. How can I alter the formula so that it looks at the whole of columns C and F?

Dave
Dave,

There are 2 methods you can use to cope with the ever changing data area.

[1] Compute dynamically the relevant ranges in the summary sheet from within the SUMIF formulas that you use, or
[2] Name the dynamically computed relevant ranges via Insert|Name|Define.

Method 2 makes the named ranges in all worksheets of your WB, while Method 1 keeps them local to the summary sheet.

Lets take Method 1 first.

In say A1 in the summary sheet enter:

Note 1. MATCH is fast enough and looks just at one column, so you don't have to worry about that.

Now you can (re)write your example SUMIF formula as:

A corresponding AVERAGE formula would be:

Method 2.

Activate the option Insert|Name|Define.
Enter EndRow as name in the Names in Workbook box.
Enter as formula in the Refers to box:

Enter as name, e.g., Orders in the Names in Workbook box.
Enter as formula in the Refers to box:

Note that this defines a dynamic range as one-column range (that is, a dynamic range in F of Orders Received).

Enter as name, e.g., Amounts in the Names in Workbook box.
Enter as formula in the Refers to box:

Click OK.

Note 2. You need for every relevant subarea of data area to set up a name and a formula.

After having created named dynamic ranges, you can (re)write your example SUMIF formula as:

=SUMIF(Orders,"Accepted",Amounts)

A corresponding AVERAGE formula would be:

=SUMIF(Orders,"Accepted",Amounts))/MAX(1,COUNTIF(Orders,"Accepted"))

[ This Message was edited by: Aladin Akyurek on 2002-03-17 08:47 ]

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•