Entering a cell value in a reference

  • Thread starter Thread starter !Jerry
  • Start date Start date
J

!Jerry

Win98SE / Excel 2000 / Basic level user

I have what I think must be a fairly simple question...

I have our sales figures for the past 3 years entered on a spreadsheet in
columns. Column A has figures for 2004, B is 2003, etc. There are 52 rows,
one for each week of the year. I use the figures in B, C and D to help
predict what our sales are going to be this year based on previous years
performance to date.

I would like to enter a number in one cell and use it as part of the cell
reference in other cells. For example, I would enter "4" in one particular
cell and then have formulas that would calculate the sum of cells up to the
4th week (=sum(B1:B[value in other cell]).

I don't know if this will complicate the issue, but my actual weeks are four
rows greater than the row number (Week 1 is in Row 4 and Week 4 is in Row 8)
so I'm guessing my formula would be =sum(B4:[value in other cell]+4.

Can anyone offer any suggestions?

Thanks in advance.

J Alderton
(e-mail address removed)
 
Hi Jerry
try
=SUM(INDIRECT("B1:B" & A1))
where A1 stores your value (e.g. 4)
or for a different starting row try
=SUM(INDIRECT("B4:B" & A1+4))

HTH
Frank
 
Thanks!!! That worked perfectly. I really appreciate your help (and the
speed at which you provided it!).

J Alderton

Frank Kabel said:
Hi Jerry
try
=SUM(INDIRECT("B1:B" & A1))
where A1 stores your value (e.g. 4)
or for a different starting row try
=SUM(INDIRECT("B4:B" & A1+4))

HTH
Frank

!Jerry said:
Win98SE / Excel 2000 / Basic level user

I have what I think must be a fairly simple question...

I have our sales figures for the past 3 years entered on a
spreadsheet in columns. Column A has figures for 2004, B is 2003,
etc. There are 52 rows, one for each week of the year. I use the
figures in B, C and D to help predict what our sales are going to be
this year based on previous years performance to date.

I would like to enter a number in one cell and use it as part of the
cell reference in other cells. For example, I would enter "4" in one
particular cell and then have formulas that would calculate the sum
of cells up to the 4th week (=sum(B1:B[value in other cell]).

I don't know if this will complicate the issue, but my actual weeks
are four rows greater than the row number (Week 1 is in Row 4 and
Week 4 is in Row 8) so I'm guessing my formula would be
=sum(B4:[value in other cell]+4.

Can anyone offer any suggestions?

Thanks in advance.

J Alderton
(e-mail address removed)
Click to expand...
Click to expand...
 
Hi Sparky
you're welcome
Frank
Thanks!!! That worked perfectly. I really appreciate your help (and
the speed at which you provided it!).

J Alderton

Frank Kabel said:
Hi Jerry
try
=SUM(INDIRECT("B1:B" & A1))
where A1 stores your value (e.g. 4)
or for a different starting row try
=SUM(INDIRECT("B4:B" & A1+4))

HTH
Frank

!Jerry said:
Win98SE / Excel 2000 / Basic level user

I have what I think must be a fairly simple question...

I have our sales figures for the past 3 years entered on a
spreadsheet in columns. Column A has figures for 2004, B is 2003,
etc. There are 52 rows, one for each week of the year. I use the
figures in B, C and D to help predict what our sales are going to be
this year based on previous years performance to date.

I would like to enter a number in one cell and use it as part of the
cell reference in other cells. For example, I would enter "4" in
one particular cell and then have formulas that would calculate the
sum of cells up to the 4th week (=sum(B1:B[value in other cell]).

I don't know if this will complicate the issue, but my actual weeks
are four rows greater than the row number (Week 1 is in Row 4 and
Week 4 is in Row 8) so I'm guessing my formula would be
=sum(B4:[value in other cell]+4.

Can anyone offer any suggestions?

Thanks in advance.

J Alderton
(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top