Formula for row vs column

[seanrigby]

I reviewed this tip to automatically update graphs....

Go to Insert | Name| Define.
Enter Date in the Names In Workbook text box.
Enter the following formula in the Refers to text box:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)Click Add.
Enter Temperature in the Names In Workbook text box.
Enter the following formula in the Refers To text box:

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)Click Add and then OK.
Click on the chart, and then on the data series.
Change the formula in the formula bar to the following:

=SERIES(,Sheet1!Date,Sheet1!Temperature,1)The chart will update automatically each day with a new temperature. Be sure you don't use Columns A and B for any other data; otherwise, COUNTA will return an incorrect value.
-------------------------------------------------------------------

How do you change these formulas to do it for a particular row instead of a column?

[teylyn]

Hi,

you want to look into how the offset and counta formulas work.

=OFFSET(reference, rows, columns, [height], [width])

In the above example the "height" parameter is calculated by counting the number of entries in the column, thus determining how many rows the range will contain.

If you want to construct a dynamic range that runs horizontally, you will need to count how many columns are populated in the row, and use that number for the "width" parameter, something like

=OFFSET(Sheet1!$A$2,0,0,1,COUNTA(Sheet1!$2:$2)-1)

COUNTA() counts the number of populated cells. The -1 above is probably there to adjust for a label that should not be included in the range.

Note that COUNTA counts all populated cells in the specified range, so you want to make sure that the row contains only cells you want to include, i.e. don't have anything else in that row that does not need to be there.

hth

[seanrigby]

I'm still missing something......

A22: John Doe B22: 12 C22:24 D22:36

Can I do a offset formula as well as a series formula based off of one row? I would say that data could go 30 columns across row 22.

[teylyn]

Hi, can you upload a workbook with a small sample of data that illustrates what you want to do?

[seanrigby]

In this spreadsheet, you will notice 2 graphs. One just shows the data listed, which I want. The other one shows all the columns, even if there is no data entered, which I don't want. I need the graph to grow automatically when the data is entered.

[contaminated]

Hi plesa see file
Used function named as Dyn_val

Please Login or Register  to view this content.

If it meets your requirements, mark the thread as "SOLVED", and don't forget to add reputation by pressing blue scales on the right of your screen

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[teylyn]

Hi,

define a range name, for example JohnDoe with the following formula

=OFFSET(Sheet1!$B$22,0,0,1,COUNTA(Sheet1!$A$22:$S$22)-1)

Then edit your series formula to be

=SERIES(Sheet1!$A$22,,'test data.xls'!JohnDoe,1)

cheers

[seanrigby]

I do not see a file attached to your posting for some reason?

[contaminated]

posting it again....

[seanrigby]

Teylyn,

What if the blank cell has a formula in it? For example cell H22 of the attached spreadsheet?

[seanrigby]

Perfect. Works great. Thanks!

[contaminated]

If it meets your requirements, mark the thread as "SOLVED", and don't forget to add reputation by pressing blue scales on the right of your screen

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save