Darleene
08-18-2022, 05:28 AM
Hello,
I am a complete beginner and would like to ask whether it is at all possible to automate the following steps:
Once in the Input worksheet (or message box etc.) the user manually keys in InputEntity (e.g. White Ltd), InputExpenseType (e.g. ExpenseType 2) and InputValue (e.g. 450):
1. In the Master Lookup worksheet:
a) find Entity in column A (example: White Ltd is in cell A6 of the Master Lookup worksheet)
b) grab the value from the same row as the entity but column B to determine the relevant regional lookup (example: for White Ltd in cell A6 the relevant regional lookup is in cell B6 - Regional Lookup 2)
2. In the relevant regional lookup worksheet:
a) use the InputExpenseType to determine the right column (example: in the Regional Lookup 2 for InputExpenseType=ExpenseType 2 it is column C)
b) in the relevant column use the InputValue to retrieve the relevant row range. The listed values are maximum, up to values (example: in the Regional Lookup 2 for InputExpenseType=ExpenseType 2 and InputValue=450 the row range is 2:7)
c) use the row range determined under 2.b) to retrieve the values from the corresponding cells in column A (example: for rows 2:7 these would be cells A2:A7, so Levels between 65 and 100)
3. In the Email worksheet:
a) use the InputEntity and Level(s) determined under 2.c) to determine the right email(s) (example: according to the Email tab, for InputEntity=White Ltd the emails for Levels between 65 and 100 are f_AT_sample.com and b_AT_sample.com)
4. Provide the email(s) determined under 3.) to the user (in a cell, message box or other)
I would love to figure it out myself - am going through a VBA handbook - but I can only find bits and pieces of potential solution, I don't have the overall big picture. Any guidance would be appreciated (what key words to be looking for, the actual code etc.).
Thanks for reading : )
I am a complete beginner and would like to ask whether it is at all possible to automate the following steps:
Once in the Input worksheet (or message box etc.) the user manually keys in InputEntity (e.g. White Ltd), InputExpenseType (e.g. ExpenseType 2) and InputValue (e.g. 450):
1. In the Master Lookup worksheet:
a) find Entity in column A (example: White Ltd is in cell A6 of the Master Lookup worksheet)
b) grab the value from the same row as the entity but column B to determine the relevant regional lookup (example: for White Ltd in cell A6 the relevant regional lookup is in cell B6 - Regional Lookup 2)
2. In the relevant regional lookup worksheet:
a) use the InputExpenseType to determine the right column (example: in the Regional Lookup 2 for InputExpenseType=ExpenseType 2 it is column C)
b) in the relevant column use the InputValue to retrieve the relevant row range. The listed values are maximum, up to values (example: in the Regional Lookup 2 for InputExpenseType=ExpenseType 2 and InputValue=450 the row range is 2:7)
c) use the row range determined under 2.b) to retrieve the values from the corresponding cells in column A (example: for rows 2:7 these would be cells A2:A7, so Levels between 65 and 100)
3. In the Email worksheet:
a) use the InputEntity and Level(s) determined under 2.c) to determine the right email(s) (example: according to the Email tab, for InputEntity=White Ltd the emails for Levels between 65 and 100 are f_AT_sample.com and b_AT_sample.com)
4. Provide the email(s) determined under 3.) to the user (in a cell, message box or other)
I would love to figure it out myself - am going through a VBA handbook - but I can only find bits and pieces of potential solution, I don't have the overall big picture. Any guidance would be appreciated (what key words to be looking for, the actual code etc.).
Thanks for reading : )