T
tx12345
Hi,
Well, here goes. It is all about the question, so i'll try to ask th
right one!
Let's say I have a list of numbers (in excel, assuming we are i
Column 'A' going from row 1 to row 10)
2<<
5
12
2<<
6
23
2<<
5
5
9
OK, pretty simple so far. Do you see the number '2' in that list, ho
it occurs a few times? What I am particularly interested in is wha
the number *was* that *followed* the appearance of the '2' the nex
time around.
In the first case the number following the '2' was a '4', the secon
case it was a '6' and in the third case it was a '5' (it doesn'
matter if there are repititions).
Now I, living two caves down from Osama, would come up with a ver
primative solution to at least extract the following numbers to Colum
B:
=if(a1=2,a2)
Am I a genius, or what? From there I would fill down 10 cells, and th
out put would look like ths (in column B):
5
(blank cell)
(blank cell)
6
(blank cell)
(blank cell)
5
(note the gaps, as the search for the numbers that follow appear in th
adjoining cell, if I could eliminate the gaps initially, or if there i
a better way to extract the data, I am all ears)
OK, great, so at least I have 'found' the desired numbers. Now i
order for me to chart the numbers without the annoying gaps, there ar
a few solutions. Two of which are to construct the =if statement wit
an #n/a [=if(a1=2,a2,#n/a], this way I can highlight the entire rang
and it will chart without the gaps, but it leaves all these '#n/a's
all over the spread sheet. The next solution to eliminate the gaps fo
charting purposes is somewhat more elegant, and simple, and that is t
select the column where the 'found' numbers are, and click o
AutoFilter. Fron there click on the toggle button for 'Nonblanks' an
voila! it sucks all the offening air out of the column and we see
nice consecutive list of mumbers.
Are you with me so far? OK, good, because I am no expert, and the nex
part is the big deal.
So I have found the numbers, and as a bonus I can eliminate th
offending air in the column as to produce a chart that looks right (
simple line chart, nothing fancy). But now I am getting greedy, becasu
I want to *AUTOMATE* the production of the charts on an *As needed
basis.
Since the data is continually in flux, it won't do me much good to jus
manually chart all thee columns of numbers, because the chart one da
will look different the next, and all I am interested in is how th
chart looks today. Plus, the number of charts to maintain is somewajha
staggering, and would really mushroom the spread sheet. And, on any gie
day I will only be interested in loking at a few select charts out o
the hundreds. Thus, the ability to say to excel:
"Go make Chart 1, Chart 3, Chart 12, and Chart 17, Chart 23, and Char
41, from the data located on the sheet called 'adata', and place the
on the sheet called 'chartoutput'"
would be sweet indeed!
Now, I have tinkerd with a macro (recording one, the stupid person'
solution to creating a macro
) Since I know how to tell the sprea
sheet where to go (the 6 unique charts I want created) I would star
out the macro by highlighting the cell that directed excel to th
precise column where the chart data was located.
On a sheet called 'data' there is a cell which has the address of th
column that has the chart data which is Column A on the sheet calle
'adata'. After directing the macro to copy the addressed cell, I the
say Find, and it moves right to the cell I want in 'adata'.
And that is the problem. I want to reuse the macro generically so tha
*no matter what the name of the cell i want is found, it wil
autofilter *that* column, and not the one with the original cel
address*
Did you gt that? It is so hard to explain. When I record the macro
once it 'finds' the right cell in 'adata' it always remembers *that
cell! If the find led me initially to A1 in 'adata', it gets recorde
in the macro to end up at A1, even though on the next go around th
freshly copied address from the locator cell on sheet 'data' i
completely different. What this leads to is I always end up with the
same chart output from column A1.
There are over 250 distinct possibilities (for my purposes), and I am
trying to avoid creating 250 macros that say "OK, you stupid program,
go to column A, and make the chart, end macro" and then another "column
B" etc. That would take forever. Even if I could point and click and
get the chart out put just so, once I knew which ones I wanted to look
at, I'd have to manually hunt through the list of 250 macros, and click
on them individually to get the 6 different charts I want.
What I *want* to do, is find a way to go from the 6 cells that have the
specific column addresses of where the chart data is, and from there
create the chart, without the macro getting locked in to the same
initial destination address. once i get around that bug, the rest of it
is wasy.
TIA!
Well, here goes. It is all about the question, so i'll try to ask th
right one!
Let's say I have a list of numbers (in excel, assuming we are i
Column 'A' going from row 1 to row 10)
2<<
5
12
2<<
6
23
2<<
5
5
9
OK, pretty simple so far. Do you see the number '2' in that list, ho
it occurs a few times? What I am particularly interested in is wha
the number *was* that *followed* the appearance of the '2' the nex
time around.
In the first case the number following the '2' was a '4', the secon
case it was a '6' and in the third case it was a '5' (it doesn'
matter if there are repititions).
Now I, living two caves down from Osama, would come up with a ver
primative solution to at least extract the following numbers to Colum
B:
=if(a1=2,a2)
Am I a genius, or what? From there I would fill down 10 cells, and th
out put would look like ths (in column B):
5
(blank cell)
(blank cell)
6
(blank cell)
(blank cell)
5
(note the gaps, as the search for the numbers that follow appear in th
adjoining cell, if I could eliminate the gaps initially, or if there i
a better way to extract the data, I am all ears)
OK, great, so at least I have 'found' the desired numbers. Now i
order for me to chart the numbers without the annoying gaps, there ar
a few solutions. Two of which are to construct the =if statement wit
an #n/a [=if(a1=2,a2,#n/a], this way I can highlight the entire rang
and it will chart without the gaps, but it leaves all these '#n/a's
all over the spread sheet. The next solution to eliminate the gaps fo
charting purposes is somewhat more elegant, and simple, and that is t
select the column where the 'found' numbers are, and click o
AutoFilter. Fron there click on the toggle button for 'Nonblanks' an
voila! it sucks all the offening air out of the column and we see
nice consecutive list of mumbers.
Are you with me so far? OK, good, because I am no expert, and the nex
part is the big deal.
So I have found the numbers, and as a bonus I can eliminate th
offending air in the column as to produce a chart that looks right (
simple line chart, nothing fancy). But now I am getting greedy, becasu
I want to *AUTOMATE* the production of the charts on an *As needed
basis.
Since the data is continually in flux, it won't do me much good to jus
manually chart all thee columns of numbers, because the chart one da
will look different the next, and all I am interested in is how th
chart looks today. Plus, the number of charts to maintain is somewajha
staggering, and would really mushroom the spread sheet. And, on any gie
day I will only be interested in loking at a few select charts out o
the hundreds. Thus, the ability to say to excel:
"Go make Chart 1, Chart 3, Chart 12, and Chart 17, Chart 23, and Char
41, from the data located on the sheet called 'adata', and place the
on the sheet called 'chartoutput'"
would be sweet indeed!
Now, I have tinkerd with a macro (recording one, the stupid person'
solution to creating a macro
) Since I know how to tell the spreasheet where to go (the 6 unique charts I want created) I would star
out the macro by highlighting the cell that directed excel to th
precise column where the chart data was located.
On a sheet called 'data' there is a cell which has the address of th
column that has the chart data which is Column A on the sheet calle
'adata'. After directing the macro to copy the addressed cell, I the
say Find, and it moves right to the cell I want in 'adata'.
And that is the problem. I want to reuse the macro generically so tha
*no matter what the name of the cell i want is found, it wil
autofilter *that* column, and not the one with the original cel
address*
Did you gt that? It is so hard to explain. When I record the macro
once it 'finds' the right cell in 'adata' it always remembers *that
cell! If the find led me initially to A1 in 'adata', it gets recorde
in the macro to end up at A1, even though on the next go around th
freshly copied address from the locator cell on sheet 'data' i
completely different. What this leads to is I always end up with the
same chart output from column A1.
There are over 250 distinct possibilities (for my purposes), and I am
trying to avoid creating 250 macros that say "OK, you stupid program,
go to column A, and make the chart, end macro" and then another "column
B" etc. That would take forever. Even if I could point and click and
get the chart out put just so, once I knew which ones I wanted to look
at, I'd have to manually hunt through the list of 250 macros, and click
on them individually to get the 6 different charts I want.
What I *want* to do, is find a way to go from the 6 cells that have the
specific column addresses of where the chart data is, and from there
create the chart, without the macro getting locked in to the same
initial destination address. once i get around that bug, the rest of it
is wasy.
TIA!