Hi - I have searched and searched for the correct approach to solving my problem and have gotten only close. Any help will be much appreciated.
I need to be able to search a range of cells (one column only), most of which do not have data in them, and have all the non zero values returned on a different worksheet, in a range that's a different size. For example:
Sheet 1 has:
1|
2|
3| .05
4|
5|
6| .02
7|
8|
9|
10| .01
And I need Sheet 2 to show:
1| .05
2| .02
3| .01
4|
5|
For one, the data entered will not always be entered in rows 3, 6 and 10, so it needs to be able to search all the rows. We do however know that there will be no more than 5 data entries (this is the size of the destination range).
Second, the fact that the source range and the destination range are different sizes can't be changed.
Third, Sheet 2 needs to auto-update when a value on Sheet 1 changes. This is why a filter wasn't enough, unless I was just missing something.
If the blank cells are an issue, zeros can be inserted.
Any insight here will be greatly appreciated. I have struggled to find the right formula/function for this and can't find one that meets each of the requirements.
Thank you!
Bookmarks