Does the infinite symbol work in Excel?

[ssalazarjr]

I would like to use the infinite symbol "∞" in the height portion of my OFFSET Function. Or any other symbol, function or ideas that will work. I put an example of what I'm trying to accomplish.

=MATCH(B2, OFFSET(A1, 30, 0, "∞", 1), 0)

so

=MATCH(B2, A31:A"∞", 0)

 

[PaddyD]

no, but there are ways to dynamically define ranges to size to the used area. search the board for 'dynamic named range' for examples, post back if you can't get it sorted

 

[Colin Legg]

Hi,

It's a nice idea but I don't think you'll be able to make it work. Excel doesn't have an infinite number of rows, so making it into a valid range reference would be tricky.

There are various alternatives though. You can use formulas which calculate the number of used rows and automatically expand or contract as the volume of data changes, or you can just reference the largest row number in the sheet (eg. 65536 in XL 2003 or earlier).

Does that help?

 

[ssalazarjr]

it does, so what what is the largest number of rows you can have in Excel 2007?

 

[Colin Legg]

1048576