Well, here goes. It is all about the question, so i'll try to ask the right one!
Let's say I have
a list of numbers (in excel, assuming we are in Column 'A' going from row 1 to row 10)
OK, pretty simple so far. Do you see the number '2' in that list, how it occurs a few times? What I am
particularly interested in is what the number *was* that *followed* the appearance of the '2' the next time around.
In the first case the number following the '2' was a '4', the second case it was a '6' and in the third case it was a '5'
(it doesn't matter if there are repititions).
Now I, living two caves down from Osama, would come up with a very
primative solution to at least extract the following numbers to Column B:
Am I a genius,
or what? From there I would fill down 10 cells, and the out put would look like ths (in column B):
(note the gaps, as the search for the numbers that follow appear in the adjoining cell, if I could eliminate the
gaps initially, or if there is a better way to extract the data, I am all ears)
OK, great, so at least I have
'found' the desired numbers. Now in order for me to chart the numbers without the annoying gaps, there are a few solutions.
Two of which are to construct the =if statement with an #n/a [=if(a1=2,a2,#n/a], this way I can highlight the entire range
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 for charting purposes is somewhat more elegant, and simple, and that is to select the column where the
'found' numbers are, and click on AutoFilter. Fron there click on the toggle button for 'Nonblanks' and voila! it sucks all
the offening air out of the column and we see a nice consecutive list of mumbers.
Are you with me so far? OK,
good, because I am no expert, and the next part is the big deal.
So I have found the numbers, and as a bonus I can
eliminate the offending air in the column as to produce a chart that looks right (a simple line chart, nothing fancy). But
now I am getting greedy, becasue I want to *AUTOMATE* the production of the charts on an *As needed* basis.
the data is continually in flux, it won't do me much good to just manually chart all thee columns of numbers, because the
chart one day will look different the next, and all I am interested in is how the chart looks today. Plus, the number of
charts to maintain is somewajhat staggering, and would really mushroom the spread sheet. And, on any gien day I will only be
interested in loking at a few select charts out of the hundreds. Thus, the ability to say to excel:
Chart 1, Chart 3, Chart 12, and Chart 17, Chart 23, and Chart 41, from the data located on the sheet called 'adata', and
place them on the sheet called 'chartoutput'"
would be sweet indeed!
Now, I have tinkerd with a
macro (recording one, the stupid person's solution to creating a macro ) Since I know how to tell the spread sheet where to
go (the 6 unique charts I want created) I would start out the macro by highlighting the cell that directed excel to the
precise column where the chart data was located.
On a sheet called 'data' there is a cell which has the address
of the column that has the chart data which is Column A on the sheet called 'adata'. After directing the macro to copy the
addressed cell, I then 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 that *no matter what the name of the cell i want is found, it will autofilter *that*
column, and not the one with the original cell 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 recorded in the macro to end up at A1, even though on the next go around the freshly copied address from
the locator cell on sheet 'data' is 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.