Forum Discussion
Mitchyll2280
Nov 03, 2022Copper Contributor
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!
You are welcome. After the formulas are entered in cells A14 and K14 you can drag them down. The results are then displayed in ranges A14:A29 and K14:K29 for example as shown in the attached screenshot and file.
- OliverScheurichGold Contributor
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.
- Mitchyll2280Copper Contributor
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!
- OliverScheurichGold Contributor
You are welcome. After the formulas are entered in cells A14 and K14 you can drag them down. The results are then displayed in ranges A14:A29 and K14:K29 for example as shown in the attached screenshot and file.