Variable in a formula

Posted by Duane Kennerson on December 14, 2001 1:56 PM

Is it possible to put a variable or better yet, multiple variables in a cell formula. I don't want to use VBA in this instance.

Thanks,

Duane

Posted by Aladin Akyurek on December 14, 2001 2:03 PM

Duane --

All cell refs in a formula are "effectively" variables. What do you have in mind?

Aladin

Posted by Duane Kennerson on December 14, 2001 2:10 PM

For instance, I want to select a range on cells from another sheet in the workbook. The formula would look something like this:

sum(sheet1!$B$1:B & x)where x would be the value of another cell.

or better yet, forget the variable, how about something like this:

sum(sheet1!$B$1:B & 7*B3) where range b3 = 2 so that my end result would be range (b1:b14) I can't seem to make any of these work. Any suggestions???

Duane

Posted by Mark W. on December 14, 2001 2:17 PM

=SUM(INDIRECT("Sheet1!B1:B"&7*B3))

...although this is a pretty strange formulation
since the value used to determine the extent of
the range to be summed is being summed itself.

Posted by Duane Kennerson on December 14, 2001 2:20 PM

:Sorry, that is a little unclear on my part. B1:B (whatever) is on sheet1. B3 is on the active sheet. That probably complicates things, doesn't it?

=SUM(INDIRECT("Sheet1!B1:B"&7*B3))

Posted by Mark W. on December 14, 2001 2:49 PM

> That probably complicates things, doesn't it?

Nope, just enter this formula on a sheet other
than Sheet1.

Posted by Duane Kennerson on December 14, 2001 2:58 PM

Re: Works perfect, thanks.