Forum home » Delegate support and help forum » Microsoft Excel Training and help » Extending Named Range

Extending Named Range

ResolvedVersion 2003

Wed 20 Jan 2010, 15:06 Delegate Shreyas said...

Shreyas has attended:
Project Intro Intermediate course
Excel Intermediate course
Excel Advanced course
Visio Advanced course
Project Management - Framework & Processes course
Presentation Skills course

Extending Named Range

How do you extend a named range?

Sun 24 Jan 2010, 14:21 Trainer Stephen said...

RE: Extending Named Range

Hi Shreyas

Thanks for your question

Create a named range by going to Insert-Name-Define.

Give your ranage a name and in the refers to box type the following equation

=OFFSET($B$1,0,0,COUNTA($B:$B),1)

This uses the CountA function nested within the Offset function.The offset function specifies a start point of cell B1 and a finish point based on the number of populated cells in column B.

Hope this helps,

Regards

Stephen

Sun 31 Jan 2010: Automatically marked as resolved.

Excel tip:

Conditional formatting for cells that return text , not picked up by Go to command

If you have tried to format all cells containing text even those that display text as a result of a formula you may have had difficulty. As Go to command with constant selected does not pick up formulas that result in text.

Then try this. Select the range the formula cells appear in on your sheet. Format, select Conditional Formatting menu. In the dialog box under Condition 1, select "Formula Is" from drop down. Next to it in the Formula Box, enter the formula =Istext(A1. Click Format button , choose desired formatting settings and click OK. To go ahead and apply the conditional formatting click OK to accept

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Extending Named Range

Extending Named Range

RE: Extending Named Range

Excel articles

Excel tip:

Conditional formatting for cells that return text , not picked up by Go to command