Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If cell is left blank, or equals zero, then cell equals a different cell | Excel Discussion (Misc queries) | |||
If cell value equals zero | Excel Worksheet Functions | |||
add one cell with no equals operator | Excel Discussion (Misc queries) | |||
if a:a (range) equals january and c:c equals gas then add g:g ($) | Excel Worksheet Functions | |||
custom filter does not work when selecting 'equals' X AND 'equals' | Excel Discussion (Misc queries) |