Forum Discussion
Formula to Skip Cells
I need help creating a formula to return values from a single cell to multiple cells (repeated) and to return values to nonconsecutive cells. So far I have created three which do one or the other but when I try to expand or nest them it breaks. I just need it more dynamic and easy to maintain with fewer formulas, as my actual database is very large.
Copies the single value from Table 1 to multiple Crew members, but I would like it expanded to do the same with a single function and not have to copy paste and update every 4 cells.
=IF(LEFT(A2:A5,3)=LEFT('Table 1'!A2,3),IF('Table 1'!B2:C2="","",'Table 1'!B2:C2))
I would like to use a single formula to return all the values (such as Table 3) but need it put the values on the Tracker every four cells/rows. Like I have Table 2, but I had to copy the formula to each cell and change the autofill as it kept adding 4 (i.e. matching the row).
=IF('Table 2'!B2:C2="","",'Table 2'!B2:C2) **(I do not want to have to copy/paste then update too many formulas)
=IF('Table 3'!B2:C8="","",'Table 3'!B2:C8) **(can this be changed to populate every four cells?)
=IF(COUNT(SEARCH(LEFT($A2,3),$A$2:$A2))=1,INDEX(H$2:H$8,MATCH(LEFT($A2,3),LEFT($G$2:$G$8,3),0)),"")
This formula returns the intended result if i correctly understand what you want to do. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021. The formula is in cell B2 and filled across range B2:C29.
For illustration i've made an example where all the data is in one worksheet.
OliverScheurich Thank you so much. That looks perfect. What would I change to make it duplicate/repeat the values every four cells instead of returning blanks for 3?
=INDEX(H$2:H$8,MATCH(LEFT($A2,3),LEFT($G$2:$G$8,3),0))
You are welcome. If you want to return the result 4 times you can apply this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
