Advanced Excel Lookup and Analysis
Budget: $30 – $250 USD
I'm looking for an Excel expert to create a robust three-level lookup function that can handle text data. The primary goal of this function is to facilitate data analysis, and it needs to be followed by basic arithmetic operations based on the result of the lookups.
Key requirements:
- The Excel function should have a three-level lookup that can handle text data.
- The primary goal of the function is data analysis.
- After the lookup, basic arithmetic operations should be performed based on the result.
- The work needs to "live" in only two places - the reference table worksheet, and the worksheet doing the calculations. Because of how I'm structuring multiple sheets, I can't have a "scratch pad" sheet with intermediate lists.
- The lookup will live in many different rows, performing lookups/calculations in each row based on selections
- Because of the above, I believe it needs to be done via VBA, possibly by storing arrays and setting variables for row references.
Ideal skills for the job:
- Advanced Excel skills, particularly in lookup functions.
- Experience in data analysis in Excel.
- Proficiency in creating Excel functions for basic arithmetic operations.
- Visual Basic
I've attached the reference table, which will likely grow over time as we include different pipe materials/types. The pulldowns will be used to calculate things like velocity in the pipe, friction loss through the pipe, etc. on a row-by-row basis. One row might be 4" steel schedule 40, the next row might be 2" copper type L. So each row needs to reference the values from the correct row in the table (select the corresponding internal diameter, for example).
I believe it may be most practical to use the dropdowns to set a cell in a hidden column as the value of the row number from the table that corresponds to the selections, provided subsequent cells in the row can use that to correctly pull data from the table. This is where I'm over my skis in Excel skills.
Key requirements:
- The Excel function should have a three-level lookup that can handle text data.
- The primary goal of the function is data analysis.
- After the lookup, basic arithmetic operations should be performed based on the result.
- The work needs to "live" in only two places - the reference table worksheet, and the worksheet doing the calculations. Because of how I'm structuring multiple sheets, I can't have a "scratch pad" sheet with intermediate lists.
- The lookup will live in many different rows, performing lookups/calculations in each row based on selections
- Because of the above, I believe it needs to be done via VBA, possibly by storing arrays and setting variables for row references.
Ideal skills for the job:
- Advanced Excel skills, particularly in lookup functions.
- Experience in data analysis in Excel.
- Proficiency in creating Excel functions for basic arithmetic operations.
- Visual Basic
I've attached the reference table, which will likely grow over time as we include different pipe materials/types. The pulldowns will be used to calculate things like velocity in the pipe, friction loss through the pipe, etc. on a row-by-row basis. One row might be 4" steel schedule 40, the next row might be 2" copper type L. So each row needs to reference the values from the correct row in the table (select the corresponding internal diameter, for example).
I believe it may be most practical to use the dropdowns to set a cell in a hidden column as the value of the row number from the table that corresponds to the selections, provided subsequent cells in the row can use that to correctly pull data from the table. This is where I'm over my skis in Excel skills.