How can I use the value in one cell inside the formula in another cell?

[wscott]

Here's my example:

In cell A1 I have the formula =sum($b$1:$b$8)

I want to replace ONLY THE 8 above with a cell reference...for example in cell D1 I have the number 9. How can I get that number 9 inside my formula by just referencing D1?

Thanks!

 

[Smitty]

Welcome to the Board!

You can use INDIRECT:

=SUM(INDIRECT("$B$1:$B$"&D1))

HTH,

 

[wscott]

THANKS! that did it. I knew I needed to use the Indirect function, but I couldn't get the syntax right. Thanks again!

 

[Rick Rothstein]

You can use INDIRECT:

=SUM(INDIRECT("$B$1:$B$"&D1))

Since the B cell "references" are inside quote marks, you can omit the $ signs and save 4 characters...

=SUM(INDIRECT("B1:B"&D1))

 

[Magriza]

An alternative to using INDIRECT as it's a volatile formula:
=SUM(OFFSET($B$1,0,0,$D$1))

 

[ColdGeorge]

Since the B cell "references" are inside quote marks, you can omit the $ signs and save 4 characters...

=SUM(INDIRECT("B1:B"&D1))

Hi Rick

How is that? Would you mind to explain it?

Thanks

ColdGeorge

 

[Rick Rothstein]

Hi Rick

How is that? Would you mind to explain it?

Anything in quote marks is just text... it may look like cell references to you, but to Excel it is just characters grouped to together that have no meaning other than the individual characters themselves (it is the INDIRECT function's job to give meaning to those characters)... when you copy a cell with text in it, that text does not change, so the absolute referencing $ signs (to keep the cell references from changing when the formula is copied) are not needed because the text inside the quote mark will never change when the formula is copied.

 

[ColdGeorge]

Thanks Rick

Clear enough

ColdGeorge

 

[pbessett]

Hi I have a similar question but it's summing across multiple wooksheets in the same workbook:

=SUM('1:2000'!E17)

I want to change the 2000 value to link into a specific cell (A1) or (NewSheet!A1). The cell will contain the correct figure I want to display weather it be 2000 or 2001 or otherwise.

 

[pbessett]

Hi I have a similar question but it's summing across multiple wooksheets in the same workbook:

=SUM('1:2000'!E17)

I want to change the 2000 value to link into a specific cell (A1) or (NewSheet!A1). The cell will contain the correct figure I want to display weather it be 2000 or 2001 or otherwise.

Anyone have a guess?