Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index on another worksheet
I'm trying to create a purchase order system. What I would like to do is
page one titled "Order" I have 3 columns; A (Department), B (Part Number), and C (Quantity). Once the person fills out the required info they will click on a "submit" form control button. What I would like to have happen next is, the part number inserted in column B will index the same part number in column B on the page two titled "Cut List". I would like a message box to come up with the information from columns F and G of the same row that was indexed earlier. Column F (Order Quantity), column G (Need By Date). If F and G are blank I want "Cut List" column E to have today's date, "Cut List" column F, the information from "Order" column C, and so on. If there is information in Column F and G, I need then to have the option of adding the quantity of parts to the existing Need By Date or creating a new need by date. If a new date is required the information from "Order" would be put into "Cut List" column J-L. For the most part, I really need to know how to index a cell from one worksheet and match the same part number on another worksheet and make that row active. I could probably figure out the rest but thought if anyone has done something similar I could just use your idea. Thanks in advance for any help or ideas you may provide. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index on another worksheet
the part number inserted in column B will index the same part number
in column B on the page two titled "Cut List". What does the above statement mean? Does it mean to find a matching part number? or Does it mean to add to a list and then sort the list? INDEX is a function of Excel that can be used to return values from arrays or to return references. You need to clarify the statement. "Cerberus" wrote in message ... I'm trying to create a purchase order system. What I would like to do is page one titled "Order" I have 3 columns; A (Department), B (Part Number), and C (Quantity). Once the person fills out the required info they will click on a "submit" form control button. What I would like to have happen next is, the part number inserted in column B will index the same part number in column B on the page two titled "Cut List". I would like a message box to come up with the information from columns F and G of the same row that was indexed earlier. Column F (Order Quantity), column G (Need By Date). If F and G are blank I want "Cut List" column E to have today's date, "Cut List" column F, the information from "Order" column C, and so on. If there is information in Column F and G, I need then to have the option of adding the quantity of parts to the existing Need By Date or creating a new need by date. If a new date is required the information from "Order" would be put into "Cut List" column J-L. For the most part, I really need to know how to index a cell from one worksheet and match the same part number on another worksheet and make that row active. I could probably figure out the rest but thought if anyone has done something similar I could just use your idea. Thanks in advance for any help or ideas you may provide. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index on another worksheet
Sorry about the vague information or improper use of terms. I wish I knew
the proper terms so I could utilize the books I have more effectivly. I want it to match the same part number from a list of 2066 parts located on page two (Cut List). "JLGWhiz" wrote: the part number inserted in column B will index the same part number in column B on the page two titled "Cut List". What does the above statement mean? Does it mean to find a matching part number? or Does it mean to add to a list and then sort the list? INDEX is a function of Excel that can be used to return values from arrays or to return references. You need to clarify the statement. "Cerberus" wrote in message ... I'm trying to create a purchase order system. What I would like to do is page one titled "Order" I have 3 columns; A (Department), B (Part Number), and C (Quantity). Once the person fills out the required info they will click on a "submit" form control button. What I would like to have happen next is, the part number inserted in column B will index the same part number in column B on the page two titled "Cut List". I would like a message box to come up with the information from columns F and G of the same row that was indexed earlier. Column F (Order Quantity), column G (Need By Date). If F and G are blank I want "Cut List" column E to have today's date, "Cut List" column F, the information from "Order" column C, and so on. If there is information in Column F and G, I need then to have the option of adding the quantity of parts to the existing Need By Date or creating a new need by date. If a new date is required the information from "Order" would be put into "Cut List" column J-L. For the most part, I really need to know how to index a cell from one worksheet and match the same part number on another worksheet and make that row active. I could probably figure out the rest but thought if anyone has done something similar I could just use your idea. Thanks in advance for any help or ideas you may provide. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index on another worksheet
This assumes that Columns F and G are on the second sheet.
Private Sub CommandButton1_Click() Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long Dim c As Range Set sh1 = Sheets("Order") Set sh2 = Sheets("Cut List") lr = sh2.Cells(Rows.Count, 2).End(xlUp).Row Set rng = sh2.Range("B2:B" & lr) Set c = rng.Find(sh1.Range("B" & ActiveCell.Row).Value, LookIn:=xlValues) If Not c Is Nothing Then MsgBox "Col F Value is: " & sh2.Range("F" & c.Row) & vbCrLf _ & "Col G value is: " & sh2.Range("G" & c.Row) End If End Sub I did not address the need date change because there is no criteria to establish when that decision would be needed. This code uses a command button from the Control Toolbox, with the button being installed on the first sheet "Order". The code goes in the sheet code module. "Cerberus" wrote in message ... Sorry about the vague information or improper use of terms. I wish I knew the proper terms so I could utilize the books I have more effectivly. I want it to match the same part number from a list of 2066 parts located on page two (Cut List). "JLGWhiz" wrote: the part number inserted in column B will index the same part number in column B on the page two titled "Cut List". What does the above statement mean? Does it mean to find a matching part number? or Does it mean to add to a list and then sort the list? INDEX is a function of Excel that can be used to return values from arrays or to return references. You need to clarify the statement. "Cerberus" wrote in message ... I'm trying to create a purchase order system. What I would like to do is page one titled "Order" I have 3 columns; A (Department), B (Part Number), and C (Quantity). Once the person fills out the required info they will click on a "submit" form control button. What I would like to have happen next is, the part number inserted in column B will index the same part number in column B on the page two titled "Cut List". I would like a message box to come up with the information from columns F and G of the same row that was indexed earlier. Column F (Order Quantity), column G (Need By Date). If F and G are blank I want "Cut List" column E to have today's date, "Cut List" column F, the information from "Order" column C, and so on. If there is information in Column F and G, I need then to have the option of adding the quantity of parts to the existing Need By Date or creating a new need by date. If a new date is required the information from "Order" would be put into "Cut List" column J-L. For the most part, I really need to know how to index a cell from one worksheet and match the same part number on another worksheet and make that row active. I could probably figure out the rest but thought if anyone has done something similar I could just use your idea. Thanks in advance for any help or ideas you may provide. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index on another worksheet
P.S. If the user does not leave the cursor on the entry row while the
"Submit" button is clicked, the code fails. "Cerberus" wrote in message ... Sorry about the vague information or improper use of terms. I wish I knew the proper terms so I could utilize the books I have more effectivly. I want it to match the same part number from a list of 2066 parts located on page two (Cut List). "JLGWhiz" wrote: the part number inserted in column B will index the same part number in column B on the page two titled "Cut List". What does the above statement mean? Does it mean to find a matching part number? or Does it mean to add to a list and then sort the list? INDEX is a function of Excel that can be used to return values from arrays or to return references. You need to clarify the statement. "Cerberus" wrote in message ... I'm trying to create a purchase order system. What I would like to do is page one titled "Order" I have 3 columns; A (Department), B (Part Number), and C (Quantity). Once the person fills out the required info they will click on a "submit" form control button. What I would like to have happen next is, the part number inserted in column B will index the same part number in column B on the page two titled "Cut List". I would like a message box to come up with the information from columns F and G of the same row that was indexed earlier. Column F (Order Quantity), column G (Need By Date). If F and G are blank I want "Cut List" column E to have today's date, "Cut List" column F, the information from "Order" column C, and so on. If there is information in Column F and G, I need then to have the option of adding the quantity of parts to the existing Need By Date or creating a new need by date. If a new date is required the information from "Order" would be put into "Cut List" column J-L. For the most part, I really need to know how to index a cell from one worksheet and match the same part number on another worksheet and make that row active. I could probably figure out the rest but thought if anyone has done something similar I could just use your idea. Thanks in advance for any help or ideas you may provide. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Index on another worksheet
Thank you so very much.
"JLGWhiz" wrote: This assumes that Columns F and G are on the second sheet. Private Sub CommandButton1_Click() Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long Dim c As Range Set sh1 = Sheets("Order") Set sh2 = Sheets("Cut List") lr = sh2.Cells(Rows.Count, 2).End(xlUp).Row Set rng = sh2.Range("B2:B" & lr) Set c = rng.Find(sh1.Range("B" & ActiveCell.Row).Value, LookIn:=xlValues) If Not c Is Nothing Then MsgBox "Col F Value is: " & sh2.Range("F" & c.Row) & vbCrLf _ & "Col G value is: " & sh2.Range("G" & c.Row) End If End Sub I did not address the need date change because there is no criteria to establish when that decision would be needed. This code uses a command button from the Control Toolbox, with the button being installed on the first sheet "Order". The code goes in the sheet code module. "Cerberus" wrote in message ... Sorry about the vague information or improper use of terms. I wish I knew the proper terms so I could utilize the books I have more effectivly. I want it to match the same part number from a list of 2066 parts located on page two (Cut List). "JLGWhiz" wrote: the part number inserted in column B will index the same part number in column B on the page two titled "Cut List". What does the above statement mean? Does it mean to find a matching part number? or Does it mean to add to a list and then sort the list? INDEX is a function of Excel that can be used to return values from arrays or to return references. You need to clarify the statement. "Cerberus" wrote in message ... I'm trying to create a purchase order system. What I would like to do is page one titled "Order" I have 3 columns; A (Department), B (Part Number), and C (Quantity). Once the person fills out the required info they will click on a "submit" form control button. What I would like to have happen next is, the part number inserted in column B will index the same part number in column B on the page two titled "Cut List". I would like a message box to come up with the information from columns F and G of the same row that was indexed earlier. Column F (Order Quantity), column G (Need By Date). If F and G are blank I want "Cut List" column E to have today's date, "Cut List" column F, the information from "Order" column C, and so on. If there is information in Column F and G, I need then to have the option of adding the quantity of parts to the existing Need By Date or creating a new need by date. If a new date is required the information from "Order" would be put into "Cut List" column J-L. For the most part, I really need to know how to index a cell from one worksheet and match the same part number on another worksheet and make that row active. I could probably figure out the rest but thought if anyone has done something similar I could just use your idea. Thanks in advance for any help or ideas you may provide. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet Tab index | Excel Discussion (Misc queries) | |||
selecting worksheet by its index. | Excel Programming | |||
How to use worksheet function Index | Excel Programming | |||
using index, match on another worksheet | Excel Worksheet Functions | |||
resequence worksheet index after deletion of worksheet | Excel Programming |