For the double-click method to work, you must have data in adjacent
columns (either left or right). However, you can hide columns I to L
temporarily, and then the double-click will work. I find it easier to
double-click on each formula, rather than highlight all formulas M to
T and double-click that, as Excel can come back to say not enough
resources available.

Hope this helps.

Pete

On Oct 26, 6:50 pm, thewizz wrote:
I tried as you suggested, however, I must not be doing something correctly.
Here is what I have:

I imported (a small example) 500 rows of data that are in columns A - H. My
formulas are in columns M-T. I put only one row of formulas in to begin with
and tried to double click on the fill handle, but nothing happened.

Am I doing something wrong?

Thank you again!

--
I am not where I intended to go, but I think I am where I am supposed to be!



"Pete_UK" wrote:
You can use INDIRECT in answer to your question #1, something like:

=SUM(INDIRECT("A1:A"&B1+1))

Not sure why you can't put 11 in B1 if you want to include row 11,
then you wouldn't need the +1 at the end of the formula. <bg

As for question #2, why do you say that the file is too big to start
with? Do you need more memory in your PC? I regularly import telephone
call data (maybe 40,000 calls or more) into a template I have set up,
where I just have the formulae on the top row - once the data for a
new month is imported, I just copy the formulae down for as many rows
as I need. You can do this by double-clicking the fill handle (the
small black square in the bottom right corner of the cursor), or by
running a simple macro to do this for you (it can easily detect how
much data you have). The template is quite a small file, but obviously
grows when data is added and formulae copied down - it pays to fix the
values of those formulae.

Hope this helps.

Pete

On Oct 26, 4:18 pm, thewizz wrote:
I actually have 2 questions:
#1 - I would like to define a range (Say A1:A?) by using a number that is
stored in a cell (say B1) where B1 can be change if I want to look at a
different range (shorter or longer). Ex: if B1=10 then range would be A1:A11
if B1=6 than the range would be A1:A7....

#2 - This one will probably involve VB which I am not very familiar with (I
have used it in the past but not often enough to remember).

I would like to populate field of rows and columns depending upon data that
I import and how much data is there. Now I have a formula copied into about
30,000 cells in a column that calculate the data that I import, but the Excel
file is way to big before I even start.

Thank you for any help!

--
I am not where I intended to go, but I think I am where I am supposed to be!- Hide quoted text -

- Show quoted text -