increasing 'data tab' range in XL

[presto]

not sure of the correct lingo - but here goes.

in my spreadsheet i have different worksheets for different systems, and have one worksheet that breaks down and analysis each worksheet a 'stats' page.

now on the stats page i use a 'VLOOKUP' formula - to gather data from the different worksheets.
the problem is the 'data range' on each worksheet isn't big enough, and i was wondering how do i increase the size of the data range?

i have tried:
insert: name: define - then altering the 'row' number. but this just comes up as an error.

i am sure there is a simple way of just increasing the size - but can't remember how ;fire

anyone any ideas how to do it?

[MarcusMel]

Try double clicking on the formula - you should get areas highlighted and you can use the mouse to adjust the size of the highlighted area.

[presto]

thanks MM - tried that, when i dragged the highlighted area over it just 'overwrote / deleted' the cells underneath.

for now i have just inserted another 'bigger' data tab range over the existing one.

[markwales]

Hmmm I always use Name -> Define

You need to remember to click OK after changing the data range, rather than just pressing enter...

[vegyjones]

Oi, Walsey, aren't you supposed to be on your way to Cardiff now, yaky dar??

[presto]

thats what i tried mark, but the 'formula contains error' box pop's up. dam stupid computers ;fire - how can there be an error by adding a didget to a formula that already works :doh

ah well - it can still compute 14600 rows of data quicker than me :)

[markwales]

Oi, Walsey, aren't you supposed to be on your way to Cardiff now, yaky dar??

I'm at my parents house, 12 miles from Cardiff :D

Thought I'd get ahead of the game and miss the traffic.

[vegyjones]

You jammy git.

Still your loss.
Your going to miss all the celebrations at Brisbane Road tomorrow!

Latest news is that

SUPER CARL GRIFFITHS will be there.
And the clubs chaplain Reverand Alan Comfort will also be playing!

[MarcusMel]

Must be a definition your missing or a circular argument. It is not something I use very much.

[MarcusMel]

Does not the data need to be and order for Vlookup?

[presto]

i know what i did wrong.

a data range has a maximum of 65k rows

i was just adding an extra didgit - so the 120000 was above the max allowed :rolleyes:

[presto]

well thats what my dad reckons - will try it out

[presto]

yup - get the error message when i increase 'data range' above 65k rows.

i thaught XL could handle an infinate number :doh

[vegyjones]

Maybe you need XXL

[MarcusMel]

Your in database sizes now! - :yikes:

[MarcusMel]

Being nosey what an earth are you doing with that size of lookup?

[mathare]

Any Excel worksheet has a max of 256 columns and 65536 rows so you can't use more than that in any formula.

A1:IV65536 is the cell range

[presto]

Being nosey what an earth are you doing with that size of lookup?

just doing my betting bank SS
since jan this year the lays have taken up 12,600 rows :yikes: on one worksheet.

the 'lookup' is just to shorten that on the stats page to a much more friendly 365 rows (1 year) view of the main usefull data, profit, SR etc....

i didn't really need the full 120,000 data range. i just added a '0' to the 12,000 rows data range.

[mathare]

since jan this year the lays have taken up 12,600 rows :yikes: on one worksheet.I would suggest your spreadsheet needs some tidying up and slight redesign.

How many rows does each lay take up? And how many rows do you use, if any, for end of day/month data?

[presto]

when i made the SS in jan - i left around 50 rows for the 'lay selections' per day, last year that would have been about right - but with less selections this year there was quiet a few spare rows, a week or so ago i reduced the numbers - so it should be more economical on the space now.

[mathare]

I was going to say I have only used around 3000 rows for lays this year...

[Onlyforfun]

I'm on exactly 4001! Although a few will be knocked off tonight with over cut-offs etc.

[GlosRFC]

120,000? That's a bit optimistic...it's more than 328 bets per day!! With an average of four meetings per day, that's a race every 15 minutes over a 24 hour period. Over the standard duration of a race meeting, those poor nags are thundering out of the stalls every one and a half minutes...you must be laying on one furlong sprints :D