collin8579
New Member
- Joined
- Oct 31, 2017
- Messages
- 20
Hello, and thanks for taking the time to even look-
I have a complicated scenario for my level of skill that involves users pulling in their own set of columns based on user defined selections on Main sheet of the excel workbook.
These columns are sorted into "Results column", Item 1 and Item 2
The way I built it to be dynamic creates a results column that is a placeholder column for actual results.
I found the method to use Find and Replace with formulas, so I can replace the empty cells with specific formulas for calculations.
I have two issues yet to overcome and would like to see if any can help:
1. Make the find and replace Dynamic in respect that it doesn't use column names, but positions based on the position of that particular column
So if I have results column A in "Column 5", and the items used to calculate it in COlumn 6/7 (which will always be the case in terms of order), I want to be able to do it regardless of the column names in Column 5/6/7,, so the formulas in PQ would just say.. "Take the column one to the right, do this and compare it with one more to the right,, etc)
2.Have it so it runs on every set of columns where it is relevant.
The main sheet lets the users pick the milestones they want to actualize in the system, then the column names based on their input for the two items that get checked
Each user can pick different milestones, more or less, etc, than their counterparts so the formula that changes the placeholder columns into calculations needs to be dynamic too.
How would I go about applying a find/replace not only dynamically noted above, but dynamically for all appropriate columns without knowing the column names to hard code it.
Two items we can use if it would make it easier to calculate which columns are relevant- The main page has its own query that just creates a format for column names - so that can be used in a list (I think) to potentially help, but I'm not confident how to do it and chat gpt isn't being helpful.
Thank you
I have a complicated scenario for my level of skill that involves users pulling in their own set of columns based on user defined selections on Main sheet of the excel workbook.
These columns are sorted into "Results column", Item 1 and Item 2
The way I built it to be dynamic creates a results column that is a placeholder column for actual results.
I found the method to use Find and Replace with formulas, so I can replace the empty cells with specific formulas for calculations.
I have two issues yet to overcome and would like to see if any can help:
1. Make the find and replace Dynamic in respect that it doesn't use column names, but positions based on the position of that particular column
So if I have results column A in "Column 5", and the items used to calculate it in COlumn 6/7 (which will always be the case in terms of order), I want to be able to do it regardless of the column names in Column 5/6/7,, so the formulas in PQ would just say.. "Take the column one to the right, do this and compare it with one more to the right,, etc)
2.Have it so it runs on every set of columns where it is relevant.
The main sheet lets the users pick the milestones they want to actualize in the system, then the column names based on their input for the two items that get checked
Each user can pick different milestones, more or less, etc, than their counterparts so the formula that changes the placeholder columns into calculations needs to be dynamic too.
How would I go about applying a find/replace not only dynamically noted above, but dynamically for all appropriate columns without knowing the column names to hard code it.
Two items we can use if it would make it easier to calculate which columns are relevant- The main page has its own query that just creates a format for column names - so that can be used in a list (I think) to potentially help, but I'm not confident how to do it and chat gpt isn't being helpful.
Thank you
