Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pulling "Matrix" Data
I am trying to return information using look-ups by looking up info that is
in a column and a row....so Region 1 Region 2 Region 3 Sales Revenue Sales Revenue Sales Revenue Code 1 Code 2 Code 3 So, say I want the revenue for Code 1 and Region 1 returned in a report....I'm just not sure of the syntax....do I create a name reference and do a look-up from that? Something like lookup(Code1|Region1,Cell References,2,False)? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pulling "Matrix" Data
Matt,
Don't know the source of your data, but have you looked into Pivot Tables ? NickHK "Matt" ... I am trying to return information using look-ups by looking up info that is in a column and a row....so Region 1 Region 2 Region 3 Sales Revenue Sales Revenue Sales Revenue Code 1 Code 2 Code 3 So, say I want the revenue for Code 1 and Region 1 returned in a report....I'm just not sure of the syntax....do I create a name reference and do a look-up from that? Something like lookup(Code1|Region1,Cell References,2,False)? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pulling "Matrix" Data
Assume M1 holds the string "Region1"
M2 holds the string "code 2" further assume the Region Name is in the Sales column in each case Match(M1,A1:G1,0) give the column for region 1 Match(M1,A1:G1,0)+1 gives the Revenue column for Region 1 Match(M2,A1:A100,0) gives the row for code 2 Put those in the index function =Index(A1:G100,Match(M2,A1:A100,0),Match(M1,A1:G1, 0)+1) gives the revenue for the appropriate code. -- Regards, Tom Ogilvy "Matt" wrote: I am trying to return information using look-ups by looking up info that is in a column and a row....so Region 1 Region 2 Region 3 Sales Revenue Sales Revenue Sales Revenue Code 1 Code 2 Code 3 So, say I want the revenue for Code 1 and Region 1 returned in a report....I'm just not sure of the syntax....do I create a name reference and do a look-up from that? Something like lookup(Code1|Region1,Cell References,2,False)? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pulling "Matrix" Data
Hello Nick - thanks for the response....I'm actually summarizing in a pivot
table...I want to then pull that information into a report that is dynamic....i.e. the user has the abilility to get the report for all codes, all regions, specific regions, etc.... "NickHK" wrote: Matt, Don't know the source of your data, but have you looked into Pivot Tables ? NickHK "Matt" ... I am trying to return information using look-ups by looking up info that is in a column and a row....so Region 1 Region 2 Region 3 Sales Revenue Sales Revenue Sales Revenue Code 1 Code 2 Code 3 So, say I want the revenue for Code 1 and Region 1 returned in a report....I'm just not sure of the syntax....do I create a name reference and do a look-up from that? Something like lookup(Code1|Region1,Cell References,2,False)? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pulling "Matrix" Data
Thank you Tom!
"Tom Ogilvy" wrote: Assume M1 holds the string "Region1" M2 holds the string "code 2" further assume the Region Name is in the Sales column in each case Match(M1,A1:G1,0) give the column for region 1 Match(M1,A1:G1,0)+1 gives the Revenue column for Region 1 Match(M2,A1:A100,0) gives the row for code 2 Put those in the index function =Index(A1:G100,Match(M2,A1:A100,0),Match(M1,A1:G1, 0)+1) gives the revenue for the appropriate code. -- Regards, Tom Ogilvy "Matt" wrote: I am trying to return information using look-ups by looking up info that is in a column and a row....so Region 1 Region 2 Region 3 Sales Revenue Sales Revenue Sales Revenue Code 1 Code 2 Code 3 So, say I want the revenue for Code 1 and Region 1 returned in a report....I'm just not sure of the syntax....do I create a name reference and do a look-up from that? Something like lookup(Code1|Region1,Cell References,2,False)? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pulling "Matrix" Data
Matt wrote:
I am trying to return information using look-ups by looking up info that is in a column and a row....so Region 1 Region 2 Region 3 Sales Revenue Sales Revenue Sales Revenue Code 1 Code 2 Code 3 So, say I want the revenue for Code 1 and Region 1 returned in a report....I'm just not sure of the syntax....do I create a name reference and do a look-up from that? Something like lookup(Code1|Region1,Cell References,2,False)? I would recommend the following kind of matrix: Sales1 Revenue1 Sales2 Revenue2 Sales3 Revenue3 Code1 amtA amtB amtC amtD amtE amtF Code2 amtG amtH amtI amtJ amtK amtL Code3 amtM amtN amtO amtP amtQ amtR Then highlight the tabLE including column and row headings, click Insert|Name|Create, check Top row and Left column, click OK Then =Code1 Sales1 will return amtA, =Code2 Revenue3 will return amtL And =Code3 Sales3&", "&Revenue3 will return amtQ, amtR Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Pulling rows from tabs to a "summary" sheet | Excel Worksheet Functions | |||
Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A" | Excel Discussion (Misc queries) | |||
Help! Boss needs this ASAP - I am pulling out my hair here folks use of "&" in HEADER | Excel Worksheet Functions | |||
Adding "New" "Insert" "Delete" into a workbook to change from data 1 to data 2 etc | Excel Programming |