Using a combination of OFFSET and COUNTA functions

  • Thread starter Thread starter K. Georgiadis
  • Start date Start date
K

K. Georgiadis

In the past few days I had an exchange of messages with
Tushar Mehta regarding an interactive chart, driven by a
combo box. My issue has been resolved but, for the
benefit of us who are still a little uncertain about the
combination of these functions, may I ask a couple of
questions regarding the construct and the arguments?

Again, I had an array of data in A4:F19, with cell B23 as
the link cell for the combo box. I used the named formula:

PlotData = OFFSET(Sheet1!$B$4,Sheet1!$B$23-1,0,1,COUNTA
(Sheet1!$A:$A)-1)

1. I read about OFFSET and its arguments (starting cell,
cells up, down, right or left). I'm not sure I understand
the construct using $B$23 (the cell linked to the combo
box), since this cell is outside the data array

2. When I clicked the "Refers to" button in
Insert/Name/Define, I saw that the range highlighted on
the worksheet extended 10 or 12 columns beyond my data
range. Is that an Excel quirk or is it an effect of the
formula? What would have happened if I had unrelated
numbers entered in those extra columns?

Many thanks
 
Hi
could you post the values in your cell B23 and your expected result
(that is which range should be selected)
 
B23 is the link cell to the combo box, the input range of
which is Column A, containing the row labels.
Column A has the row labels, and columns B through F
containing the data
 
Hi
please provide some sample data :-)
are your labels 'Text' values like headers or numbers?
 
Column A row labels are text strings (e.g., Net selling
price, Probability of success, etc.)
 
Hi
not so sure about your spreadsheet layout. You said that columns B to F
contains the data but also the linked cell is in column B?. If you like
email me your file
email: frank[dot]kabel[at]freenet[dot]de
 
yes the link cell is in cell B23, outside the data array,
but linked to the column A text labels through the combo
box.

Let me emphasize again that the formula suggested by
Tushar Mehta works beautifully and that I'm not trying to
solve a hot, pending problem. I was only trying to
understand the logic/construct of the formula so that I
can hopefully adapt it to future situations.

Under the circumstances, I'd say that there must be more
urgent requests on the message board therefore I wouldn't
worry excessively about this one. I can always catch up
on the theory later.

Your willingness to help has been greatly appreciated.

-----Original Message-----
Hi
not so sure about your spreadsheet layout. You said that columns B to F
contains the data but also the linked cell is in column B?. If you like
email me your file
email: frank[dot]kabel[at]freenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany


[email protected] said:
Column A row labels are text strings (e.g., Net selling
price, Probability of success, etc.)
Click to expand...
expected
result
Click to expand...

.
Click to expand...
 
(1) The offset function is constructed this way:

OFFSET({original reference}, {row offset}, {column offset}, {size in
rows}, {size in columns})

So for your formula...
PlotData = OFFSET(Sheet1!$B$4,Sheet1!$B$23-1,0,1,COUNTA
(Sheet1!$A:$A)-1)
Click to expand...

{Sheet1!$B$4} is the reference from which the OFFSET is determined.

{Sheet1!$B$23-1} is the number of rows downward from the reference your
OFFSET range starts at. whatever number is in B23, subtract one, and
that's the number of rows to move down. If the answer is negative, it's
the number of rows to move up.

{0} is the number of columns away from your reference that the OFFSET
range begins. Positive means move to the right.

{1} is the number of rows tall your OFFSET range will be, starting with
the range defined by the two offsets above.

{COUNTA(Sheet1!$A:$A)-1} is the number of columns wide your OFFSET range
will be, starting with the range defined by the two offsets. So it's one
less than the number of populated cells in column A.

(2) You are counting the number of filled rows in order to compute the
column width of the offset range. If you want the offset to be parallel
to the filled part of column A, you have mixed up the row size and
column size, and you probably want:

PlotData = OFFSET(Sheet1!$B$4,Sheet1!$B$23-1,0,COUNTA(Sheet1!$A:$A)-1,1)

This formula makes more sense to me, by inspection, without knowing your
data, if you switch the row and column offsets, too:

PlotData = OFFSET(Sheet1!$B$4,0,Sheet1!$B$23-1,COUNTA(Sheet1!$A:$A)-1,1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Thank you. This is what I was looking for. Since I have a
working formula and the time pressure is off, I'm also
going to experiment with your version of the formula.

KG
 
Back
Top