Wow! that is complex, I think I understand how this would work but I am
getting a reference error in X2 when I enter the search/indirect function.

I have entered the following for my spreadsheet

=MATCH("x",INDIRECT(AB20),0)+AA19

AA19 would be X1 and AB20 would be Y2 in your example. Did I do something
incorrectly?
--
Tim at Alliant


"Bob Bridges" wrote:

Macros are great and I use them all the time. But the problem with a
macro in this situation is that you have to make the sheet run the
macro every time the user selects a new value, or make the user run
the macro after choosing all desired values. Automatic is better, and
yeah, there's a way to do it with formulae; it'll be a little more
complicated than you hoped, though.

Actually there's probably a number of ways, and you may be able to
come up with a better after trying this, but here's how I'd probably
approach it. First, forget about rows 1 through 8; eventually you're
going to have one or more column-header rows, so let's just say
your Sheet1 data is in rows 2 through, I dunno, 999. I mean, you
don't have to use 999 as the upper limit, but that's what I'll use in
this example.

Now in Sheet2 set up some helper columns out at the right; I'll
use X, Y and Z. In X1 put the value 1; that's the row right above
the first data row in Sheet1 that your search function will be
looking in, to start with.

Then in Y2 put this formula:

="Sheet1!A"&X1+1&":A999"

Since we just put a 1 in X1, this generates the character string
"Sheet1!A2:A999". That's the range we want to search for the
first 'x'. But so far Y2 is just a character string; next, in X2,
combine a search formula and the INDIRECT function, which
interprets that character string as a real range:

=MATCH("x",INDIRECT(Y2),0)+X1

INDIRECT(Y2) just converts Y2 to a real range argument. The
MATCH function is like VLOOKUP in that it searches the range in
the second argument for the value in the first argument, only
instead of returning a value from another cell out to the right,
it just hands you the row number the match was found in. The
row is relative to the range you gave it, so if, back on Sheet1,
you put an 'x' in say row 3, that's the SECOND row of A2:A999,
so the MATCH function returns not 3 but 2. That's why I added
X1 back into the result, to turn it back into the real row number.

Now watch what happens when you copy these two formulae
downward. Y3 now displays the updated range,
"Sheet1!A4:A999", which is where you want the next search to be
done. If you've put only the one 'x' in Sheet1!A3, then X3 will do
the next search starting in row 4 and find nothing, so for now X3
shows #N/A. Since that isn't a valid row number, Y4 shows #N/A
too, and thus down the page.

But go back to Sheet1 and put 'x' in some other row of column A;
now the next set of values are filled in. And so on.

Now in Sheet2 column X you have a list of the Sheet1 rows the user
marked. Let's pull in the name from Sheet1!B:B; in col Z use this
formula:

=INDIRECT("Sheet1!B"&X2)

In each row that has a valid row number in col X, this pulls the value
from Sheet1!B<WhateverRow. When you get down past the number
of Xs the user entered, Z just has more #N/As.

Ok, you have your data. But #N/As are unsightly, so back in col A of
Sheet2 let's make it more cosmetic. In A2 put

=IF(ISERROR(Z2),"",Z2)

This checks Z2; if it's #N/A then it counts as an error and you just
display a blank character string; otherwise you display Z2 itself (the
value in Sheet1). Copy all this downward and you have what you
want. But as I warned you, it's more complex than it is pretty.