Using one cell's value as a column value in an address of a formula

Hi,

I am trying to create a formula with a range that is variable based on a cell entry. ie; if I enter 20 into a cell, my formula range is A1:A20. Where A20 is derived from the 20 entered in another cell.

Thanks in advance.

Hi
if you enter this value in B1 use for example
=SUM(INDIRECT("A1:A" & B1))

--
Regards
Frank Kabel
Frankfurt, Germany

"JDL" schrieb im Newsbeitrag
...
Hi,

I am trying to create a formula with a range that is variable based
on a cell entry. ie; if I enter 20 into a cell, my formula range is
A1:A20. Where A20 is derived from the 20 entered in another cell.

Thanks in advance.

Now coming up with a #VALUE?

I am using J7 to enter a value, ie; 20. Here is my formula: =SUM(INDIRECT("E3:E" & J7))

Any thoughts?

Hi
are there any errors in this range E3:E20

--
Regards
Frank Kabel
Frankfurt, Germany

"JDL" schrieb im Newsbeitrag
...
Now coming up with a #VALUE?

I am using J7 to enter a value, ie; 20. Here is my formula:
=SUM(INDIRECT("E3:E" & J7))

Any thoughts?

This function appears to work on entered values but not calculated values. Is this correct?

No reported errors. I can create a new spreadsheet using your formula and it works.

Hi
what are your exact formulas
- the INDIRECT formula
- the formulas in the range

--
Regards
Frank Kabel
Frankfurt, Germany

"JDL" schrieb im Newsbeitrag
...
This function appears to work on entered values but not calculated
values. Is this correct?

I just copied and pasted the old worksheet cells to a new wookbook. It now works. I have to assume the file which was originally created by another was of an old version or something. Your initial response has resolved my issue.

Thanks Frank.