Dynamic Range - Cell references changing in OFFSET function

[happyrachael]

Hi,

I'm working in Excel 2010. I'm trying to create a dynamic range, using the OFFSET function. I've got it working beautifully but as soon as I save it and close the Name Manager the range stops working. When I go back into Name Manager I find all the cell references have changed (from C1:C600 to C1048572:C595 - or some other strange range).

I don't seem to be clicking away while I'm in the Name Manager. I just can't work out what's happening. Can anyone help?

Thanks

Rachael

P.S. What it's doing isn't so important as when the cell references are right it works. But just for full info: It's an OFFSET function, starting at A1. It will look at a list of companies and use a Match function to find out how many rows to offset (based on where the first instance of a particular company. And it uses the COUNTIF to make the range the same number of rows as there are entries for that company.

Here is my broken function:
=OFFSET('Map Point'!A1048572,MATCH("Company Name",'Sheet Name'!C1048572:C595)-COUNTIF('Sheet Name'!C1048572:C595,"Company Name"),0,COUNTIF('Sheet Name'!C1048572:C595,"Company Name"))

 

[theBardd]

Absolute them

$C$1:$C$600

 

[Marcol]

Or make C1:C600 a Dynamic Named Range

Name:= Company_Name

Refers to:=
='Sheet Name'!$C$1:INDEX('Sheet Name'!$C:$C,MATCH(REPT("z",255),'Sheet Name'!$C:$C,1))

Assuming that the company names are all text strings.

You might need to change $C$1 to $C$2 to allow for a header row.

 

[happyrachael]

Oh my goodness - of course. Thanks. Glad someone's brain is working this morning!

Thank you both.

R