Dynamic Range - Cell references changing in OFFSET function

happyrachael

New Member
Joined
Aug 26, 2011
Messages
2
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"))
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Marcol

Well-known Member
Joined
Mar 1, 2010
Messages
644
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

New Member
Joined
Aug 26, 2011
Messages
2
Oh my goodness - of course. Thanks. Glad someone's brain is working this morning!

Thank you both.

R
 

Watch MrExcel Video

Forum statistics

Threads
1,095,369
Messages
5,444,057
Members
405,264
Latest member
JohnP1972

This Week's Hot Topics

Top