Forum Discussion

Mitchyll2280's avatar
Mitchyll2280
Copper Contributor
Nov 03, 2022

Duplicating cell value on another sheet

Hello,

 

I am pretty new to excel and this community. I have some knowledge of doing formulas, but all pretty basic. So, what I would like to do is this:

If the value of cell "i2" in "sheet2" is greater than 0, then I would like that cell along with its description cell, "d2", to be duplicated on "sheet1", where the description would go in cell "a14", and value would go in cell "k14". Basically, once the document is finished, I'd like to be able to have any values greater than 0 on "sheet2" in row "i" to copy into row "k" of "sheet1" and would continue down as I add them (i.e., if 3 cells in column "i" had values greater than 0, then they would be inputted at "k14","k15", and "k16" and continue as such. The description cells would accompany those values as well, but would go in column "a".

 

Thank you for any assistance or help! Please let me know if you have any questions or if this is even possible! Thank you!

  • Mitchyll2280 

    This is the formula in cell A14 in sheet1:

    =IFERROR(INDEX(sheet2!$D$2:$D$25,SMALL(IF(sheet2!$I$2:$I$25>0,ROW(sheet2!$I$2:$I$25)-1),ROW(sheet2!A1))),"")

    This is the formula in cell K14 in sheet1:

    =IFERROR(INDEX(sheet2!$I$2:$I$25,SMALL(IF(sheet2!$I$2:$I$25>0,ROW(sheet2!$I$2:$I$25)-1),ROW(sheet2!A1))),"")

    Enter the formulas with ctrl+shift+enter if you don't work with Office365 or 2021.

    If you work with Excel 2019 or later you can apply FILTER function.

    • Mitchyll2280's avatar
      Mitchyll2280
      Copper Contributor

      OliverScheurich 

       

      Thank you! So the formulas work and will add the cell values into sheet 1, but only into Row 14. What it's doing is only taking the first value greater than 0 and adding it to Row 14. If any other values are greater than 0, they do not show up in Sheet 1 unless I erase the first value. Is there a way to have them added to the rows below when I have multiple values above 0?

       

      Thank you for your help!

Resources