• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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.
 

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.
 
Top