another for the experts

[Wazooli]

I have now figured out my VLOOKUP problem, so let me proceed with my troubles.

I have a sheett of data that represents the entire human genome, organized
in ascending order by chromosome. The list is around 9500 rows, and consists
of 9 columns. The first is always going to be my x series (the whole point
of this is to make a dynamic scatter plot), and each subsequent column is a
log2 value, for 1 of 8 individual data sets, which represent my y series. I
know how to keep the x series constant, and choose which y series i want via
a combo box and linked cell, with the help of an offset formula. This works
great for all 9500 data points, and I can graph each column against the x
series no problem. I am, however, running into trouble if I try to implement
a second combo box/linked cell that allows me to choose to graph an
individual chromosome. I realize I need dynamic ranges for this, as the
SERIES formula is essentially uneditable, so any help is appreciated.

wazooli

[Ed Ferrero]

Hi Wazooli,

Chart Selector at http://edferrero.m6.net/Content/ChartSelector.zip
has what you need. You should be able to adapt it for your purposes easily
enough.

Does the human genome really fit on one spreadsheet ?!!!

Ed Ferrero

"Wazooli" <Wazooli@discussions.microsoft.com> wrote in message
news:770E7586-36E8-4166-BD89-B8EE831A19FA@microsoft.com...
>I have now figured out my VLOOKUP problem, so let me proceed with my
>troubles.
>
> I have a sheett of data that represents the entire human genome, organized
> in ascending order by chromosome. The list is around 9500 rows, and
> consists
> of 9 columns. The first is always going to be my x series (the whole
> point
> of this is to make a dynamic scatter plot), and each subsequent column is
> a
> log2 value, for 1 of 8 individual data sets, which represent my y series.
> I
> know how to keep the x series constant, and choose which y series i want
> via
> a combo box and linked cell, with the help of an offset formula. This
> works
> great for all 9500 data points, and I can graph each column against the x
> series no problem. I am, however, running into trouble if I try to
> implement
> a second combo box/linked cell that allows me to choose to graph an
> individual chromosome. I realize I need dynamic ranges for this, as the
> SERIES formula is essentially uneditable, so any help is appreciated.
>
> wazooli

[Wazooli]

Lol - it does if it is sampled at 1:1000000. Anyway, I took a look at your
selector. Not quite what I had in mind. Seemse to me I need to assign
dynamic ranges, but I am unsure as to how to do it with what I have.

"Ed Ferrero" wrote:

> Hi Wazooli,
>
> Chart Selector at http://edferrero.m6.net/Content/ChartSelector.zip
> has what you need. You should be able to adapt it for your purposes easily
> enough.
>
> Does the human genome really fit on one spreadsheet ?!!!
>
> Ed Ferrero
>
> "Wazooli" <Wazooli@discussions.microsoft.com> wrote in message
> news:770E7586-36E8-4166-BD89-B8EE831A19FA@microsoft.com...
> >I have now figured out my VLOOKUP problem, so let me proceed with my
> >troubles.
> >
> > I have a sheett of data that represents the entire human genome, organized
> > in ascending order by chromosome. The list is around 9500 rows, and
> > consists
> > of 9 columns. The first is always going to be my x series (the whole
> > point
> > of this is to make a dynamic scatter plot), and each subsequent column is
> > a
> > log2 value, for 1 of 8 individual data sets, which represent my y series.
> > I
> > know how to keep the x series constant, and choose which y series i want
> > via
> > a combo box and linked cell, with the help of an offset formula. This
> > works
> > great for all 9500 data points, and I can graph each column against the x
> > series no problem. I am, however, running into trouble if I try to
> > implement
> > a second combo box/linked cell that allows me to choose to graph an
> > individual chromosome. I realize I need dynamic ranges for this, as the
> > SERIES formula is essentially uneditable, so any help is appreciated.
> >
> > wazooli
>
>
>

[Ed Ferrero]

Hi again,

My chart selector example is dynamic, just add rows or columns and it will
grow the ranges to fit.

But if you really want to use dynamic ranges, take a look at
http://edferrero.m6.net/Content/ChartSelector2.zip
In this example i use drop-downs and dynamic ranges with no VBA at all.
The problem here is that the column headings are in a one-row many-column
range and this
needs to be transposed before it can be used in a drop-down list. I have
included formulas
to dynamically transpose a range in the sheet names "ColList". HTH

Ed Ferrero
http://edferrero.m6.net/

"Wazooli" <Wazooli@discussions.microsoft.com> wrote in message
news:A4D67FB9-974B-4C50-AC43-FED4380E034A@microsoft.com...
> Lol - it does if it is sampled at 1:1000000. Anyway, I took a look at
> your
> selector. Not quite what I had in mind. Seemse to me I need to assign
> dynamic ranges, but I am unsure as to how to do it with what I have.
>
> "Ed Ferrero" wrote:
>
>> Hi Wazooli,
>>
>> Chart Selector at http://edferrero.m6.net/Content/ChartSelector.zip
>> has what you need. You should be able to adapt it for your purposes
>> easily
>> enough.
>>
>> Does the human genome really fit on one spreadsheet ?!!!
>>
>> Ed Ferrero
>>
>> "Wazooli" <Wazooli@discussions.microsoft.com> wrote in message
>> news:770E7586-36E8-4166-BD89-B8EE831A19FA@microsoft.com...
>> >I have now figured out my VLOOKUP problem, so let me proceed with my
>> >troubles.
>> >
>> > I have a sheett of data that represents the entire human genome,
>> > organized
>> > in ascending order by chromosome. The list is around 9500 rows, and
>> > consists
>> > of 9 columns. The first is always going to be my x series (the whole
>> > point
>> > of this is to make a dynamic scatter plot), and each subsequent column
>> > is
>> > a
>> > log2 value, for 1 of 8 individual data sets, which represent my y
>> > series.
>> > I
>> > know how to keep the x series constant, and choose which y series i
>> > want
>> > via
>> > a combo box and linked cell, with the help of an offset formula. This
>> > works
>> > great for all 9500 data points, and I can graph each column against the
>> > x
>> > series no problem. I am, however, running into trouble if I try to
>> > implement
>> > a second combo box/linked cell that allows me to choose to graph an
>> > individual chromosome. I realize I need dynamic ranges for this, as
>> > the
>> > SERIES formula is essentially uneditable, so any help is appreciated.
>> >
>> > wazooli
>>
>>
>>