If a cell equals _, at the next row that equals _, return value fr
 

This is an example of what I'm trying to do:

If a row in Column 1 = Joe's Hardware
Take the next row below Joe's Hardware that is called Paint Brushes and
return the value from Column 7 in the same row

I can't for the life of me figure out how to do it.

If a cell equals _, at the next row that equals _, return value fr
 

try
=offset(A1,match("Joe's Hardware",A:A",0),6)

"CathyH" wrote:

This is an example of what I'm trying to do:

If a row in Column 1 = Joe's Hardware
Take the next row below Joe's Hardware that is called Paint Brushes and
return the value from Column 7 in the same row

I can't for the life of me figure out how to do it.

If a cell equals _, at the next row that equals _, return value fr
 

Maybe one way could be done like this:

=IF(A18="Joe's Hardware",INDIRECT("G"& (ROW(A18)-1) + MATCH("Paint
Brushes",A18:A100)))

Regards,
Paul

"CathyH" wrote in message
...
This is an example of what I'm trying to do:

If a row in Column 1 = Joe's Hardware
Take the next row below Joe's Hardware that is called Paint Brushes and
return the value from Column 7 in the same row

I can't for the life of me figure out how to do it.

If a cell equals _, at the next row that equals _, return valu
 

This is close - it works if I know where Joe's Hardware is located i.e.
CellA72 - but this will change each month.
I'm working from an exported report that looks something like:



Budgeted Budgeted Actual Actual
Revenue Costs Revenue Costs

Joe's Hardware
Paint Brushes 500 250 350 225
Paint 1000 450 1050 500
Hand Tools 250 125 250 125
Power Tools 5000 2500 5000 3000
Sundries 250 130 250 150
Joe's Hardware(CAD) 7000 3455 6900 4000

Tools 'n Stuff
Paint Brushes 750 400 350 225
Paint 1000 450 1050 500
Hand Tools 250 125 250 125
Power Tools 5000 2500 5000 3000
Sundries 250 130 250 150
Tools 'n Stuff(CAD) 7250 3605 6900 4000

Handyman World
Paint Brushes 500 250 350 225
Paint 750 450 1050 500
Hand Tools 250 125 250 125
Power Tools 5000 2500 5000 3000
Sundries 250 130 250 150
Handyman World(CAD) 6750 3455 6900 4000

Each time we export the data may be in different rows from the previous month.

We are trying to construct a seperate profitability worksheet for each
customer pulling the numbers from the exported data.

So it has to be something like:
When A:A = Handyman World, at the NEXT instance of Sundries return the value
from Column G of that row.

Greatly appreciate the help!

Cathy

"PCLIVE" wrote:

Maybe one way could be done like this:

=IF(A18="Joe's Hardware",INDIRECT("G"& (ROW(A18)-1) + MATCH("Paint
Brushes",A18:A100)))

Regards,
Paul

"CathyH" wrote in message
...
This is an example of what I'm trying to do:

If a row in Column 1 = Joe's Hardware
Take the next row below Joe's Hardware that is called Paint Brushes and
return the value from Column 7 in the same row

I can't for the life of me figure out how to do it.

If a cell equals _, at the next row that equals _, return value fr
 

Ok.

It sounds like you want to first find "Joe's Hardware". Then you want to
find "Paint Brushes" below that.

There are probaly other ways...but see if this works for you.

=INDIRECT("G"&MATCH("Paint Brushes",INDIRECT("A"&MATCH("Joe's
Hardware",A1:A100,0)&":A23"),0)+MATCH("Joe's Hardware",A1:A100,0)-1)

Paul


"PCLIVE" wrote in message
...
Maybe one way could be done like this:

=IF(A18="Joe's Hardware",INDIRECT("G"& (ROW(A18)-1) + MATCH("Paint
Brushes",A18:A100)))

Regards,
Paul

"CathyH" wrote in message
...
This is an example of what I'm trying to do:

If a row in Column 1 = Joe's Hardware
Take the next row below Joe's Hardware that is called Paint Brushes and
return the value from Column 7 in the same row

I can't for the life of me figure out how to do it.

If a cell equals _, at the next row that equals _, return valu
 

This might just work - but how do I say "return the value 13 rows down from
the MATCH"?

"bj" wrote:

try
=offset(A1,match("Joe's Hardware",A:A",0),6)

"CathyH" wrote:

This is an example of what I'm trying to do:

If a row in Column 1 = Joe's Hardware
Take the next row below Joe's Hardware that is called Paint Brushes and
return the value from Column 7 in the same row

I can't for the life of me figure out how to do it.

If a cell equals _, at the next row that equals _, return value fr
 

This seems a bit more clean using offset recommended by bj.

=OFFSET(INDIRECT("A" & MATCH("Joe's Hardware",A1:A100,0)+1),0,6)


"PCLIVE" wrote in message
...
Ok.

It sounds like you want to first find "Joe's Hardware". Then you want to
find "Paint Brushes" below that.

There are probaly other ways...but see if this works for you.

=INDIRECT("G"&MATCH("Paint Brushes",INDIRECT("A"&MATCH("Joe's
Hardware",A1:A100,0)&":A23"),0)+MATCH("Joe's Hardware",A1:A100,0)-1)

Paul


"PCLIVE" wrote in message
...
Maybe one way could be done like this:

=IF(A18="Joe's Hardware",INDIRECT("G"& (ROW(A18)-1) + MATCH("Paint
Brushes",A18:A100)))

Regards,
Paul

"CathyH" wrote in message
...
This is an example of what I'm trying to do:

If a row in Column 1 = Joe's Hardware
Take the next row below Joe's Hardware that is called Paint Brushes and
return the value from Column 7 in the same row

I can't for the life of me figure out how to do it.

If a cell equals _, at the next row that equals _, return value fr
 

Ok, that last one was completely wrong because it assumes that Paint Brushes
is one row below Joe's Hardware.

Try this one:

=OFFSET(INDIRECT("A" & MATCH("Joe's Hardware",A1:A100,0)),MATCH("Paint
Brushes",INDIRECT("A" & MATCH("Joe's Hardware",A1:A100,0) & ":A100"),0)-1,6)

Be care for wrap-around.

HTH,
Paul

"PCLIVE" wrote in message
...
This seems a bit more clean using offset recommended by bj.

=OFFSET(INDIRECT("A" & MATCH("Joe's Hardware",A1:A100,0)+1),0,6)


"PCLIVE" wrote in message
...
Ok.

It sounds like you want to first find "Joe's Hardware". Then you want to
find "Paint Brushes" below that.

There are probaly other ways...but see if this works for you.

=INDIRECT("G"&MATCH("Paint Brushes",INDIRECT("A"&MATCH("Joe's
Hardware",A1:A100,0)&":A23"),0)+MATCH("Joe's Hardware",A1:A100,0)-1)

Paul


"PCLIVE" wrote in message
...
Maybe one way could be done like this:

=IF(A18="Joe's Hardware",INDIRECT("G"& (ROW(A18)-1) + MATCH("Paint
Brushes",A18:A100)))

Regards,
Paul

"CathyH" wrote in message
...
This is an example of what I'm trying to do:

If a row in Column 1 = Joe's Hardware
Take the next row below Joe's Hardware that is called Paint Brushes and
return the value from Column 7 in the same row

I can't for the life of me figure out how to do it.

If a cell equals _, at the next row that equals _, return valu
 

=offset(A1,match("Joe's Hardware",A:A",0)+13,6)


"CathyH" wrote:

This might just work - but how do I say "return the value 13 rows down from
the MATCH"?

"bj" wrote:

try
=offset(A1,match("Joe's Hardware",A:A",0),6)

"CathyH" wrote:

This is an example of what I'm trying to do:

If a row in Column 1 = Joe's Hardware
Take the next row below Joe's Hardware that is called Paint Brushes and
return the value from Column 7 in the same row

I can't for the life of me figure out how to do it.

If a cell equals _, at the next row that equals _, return valu
 

Reading PCLIVE's enteries I think I missunderstood what you were looking for

=offset(indirect("A"&match("Joe's Hardware",A:A",0)),Match("Paint
Brushes",indirect("A"&match("Joe's Hardware",A:A",0)&"A$64000"))-1,7)


"bj" wrote:

=offset(A1,match("Joe's Hardware",A:A",0)+13,6)


"CathyH" wrote:

This might just work - but how do I say "return the value 13 rows down from
the MATCH"?

"bj" wrote:

try
=offset(A1,match("Joe's Hardware",A:A",0),6)

"CathyH" wrote:

This is an example of what I'm trying to do:

If a row in Column 1 = Joe's Hardware
Take the next row below Joe's Hardware that is called Paint Brushes and
return the value from Column 7 in the same row

I can't for the life of me figure out how to do it.

If a cell equals _, at the next row that equals _, return value
 

OK! If every customer's data prints out with the same number of rows i.e. we
have the same # of categories for each I can use:

=OFFSET(INDIRECT("data!A" & MATCH("Joe's Hardware",Data!$A$1:$A$100,0)),13,6)

The simpler of the two.

Otherwise I can use the one that matches both the customer name and the
category.

Thank you both so much! You're geniuses!




"bj" wrote:

Reading PCLIVE's enteries I think I missunderstood what you were looking for

=offset(indirect("A"&match("Joe's Hardware",A:A",0)),Match("Paint
Brushes",indirect("A"&match("Joe's Hardware",A:A",0)&"A$64000"))-1,7)


"bj" wrote:

=offset(A1,match("Joe's Hardware",A:A",0)+13,6)


"CathyH" wrote:

This might just work - but how do I say "return the value 13 rows down from
the MATCH"?

"bj" wrote:

try
=offset(A1,match("Joe's Hardware",A:A",0),6)

"CathyH" wrote:

This is an example of what I'm trying to do:

If a row in Column 1 = Joe's Hardware
Take the next row below Joe's Hardware that is called Paint Brushes and
return the value from Column 7 in the same row

I can't for the life of me figure out how to do it.