Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 25
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bj bj is offline
external usenet poster
 
Posts: 1,397
Default 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.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,311
Default 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.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 25
Default 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.




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,311
Default 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.







  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 25
Default 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.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,311
Default 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.







  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,311
Default 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.








  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bj bj is offline
external usenet poster
 
Posts: 1,397
Default 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.

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bj bj is offline
external usenet poster
 
Posts: 1,397
Default 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.



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 25
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
If cell is left blank, or equals zero, then cell equals a different cell John McMurry Excel Discussion (Misc queries) 3 April 13th 07 01:14 PM
If cell value equals zero Steve Excel Worksheet Functions 2 March 3rd 06 03:57 AM
add one cell with no equals operator reggie26t Excel Discussion (Misc queries) 1 January 24th 06 03:57 PM
if a:a (range) equals january and c:c equals gas then add g:g ($) BCOz Excel Worksheet Functions 4 December 29th 05 08:40 PM
custom filter does not work when selecting 'equals' X AND 'equals' plindman Excel Discussion (Misc queries) 1 June 1st 05 11:29 PM


All times are GMT +1. The time now is 03:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"