Pulling info consistently from two cells right of original target

Copper Contributor

Thanks for helping. I Have one cell (S4) where I am using ‘=C1’ to extract info. I would like to pull info from E1 (two cells to the right) and place in S5. Further down, I want to use ‘=C5’ in cell S6 and automatically pull info from E5 to place in S7. The draw will always be from the second cell to the right of the first one.  This will be movement between sheets. Any ideas?

Second possible question: Can I ask it to look up the number in S4 on sheet 2, using only column C on sheet one, then pull the info from two columns to the right of that cell (E on sheet one) and place in S5 on sheet 2? Thanks.

8 Replies
This is potentially very simple depending on what else is in the sheet near the target cells. Are you able to share an anonymized sample of the arrangement?

@Rick_Lehnen 

=IF(ISEVEN(ROW()),INDEX(C:C,ROW($C$2)*(ROW(C1)-ROW($C$1))+1),INDEX(E:E,ROW($C$2)*(ROW(C1)-ROW($C$1))-1))

Does this return the expected result?

pulling data.JPG 

@Patrick2788 Actuals:

1 Handout Sheet.JPG

1 Pricing 2.JPG

 Here we go.....Start at 'Handout" D8. Notice C8. Find 670306 in Column B on 'Pricing' (it's in B90). Pull info from C90 and place on 'Handout' at D8. It will make my life simpler......

When I entered the formula in S4, it highlighted columns C and E, but returned no result in S4. I'm not sure how that was supposed to work - got lost in the flow (which is very easy for me to do).

@Rick_Lehnen 

This is the idea:

data is a dynamic range

=LET(c,WRAPROWS(TAKE(data,,1),4),e,WRAPROWS(TAKE(data,,1),4),VSTACK(TAKE(c,,1),TAKE(e,,1)))

 

And you lost me again. I truly do want to understand.

@Rick_Lehnen 

Does it show the expected result when you open the attached file? If there isn't an entry in cell C1 then the formula returns 0 in cell S4. If E1 is empty the formula returns 0 in cell S5.

@Rick_Lehnen 

Here is what the formula is doing:

Patrick2788_0-1678748266393.png

 

If you're getting an error in your workbook, you'll have to provide some details.  Did you open the workbook?