In a worksheet, I have a row of variable data which I want to compare against
one of a number of other rows containing the fixed data. The work sheet is
filtered, so while the comparison is for adjacent rows, there may be hidden
rows between the two rows to be compared. For example, row 1 contains the
variable data and rows 2 to 20 contain the fixed data. The filtering will
always leave two rows, 1 and 2, or 1 and 7 or 1 and 17 or other combination
of 1 and any row from 2 - 20, the remainder being hidden. Can anyway provide
a formula that will achieve this? any help would be greatly appreciated.

Bryan

Need more specific details.

data which I want to compare against...other rows
containing the fixed data.

Compare for what, equality?

What is the *entire specific range* when the filter is not applied?

What result do you want? Perhaps you can post an example and what result you
want.

--
Biff
Microsoft Excel MVP


"Bryan Gnidrah" wrote in message
...
In a worksheet, I have a row of variable data which I want to compare
against
one of a number of other rows containing the fixed data. The work sheet is
filtered, so while the comparison is for adjacent rows, there may be
hidden
rows between the two rows to be compared. For example, row 1 contains the
variable data and rows 2 to 20 contain the fixed data. The filtering will
always leave two rows, 1 and 2, or 1 and 7 or 1 and 17 or other
combination
of 1 and any row from 2 - 20, the remainder being hidden. Can anyway
provide
a formula that will achieve this? any help would be greatly appreciated.

Bryan

Biff,

Row 1 includes responses to several questions. The answers are validated in
a list elsewhere in the workbook. The other rows, ie, those which are
filtered, include the data for certain situations, once filtered there will
only be two rows, row 1 and one of row 2 - 20. The comparison is whether the
chosen response is less than, i.e. higher up the sorted list, or equal to it.
I have written this formula, the response for which is either 1 which is OK
or -1 not OK. One solution I thought of was to, inert a blank row, between
rows 1 and 2, then to write a macro, which will copy the contents of the row
immediately below row 2, one of row 3 to 20, I could then utilize the
formula, which currently sits in one of the rows 2-20, inserting this into
row 1.

I hope this explains a little better what i want to achieve. Unfortunately,
I am nowworking remotely from my PC which has the work sheet on, however, I
can send an extract later.

Bryan

"T. Valko" wrote:

Need more specific details.

data which I want to compare against...other rows
containing the fixed data.

Compare for what, equality?

What is the *entire specific range* when the filter is not applied?

What result do you want? Perhaps you can post an example and what result you
want.

--
Biff
Microsoft Excel MVP


"Bryan Gnidrah" wrote in message
...
In a worksheet, I have a row of variable data which I want to compare
against
one of a number of other rows containing the fixed data. The work sheet is
filtered, so while the comparison is for adjacent rows, there may be
hidden
rows between the two rows to be compared. For example, row 1 contains the
variable data and rows 2 to 20 contain the fixed data. The filtering will
always leave two rows, 1 and 2, or 1 and 7 or 1 and 17 or other
combination
of 1 and any row from 2 - 20, the remainder being hidden. Can anyway
provide
a formula that will achieve this? any help would be greatly appreciated.

Bryan

I'm not follwing you too well on this.

I get that row 1 contains some values.

You apply a filter and one row will be visible from your table data.

You want to compare the values in row 1 with the filtered row from your
table.

After that, I'm lost.

Let's say these are the values in row 1:

......A.....B.....C.....D
1...1......2.....3......4

You apply the filter and row 7 is the visible row. Row 7 contains these
values:

......A.....B.....C.....D
7...1......6.....4......3

OK, so what result do you want?

Also:

What is the *entire specific range* when the filter is not applied?

I need that question answered.


--
Biff
Microsoft Excel MVP


"Bryan Gnidrah" wrote in message
...
Biff,

Row 1 includes responses to several questions. The answers are validated
in
a list elsewhere in the workbook. The other rows, ie, those which are
filtered, include the data for certain situations, once filtered there
will
only be two rows, row 1 and one of row 2 - 20. The comparison is whether
the
chosen response is less than, i.e. higher up the sorted list, or equal to
it.
I have written this formula, the response for which is either 1 which is
OK
or -1 not OK. One solution I thought of was to, inert a blank row, between
rows 1 and 2, then to write a macro, which will copy the contents of the
row
immediately below row 2, one of row 3 to 20, I could then utilize the
formula, which currently sits in one of the rows 2-20, inserting this into
row 1.

I hope this explains a little better what i want to achieve.
Unfortunately,
I am nowworking remotely from my PC which has the work sheet on, however,
I
can send an extract later.

Bryan

"T. Valko" wrote:

Need more specific details.

data which I want to compare against...other rows
containing the fixed data.

Compare for what, equality?

What is the *entire specific range* when the filter is not applied?

What result do you want? Perhaps you can post an example and what result
you
want.

--
Biff
Microsoft Excel MVP


"Bryan Gnidrah" wrote in message
...
In a worksheet, I have a row of variable data which I want to compare
against
one of a number of other rows containing the fixed data. The work sheet
is
filtered, so while the comparison is for adjacent rows, there may be
hidden
rows between the two rows to be compared. For example, row 1 contains
the
variable data and rows 2 to 20 contain the fixed data. The filtering
will
always leave two rows, 1 and 2, or 1 and 7 or 1 and 17 or other
combination
of 1 and any row from 2 - 20, the remainder being hidden. Can anyway
provide
a formula that will achieve this? any help would be greatly
appreciated.

Bryan

Biff,

I think I've resolved the problem using =SUBTOTAL(109,Range of rows).

Bryan

Cool! Nice and simple, too!

Thanks for the feedback.

--
Biff
Microsoft Excel MVP


"Bryan Gnidrah" wrote in message
...

Biff,

I think I've resolved the problem using =SUBTOTAL(109,Range of rows).

Bryan