Excel Formula to Reference a Value

[SuzanneColson]

Let's say I have a simple spreadsheet (the real one has over 100 columns and 5 years of data by month).

It would look something like this:
B C D
1 Sales Labor Hours
2 Jan 2000 4 6 4
3 Feb 2000 6 7 5
4 Mar 2000 5 6 4
5
6
7

I have a summary section that adds or performs a calculation on let's say a range of months. There are actually 2 columns. Basically comparisons. I would like to be able to setup something that allows me to easily changes the formulas to change the months that are being compared.

For example, I want to compare rows 2 to 3 against rows 3 to 4 and now I want to compare rows 2 to 3 against rows 5 to 6.

@SUM(a2:a3). Can I create a formula that does something like @SUM(a(the value placed in another cell):a(the value placed in another cell))

I hope this makes some sense?

Any feedback would be greatly appreciated.

Thanks,

Suzanne

 

[Bowers74]

Hi Suzanne,

You need to look in the Help File (F1) for information on the following functions to understand why the following formula works:

INDIRECT()
ADDRESS()
CONCATENATE() or &

=SUM(INDIRECT(ADDRESS(B1,1,4)&":"&ADDRESS(B2,1,4)))

Where Cell B1 contains the first row number and cell B2 contains the second row number. The 1 in both ADDRESS() functions is for Column A and the 4 is to set the reference to absolute.

The INDIRECT() function "creates" a cell reference from text that is being created through the ADDRESS() functions and the CONCATENATE() or & function (with the colon ":")

I hope this helps!!



Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[SuzanneColson]

That looks like it will do it. I knew it could. I searched help a dozen times and never saw these functions. You are a timesaver and a lifesaver.

Thanks again!

Suzanne