Use a formula to define a range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
Dynamic named range:

Insert -> Name -> Define.

Create a name and type in the Refers To box:

=OFFSET(Sheet1!$A$1,0,0,Sheet1!$B$1,1)

Which will use B1 as the row count starting in A1.

If you type in, say, 100, in B1, then go to the VBE in the immediate window:

?range("MyRng").Address

result:
$A$1:$A$100
 
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
 
Your response to question 2 realy answers my question. Now my template has
30,000 cells (by about 10 columns of different formulas) with formulas
already there. By doing what you said that makes it much easier. I didn't
realize excel would detect the data that easy! I will give that a shot!

Thank you!
 
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!
 
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
 
Back
Top