Interesting problem - Excel 2007 Offset formula set for range but returning valu

andrewbroadway

New Member
On the tab named "Dashboard" I am playing with the offset function to return a range of rows from the "Missed Time - AD Usage' tab.

=OFFSET('Missed Time - AD Usage'!\$B\$5,0,1,92,1)

That is the formula I am using. So I am starting at B5, moving 0 rows down, 1 column to the right, the height is 92 and I only want 1 column.

Yes instead of returning #VALUE, the formula actually gives me whats in cell C15.

I am so frustrated! What am I doing wrong?

Hui

Excel Ninja
Staff member
Andrewbroadway

You rarely use offset by itself except to return a single cell

Normally offset is used to return a range and you need to do something with it

eg:

=Sum(OFFSET('Missed Time - AD Usage'!\$B\$5,0,1,92,1))

=Min(OFFSET('Missed Time - AD Usage'!\$B\$5,0,1,92,1))

etc

andrewbroadway

New Member
Then what do I use to return the range of cells?

I am trying to use Name's to point to a range of cells.

That range will then be used for the series in a chart.

Hui

Excel Ninja
Staff member
Andrew

You can use your formula in a named range and then use the named range as an X or Y value in a series for a chart

These are entered using the Name Manager not into cells

Also check that there aren't any text values in the range

andrewbroadway

New Member
Thank Hui, I understand that. The problem I am having is my formula is returning a single value. Not a range. I have the formula setup correctly yes? What else could be the problem.

andrewbroadway

New Member
Thank you so much for ANY help! Truly your generosity of your time and knowledge is greatly appreciated.

Hui

Excel Ninja
Staff member
Andrew

Try creating the named range from scratch

Delete the old Named Range and create a new Named Range

Type in =Offset(and then use the mouse to select the starting cell

once that is correct finish the 0,1,92,1) part

That should do it for you

dan_l

Active Member
Andrew,

what hui means is this:

Your formula is right. Your application is a bit off. copy that formula from the cell and create a range with the name as "test" or something. "refers to" should be filled with your formula.