I am trying to pull 3 cells of data (out of an entire row of data)based on one criteria (>0) from one spreadsheet onto another spreadsheet. I have used a basic IF formula but that results in blank lines when the critera is false. How do I write the formula so when the criteria is true the 3 cells fill in on consecutive rows? Thanks
See if you can adapt the technique used here:
Lookup with multiple instances of the lookup value
If not, then you'll have to post a SMALL sample file so we can see what you're trying to do.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Share the formula you are using. Better if you upload a sample workbook with enough sample data in it with the formula applied and show the desired output.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Here is the formula I'm using. My problem is the blank lines that result if the statement is false. I am telling it to return "" if the statement is false but that results in a blank line. I want the data that is true to enter on consecutive lines not leave any blank lines. Can you help?
=IF('Cash & Bank'!F3>0,'Cash & Bank'!A3,"")
Try this...Originally Posted by Terisammis
Cash & Bank
On Sheet2...
Sheet2
This array formula** entered in A2:
=IFERROR(INDEX('Cash & Bank'!A:A,SMALL(IF('Cash & Bank'!F$3:F$15>0,ROW('Cash & Bank'!F$3:F$15)),ROWS(A$2:A2))),"")
** 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.
Try this Array Formula. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. normally when you write a formula in a cell, you confirm it by pressing Enter key, but in case of an array formula, you will need to confirm it by holding the Ctrl+Shift keys together and then pressing Enter key. Or if you paste an array formula in a cell, press F2 (function key) and hold down the Ctrl+Shift together and then press Enter. When an array formula is entered correctly, you will notice in the formula bar that the formula gets surrounded by the Curly Brackets that tells you that you have confirmed the array formula correctly.)
and then dopy down until you get blank cells.
=IFERROR(INDEX('Cash & Bank'!$A$3:$A$100,SMALL(IF('Cash & Bank'!$F$3:$F$100>0,ROW('Cash & Bank'!$F$3:$F$100)-ROW('Cash & Bank'!$F$3)+1),ROWS(A$1:A1))),"")
Is this what you are trying to achieve?
It worked!!! Thanks so much. Now I have that part under control my next hurdle is a couple cells away - I am using the same formula but am trying to add a second criteria so I tucked in an AND to have it look at a second cell as well. Unfortunately it is not working so I'm stuck again. Cell K3 is the first cell where I'm using this formula, I tried changing the last statement ROWS(K$1:K1))),"") to say ROWS(K$3:K3))),"") but that didn't seem to help. Any suggestions?
=IFERROR(INDEX('Cash & Bank'!$G$3:$G$40,SMALL(IF(AND('Cash & Bank'!$G$3:$G$40>0,'Cash & Bank'!$B$3:$B$40="ge"),ROW('Cash & Bank'!$G$3:$G$40)-ROW('Cash & Bank'!$G$3)+1),ROWS(K$1:K1))),"")
Try it like this...
=IFERROR(INDEX('Cash & Bank'!G:G,SMALL(IF('Cash & Bank'!G$3:G$40>0,IF('Cash & Bank'!B$3:B$40="ge",ROW('Cash & Bank'!G$3:G$40))),ROWS(K$3:K3))),"")
Still array entered.
Thanks so much, I am good to go now!!!
You're welcome. 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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks