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

Using Offset Horizontally

patricia

patricia

New Member
Has anyone used Offset to add columns of data to a chart rather than rows? I've played around with it and have never been successful in getting OFFSET to work with columns.


Any suggestions appreciated.


Thanks.
 
Welcome to the forums!

Can you elaborate? If you want to use OFFSET to define a dynamic range that is horizontal, maybe something like:

=OFFSET($B$2,,,1,COUNT(2:2))


Defines a horizontal range of data whose width is equal to count of numbers in row 2.
 
HI Luke- Thanks for the welcome. What I am trying to do is add columns to a chart and have the chart pick them up automatically as they are entered instead of having it pick up rows automatically.

For example, I have a chart that graphs product sales by month. In Column A, I have Products at cell A1, Widget1 at A2, Widget2 A3, Widge3 at A4 and then in Column B I have January Sales at B1, $100 in B2,$200 in B3 etc. I can chart that but I want the chart to automatically pick up the next Column so that when I enter February sales in Column C and March sales into Column D the data will automatically be included in the chart,
 
Patricia,


If you include the full range (say all twelve months) it will include the data as it is available, in Excel 2003 and below I used to insert #N/A in the coulmns that had no data and in that way the chart ignored these columns until data was placed in them.


cheers


kanti
 
Patricia


As Luke said you can use Named Ranges for your series


In your case use a named Range for Widget 1 and set it to

Widget1:
Code: 
=OFFSET(Sheet1!$A$2,,1,1,COUNTA(Sheet1!$B$2:$ZZ$2))


You will also need a Named Range for the Months

Months: =OFFSET(Sheet1!$A$1,,1,1,COUNTA(Sheet1!$B$1:$ZZ$1))


Setup your chart as normal

Using SheetName!Date as the X Axis and SheetName!Widget1 as the Values


This will extend automatically as data for subsequent months is added

It won't add new Widgets though, that has to be done manually
 
Hui,


On this topic, why are you able to set "Hidden and Empty Cell Settings" in some cases and not in others.


I would have suggested that in this problem you set that to "Gaps" and then there is no need for the use of the range name.


kanti
 
kchiba,

That option is only available if a chart is selected (or you're on a chart_worksheet).
 
Hi Luke,


Thanks for your reply, the option in certain cases is greyed out and I am not able to work out why.


kanti
 
Hurray! Both methods worked. Kanti- using the #n/a is a nice and quick approach.I think I must be brain dead or need new glasses - in the Offset I kept putting a 1 in the row instead of the column and just kept repeating the same error over and over- Egads! Thanks again.
 
You must log in or register to reply here.
Back
Top