Dynamic_Cell_Selector_Based_on_Predefind_Number_of_Cells_Counter_Forward

[david gabra]

Hi
Mission: To populate D6 and D7 with the name if the selected based on your formulae.

Range of cells: G2 to X2

The formula must be one I can copy for 10000s of examples.

1) To populate G6 - Based on step cell forward counter in D6
I need a formula that should now find the first cell output based on the value in D6. This number tell excel how many cells to move forward starting from G2.

In my scenario, D6 =2

Step 1 - Moves to the first cell with a value which is ZE (value in G2)
Step 2 - Moves to the next (second) cell with a value which is O2 (skips I2, K2, M2 since cells are empty)


2) To populate G7 - Based on step step cell forward counter in D7
I need a formula that should now find the cell output based on the value in D7. This number tell excel how many cells to move forward starting from G2.

In my scenario, D6 =4 (basically find the 4 cells that is not blank)

Step 1 - Moves to the first cell with a value which is ZE (value in G2)
Step 2 - Moves to the next (second) cell with a value which is O2 (skips I2, K2, M2 since cells are empty)
Step 3 - Moves to the third cell with a value which is O2 (value in O2)
Step 4 - Moves to the fourth cell with a value which is S3 (since skips out Q2 - No text/empty)

I have a sample table of the results we want to automate - in cells E6 / E7

Please refer to the attached excel.

Thank you in advance so much for time and effort helping.

Dave

 

[david gabra]

Is there anything I can do to make it clearer - Thanks

 

[Debaser]

What does this mean:
"Moves to the first cell with a value which is ZE (value in G2)"
G2 contains A, not ZE.

Also, and probably more importantly, which version of Excel do you have?

 

[david gabra]

Hi
I have excel version - Microsoft 365 - the easier question

To clarify: - Sorry for the typos

For 2 steps forward (finding second non blank)

Step 1 - Moves to the first cell with a value which is A (value in G2) - THEN Step 2 - Moves to the next (second populated) cell with a value which is O2 (skips I2, K2, M2 since cells are empty)

For 4 steps forward (finding 4th non blank) Step 1 - Moves to the first cell with a value which is A (value in G2) - Then Step 2 - Moves to the next (second populated) cell with a value which is O2 (skips I2, K2, M2 since cells are empty) - Then Step 3 - Moves to the third cell with a value which is G (cell S2) - Then Step 4 - Moves to the fourth cell with a value which is H (since skips out Q2 - No text/empty and goes to U2)

.The 2 and 4 are found in cells D6 and D7


Thank you

 

[Debaser]

In that case:
=INDEX(FILTER($G$2:$X$2,(ISNUMBER(SEARCH("Employee Name",$G$1:$X$1)))*($G$2:$X$2<>"")),D6)

 

[david gabra]

Amazing - thank you kindly. It works perfectly.

 

[Debaser]

You're welcome.

 

[david gabra]

I need a slight upgrade please.
If the value in the cell after "Employee Name" is 0, then move to skip that cell.
Can you please modify

 

[Debaser]

You could use:
=INDEX(FILTER($G$2:$W$2,(ISNUMBER(SEARCH("Employee Name",$G$1:$W$1)))*($G$2:$W$2<>"")*($H$2:$X$2<>0)),D6)