LinkBack URL
About LinkBacks
How do you go about copying a column of cells via a formula to another sheet without any included blank (empty ) cells to the receiving destination ?
How do you go about copying a column of cells via a formula to another sheet without any included blank (empty ) cells to the receiving destination ?
You might try a macro.
If this answers your question, please mark your thread as Solved by selecting Thread Tools ( above your first post) and mark thread as Solved .
Please Login or Register to view this content.
Please check the Add Reputation link.
Try this...
Data Range
This array formula** entered in B2:
=IFERROR(INDEX(A:A,SMALL(IF(A$2:A$15<>"",ROW(A$2:A$15)),ROWS(C$2:C2))),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy down until you get blanks.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thanks for the reply.
Yes you could use a macro, but that's not the route I want to take. a solution via a formula would be ideal.
in your macro should SkipBlanks _:=False, not be SkipBlanks _:=True, ?
OK.
Thanks, but I ran the macro and it works.
It is "Transpose:=False"
Hi Tony,
Thanks for trying to help.
this is the formula I'm using based on your, it's not returning any values. I am entering using CSE for an array formula. Any ideas ?
=IFERROR(INDEX('Production'!I:'Production'!I,SMALL(IF('Production'!I$4:'Production'!I$262<>"",ROW('Production'!I$4:'Production'!I$262)),ROWS('Stocks'!P$20:P20))),"")
I'm assuming you're entering the formula on sheet Stocks in cell P20.
You don't need to repeat the sheet names when referencing a range. Try it like this:
=IFERROR(INDEX(Production!I:I,SMALL(IF(Production!I$4:I$262<>"",ROW(Production!I$4:I$262)),ROWS(P$20:P20))),"")
Still array entered.
Still nothing. this is a sample of the data layout.
7 May 2015
12 May 2015
17 May 2015
22 May 2015
27 May 2015
1 June 2015
6 June 2015
Tony,
it's working just fine, my fat fingers !
Thank you very much for you help.
Here's a sample file...
Good deal. Thanks for the feedback!
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
I have a relatively similar task. I have tried the above formula as an array and another almost identical one but with the added parameter of ISTEXT included. Each one pulls about 1/2 the data. All the values appear in one list or the other with no duplicates. I have carefully made formatted all cells to be currencies. Not sure what is going on.
Administrative Note:Originally Posted by Craig Viau
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks