Excel formulas - comparing adjacent cells

B

Bryan Gnidrah

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
 
T

T. Valko

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.
 
B

Bryan Gnidrah

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

T. Valko

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.
 
B

Bryan Gnidrah

Biff,

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

Bryan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top