Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for multiple heading indexing
I am trying to create a UDF that will index through multiple rows of
headings. The top heading in Row 1 is companies. Company 1 is in merged cells B1:G1. Co. 2 is in merged cells H1:M1. Under this row of headings is a sub heading of programs that are offered by each company. They are in B2:C2, D2:E2, F2:G2 for co. 1. They are in H2:I2, J2:K2, L2:M2 for co. 2. Under this row of sub headings is another sub heading of quantities of either 10 or 20 for each program. These are in cells B3 as 10 C3 as 20 for program 1. D3 as 10 E3 as 20 for progrmam 2.... So on. I have item numbers down the left hand side starting at A4. What is being indexed is the price of the product. Is there a way to create a UDF that will let you filter down to the correct row and column and index? For instance if I had Item # 3 that would be row 6. If I had company 1 that woudl be cells B1:G1. I also needed program 2 which is D2:E2, and a quantity of 20 which is in column E. How to get it to be the answer of the value of E6? I am not set on UDF, a form or other option to go about accomplishing this task woudl be greatly appreciated. Thanks, Jay |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for multiple heading indexing
Function FindValue(Co As Variant, Prog As Variant, Qty As Variant, Item As
Variant) Dim cell1 As Range Dim cell2 As Range Dim cell3 As Range Set cell1 = Rows(1).Find(Co) Set cell2 = cell1.Offset(1, 0).Resize(, 6).Find(Prog) Set cell3 = cell2.Offset(1, 0).Resize(, 2).Find(Qty) FindValue = Cells(Application.Match(Item, Columns(1), 0), cell3.Column).Value End Function -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jlclyde" wrote in message ... I am trying to create a UDF that will index through multiple rows of headings. The top heading in Row 1 is companies. Company 1 is in merged cells B1:G1. Co. 2 is in merged cells H1:M1. Under this row of headings is a sub heading of programs that are offered by each company. They are in B2:C2, D2:E2, F2:G2 for co. 1. They are in H2:I2, J2:K2, L2:M2 for co. 2. Under this row of sub headings is another sub heading of quantities of either 10 or 20 for each program. These are in cells B3 as 10 C3 as 20 for program 1. D3 as 10 E3 as 20 for progrmam 2.... So on. I have item numbers down the left hand side starting at A4. What is being indexed is the price of the product. Is there a way to create a UDF that will let you filter down to the correct row and column and index? For instance if I had Item # 3 that would be row 6. If I had company 1 that woudl be cells B1:G1. I also needed program 2 which is D2:E2, and a quantity of 20 which is in column E. How to get it to be the answer of the value of E6? I am not set on UDF, a form or other option to go about accomplishing this task woudl be greatly appreciated. Thanks, Jay |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for multiple heading indexing
On Apr 24, 8:40*am, "Bob Phillips" wrote:
Function FindValue(Co As Variant, Prog As Variant, Qty As Variant, Item As Variant) Dim cell1 As Range Dim cell2 As Range Dim cell3 As Range * * Set cell1 = Rows(1).Find(Co) * * Set cell2 = cell1.Offset(1, 0).Resize(, 6).Find(Prog) * * Set cell3 = cell2.Offset(1, 0).Resize(, 2).Find(Qty) * * FindValue = Cells(Application.Match(Item, Columns(1), 0), cell3.Column).Value End Function -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jlclyde" wrote in message ... I am trying to create a UDF that will index through multiple rows of headings. *The top heading in Row 1 is companies. *Company 1 is in merged cells B1:G1. *Co. 2 is in merged cells H1:M1. *Under this row of headings is a sub heading of programs that are offered by each company. They are in B2:C2, D2:E2, F2:G2 for co. 1. *They are in H2:I2, J2:K2, L2:M2 for co. 2. *Under this row of sub headings is another sub heading of quantities of either 10 or 20 for each program. *These are in cells B3 as 10 C3 as 20 for program 1. *D3 as 10 E3 as 20 for progrmam 2.... So on. *I have item numbers down the left hand side starting at A4. *What is being indexed is the price of the product. Is there a way to create a UDF that will let you filter down to the correct row and column and index? *For instance if I had Item # 3 that would be row 6. *If I had company 1 that woudl be cells B1:G1. *I also needed program 2 which is D2:E2, and a quantity of 20 which is in column E. *How to get it to be the answer of the value of E6? *I am not set on UDF, a form or other option to go about accomplishing this task woudl *be greatly appreciated. Thanks, Jay- Hide quoted text - - Show quoted text - Bob, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for multiple heading indexing
On Apr 24, 8:40*am, "Bob Phillips" wrote:
Function FindValue(Co As Variant, Prog As Variant, Qty As Variant, Item As Variant) Dim cell1 As Range Dim cell2 As Range Dim cell3 As Range * * Set cell1 = Rows(1).Find(Co) * * Set cell2 = cell1.Offset(1, 0).Resize(, 6).Find(Prog) * * Set cell3 = cell2.Offset(1, 0).Resize(, 2).Find(Qty) * * FindValue = Cells(Application.Match(Item, Columns(1), 0), cell3.Column).Value End Function -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jlclyde" wrote in message ... I am trying to create a UDF that will index through multiple rows of headings. *The top heading in Row 1 is companies. *Company 1 is in merged cells B1:G1. *Co. 2 is in merged cells H1:M1. *Under this row of headings is a sub heading of programs that are offered by each company. They are in B2:C2, D2:E2, F2:G2 for co. 1. *They are in H2:I2, J2:K2, L2:M2 for co. 2. *Under this row of sub headings is another sub heading of quantities of either 10 or 20 for each program. *These are in cells B3 as 10 C3 as 20 for program 1. *D3 as 10 E3 as 20 for progrmam 2.... So on. *I have item numbers down the left hand side starting at A4. *What is being indexed is the price of the product. Is there a way to create a UDF that will let you filter down to the correct row and column and index? *For instance if I had Item # 3 that would be row 6. *If I had company 1 that woudl be cells B1:G1. *I also needed program 2 which is D2:E2, and a quantity of 20 which is in column E. *How to get it to be the answer of the value of E6? *I am not set on UDF, a form or other option to go about accomplishing this task woudl *be greatly appreciated. Thanks, Jay- Hide quoted text - - Show quoted text - Bob, This is great. I did nto realize that it coudl be this easy. I was doing all sorts of weird stuff with Address and I was constanly resizing an array. Thanks for the help, Jay |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for multiple heading indexing
Be aware that there is no error checking so it does assume well formatted
inputs, but you will probably just get #VALUE if there is anything wrong. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jlclyde" wrote in message ... On Apr 24, 8:40 am, "Bob Phillips" wrote: Function FindValue(Co As Variant, Prog As Variant, Qty As Variant, Item As Variant) Dim cell1 As Range Dim cell2 As Range Dim cell3 As Range Set cell1 = Rows(1).Find(Co) Set cell2 = cell1.Offset(1, 0).Resize(, 6).Find(Prog) Set cell3 = cell2.Offset(1, 0).Resize(, 2).Find(Qty) FindValue = Cells(Application.Match(Item, Columns(1), 0), cell3.Column).Value End Function -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jlclyde" wrote in message ... I am trying to create a UDF that will index through multiple rows of headings. The top heading in Row 1 is companies. Company 1 is in merged cells B1:G1. Co. 2 is in merged cells H1:M1. Under this row of headings is a sub heading of programs that are offered by each company. They are in B2:C2, D2:E2, F2:G2 for co. 1. They are in H2:I2, J2:K2, L2:M2 for co. 2. Under this row of sub headings is another sub heading of quantities of either 10 or 20 for each program. These are in cells B3 as 10 C3 as 20 for program 1. D3 as 10 E3 as 20 for progrmam 2.... So on. I have item numbers down the left hand side starting at A4. What is being indexed is the price of the product. Is there a way to create a UDF that will let you filter down to the correct row and column and index? For instance if I had Item # 3 that would be row 6. If I had company 1 that woudl be cells B1:G1. I also needed program 2 which is D2:E2, and a quantity of 20 which is in column E. How to get it to be the answer of the value of E6? I am not set on UDF, a form or other option to go about accomplishing this task woudl be greatly appreciated. Thanks, Jay- Hide quoted text - - Show quoted text - Bob, This is great. I did nto realize that it coudl be this easy. I was doing all sorts of weird stuff with Address and I was constanly resizing an array. Thanks for the help, Jay |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Indexing | Excel Worksheet Functions | |||
Multiple headings for indexing | Excel Discussion (Misc queries) | |||
pivot tables using same heading to show multiple data information | Charts and Charting in Excel | |||
Indexing data in Multiple Columns | Excel Discussion (Misc queries) | |||
How do I keep heading so it prints on multiple pages | Excel Discussion (Misc queries) |