Formula for named range

[Guest]

My data is:
cell a2=24/jun/2006
cell a3=01/jul/2006

column B(b2:b9)
-------------------
21/jun/2006
22/jun/2006
23/jun/2006
26/jun/2006
27/jun/2006
28/jun/2006
29/jun/2006
30/jun/2006
I would like name a range in col.B by looking date values from col
A(a2:a3).As per my above data I want to name a range $b$4:$b$9.I am looking
formula to define the range name,what should I write in 'refers to' box.

 

[Biff]

Hi!

I have no idea how you intend to use this so I can't guarantee this for
robustness:

=OFFSET(Sheet1!$B$2,MATCH(Sheet1!$A$2,Sheet1!$B$2:$B$9)-1,,MATCH(Sheet1!$A$3,Sheet1!$B$2:$B$9)-MATCH(Sheet1!$A$2,Sheet1!$B$2:$B$9)+1)

Use the appropriate sheet name.

Biff

 

[Guest]

The RefersTo property is normally in the form of an equation and includes the
sheetname and the cell range, so it would look something like this:
='Sheet2'!$B$4:$B$9
if that gives an error, enter it without the = symbol.

An easier way (for me at least) is to choose the cells I want to give a name
to, and they don't even have to be cells that are parts of a group you could
select random cells on one or more sheets, then go to the Name Box and type
in the name for the range and press the [Enter] key.

The Name Box is that area to the left of the formula bar which normally
shows the address of the current cell, like A1 when you have A1 selected.
You must use the [Enter] key to terminate the name entry there otherwise it
is discarded by the system.