Hi,
I have attached the sample workbook for better understanding.
In the file I have 4 sheets.
Sheet1 - I contains details which is linked with sheet2. Column P named "Battery" with the dates as cell values. If the date reached 2 yrs from the date specified in the cell value, it should pick in the sheet 2 else the column "Battery" in sheet 2 will be blank.
Sheet2 - This is the source sheet for the datas to be filled in sheet3. And also the range criteria is in Sheet4. For the particular column ex 201 if value in the cell is between 1.1 and 1.2 then those cell values to be picked in sheet2. Similary for the column names range has been fixed and if the cell value with in tat range all the values to be picked in the sheet3 with the cell value and the column name in the specific columns of sheet3.
Below are the example of column names and the range and these are in sheet4 of the file.
201 201 201-3 201-3
>=1.1 <=1.2 >=1.1 <=1.2
Contract No. - This is standard value - Say 4235
EQUIPMENT # - This should be picked from the sheet 2. For the column value and cell value equipment # is picked.
Description - Value is "Plaza Tower + Tower (specified in column C of sheet 2)
BILLABLE (Yes/ No) - Value is standard "No"
PART DESCRIPTION - if it is contractor, then contractor else column name
PART Number - This should be picked from the sheet 4 based on Column name, Drive
QTY - Always one
deal - This should be picked from Sheet 2 again based on the column name, drive and part description
other deal - This should be picked from Sheet 2 again based on the column name, drive and part description
PRT - if it is "ADD" or "Brake" or "Battery" then it is "Replacing" + column Name, Else "Replacing Contractor".
PLAN HOUR - This should be picked from Sheet 2 again based on the column name, drive and part description
Planned DATE - One month from the date specified in K3
REMARKS
SAP Order No.
Column Name - Column name of the value with in the range
Cell value - Value within the range
Drive - Drive from sheet2 for the particular column and cell value.
Except for the "ADD" or "Brake" or "Battery" for all the contractor one line to be inserted after the line. One for "contractor" and other for "Aux". and the details for the inserted line to be filled accordingly.
After filling all the details the file to be saved in the "C:" drive with the file name as "Repair details + Todays Date". and sheet 3 to be mailed (in draft) as an attachment to the below list.
I have attached the sample workbook for better understanding.
In the file I have 4 sheets.
Sheet1 - I contains details which is linked with sheet2. Column P named "Battery" with the dates as cell values. If the date reached 2 yrs from the date specified in the cell value, it should pick in the sheet 2 else the column "Battery" in sheet 2 will be blank.
Sheet2 - This is the source sheet for the datas to be filled in sheet3. And also the range criteria is in Sheet4. For the particular column ex 201 if value in the cell is between 1.1 and 1.2 then those cell values to be picked in sheet2. Similary for the column names range has been fixed and if the cell value with in tat range all the values to be picked in the sheet3 with the cell value and the column name in the specific columns of sheet3.
Below are the example of column names and the range and these are in sheet4 of the file.
201 201 201-3 201-3
>=1.1 <=1.2 >=1.1 <=1.2
Contract No. - This is standard value - Say 4235
EQUIPMENT # - This should be picked from the sheet 2. For the column value and cell value equipment # is picked.
Description - Value is "Plaza Tower + Tower (specified in column C of sheet 2)
BILLABLE (Yes/ No) - Value is standard "No"
PART DESCRIPTION - if it is contractor, then contractor else column name
PART Number - This should be picked from the sheet 4 based on Column name, Drive
QTY - Always one
deal - This should be picked from Sheet 2 again based on the column name, drive and part description
other deal - This should be picked from Sheet 2 again based on the column name, drive and part description
PRT - if it is "ADD" or "Brake" or "Battery" then it is "Replacing" + column Name, Else "Replacing Contractor".
PLAN HOUR - This should be picked from Sheet 2 again based on the column name, drive and part description
Planned DATE - One month from the date specified in K3
REMARKS
SAP Order No.
Column Name - Column name of the value with in the range
Cell value - Value within the range
Drive - Drive from sheet2 for the particular column and cell value.
Except for the "ADD" or "Brake" or "Battery" for all the contractor one line to be inserted after the line. One for "contractor" and other for "Aux". and the details for the inserted line to be filled accordingly.
After filling all the details the file to be saved in the "C:" drive with the file name as "Repair details + Todays Date". and sheet 3 to be mailed (in draft) as an attachment to the below list.