Hi friends,
I have below excel file, in this file I have USD statement of account table. But cumming all details. Please check and advice me formula changes.
Please check Row 24, 25. I want 24 only, I mention it in formula, but 25th row comming, AND Row 7 not coming in Statement of account table(USD entry).
Thank you friends.
Last edited by zbor; 01-09-2017 at 06:08 AM.
If existing formula is wrong, then please provide me new formula for Statement of Account table
I have no idea what you are trying to do here
I get this part...
SMALL(IF(($I$5:$I$7<>0)*($B$5:$B$7>=$G$15)*($B$5:$B$7<=$G$16)*($C$5:$C$7=$A$15), ROW($A$5:$A$7)-ROW($A$5)+1, (IF(($H$5:$H$7<>0)*($B$5:$B$7>=$G$15)*($B$5:$B$7<=$G$16)*($C$5:$C$7=$A$15), ROW($A$5:$A$7)-ROW($A$5)+1))), ROWS($A$23:$A23))
and I get this part...
SMALL(IF(($I$5:$I$7<>0)*($B$5:$B$7>=$G$15)*($B$5:$B$7<=$G$16)*($C$5:$C$7=$A$15), ROW($A$5:$A$7)-ROW($A$5)+1, (IF(($H$5:$H$7<>0)*($B$5:$B$7>=$G$15)*($B$5:$B$7<=$G$16)*($C$5:$C$7=$A$15), ROW($A$5:$A$7)-ROW($A$5)+1))), ROWS($A$23:$A23))
but what are you trying to do with the middle part?
SMALL(IF(($I$5:$I$7<>0)*($B$5:$B$7>=$G$15)*($B$5:$B$7<=$G$16)*($C$5:$C$7=$A$15), ROW($A$5:$A$7)-ROW($A$5)+1, (IF(($H$5:$H$7<>0)*($B$5:$B$7>=$G$15)*($B$5:$B$7<=$G$16)*($C$5:$C$7=$A$15), ROW($A$5:$A$7)-ROW($A$5)+1))), ROWS($A$23:$A23))
The general syntax for that method is...
index(range to return, small(if(range=criteria,row(range)),rows($A$1:A1)))
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
actually below formula i am using
Please Login or Register to view this content.
If H (OR) I columns have values then true other wise false.
Last edited by rajeshn_in; 01-09-2017 at 04:02 AM.
Main thing is H OR I columns have any values then cell value come other wise no need. Have some date (B) and company name(C) conditions.
IFERROR(INDEX($A$5:$N$7, SMALL(IF(AND(OR(($I$5:$I$7)<>0, ($H$5:$H$7)<>0), ($B$5:$B$7>=$G$15), ($B$5:$B$7<=$G$16), ($C$5:$C$7=$A$15)), ROW($A$5:$A$7)-ROW($A$5)+1, ROW($A$5:$A$7)-ROW($A$5)+1), ROWS($A$23:$A24)), MATCH(D$19, $A$2:$N$2, 0)), "")
Last edited by rajeshn_in; 01-09-2017 at 04:02 AM.
Try
=IFERROR(INDEX($A$5:$N$7, SMALL(IF(((I$5:$I$7<>0)+( $H$5:$H$7<>0))* ($B$5:$B$7>=$G$15)* ($B$5:$B$7<=$G$16)* ($C$5:$C$7=$A$15), ROW($A$5:$A$7)-ROW($A$5)+1, ""), ROWS($A$5:$A5)), MATCH(D$19, $A$2:$N$2, 0)+2), "")
Enter with Ctrl+Shift+Enter
"+" are OR conditions
"*" are AND Conditions
Last edited by JohnTopley; 01-09-2017 at 06:06 AM.
Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.
Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
Hi Zbor,
Please allow my post for further replies. I have changed Heading.
Hi John Topley,
Same result coming...... No Change.
I think the test is wrong but not sure what you want.
See attached
In A24
=IFERROR(INDEX($B$5:$B$7, SMALL(IF((($I$5:$I$7<>0)+($H$5:$H$7<>0))*($B$5:$B$7>=$G$15)* ($B$5:$B$7<=$G$16)* ($C$5:$C$7=$A$15), ROW($A$5:$A$7)-ROW($A$5)+1,""),ROWS($A$5:A5))), "")
Enter with Ctrl+Shift+Enter
Highlighted range changed for other data
Yes, this is what I want. Thank you very much John Topley, Thank you very much
Hi John,
I applied as your below formula, but values are not coming what we mentioned in formula.....
=IFERROR(INDEX($B$5:$B$7, SMALL(IF((($I$5:$I$7<>0)+($H$5:$H$7<>0))*($B$5:$B$7>=$G$15)* ($B$5:$B$7<=$G$16)* ($C$5:$C$7=$A$15), ROW($A$5:$A$7)-ROW($A$5)+1,""),ROWS($A$5:A5))), "")
Please see attachment , Statement of account - USD table row 20 no need, because its SGD entry,
and same Statement of account - SGD table row 19 no need, because its USD entry
please see and advice me ......
Hi friends,
Can any one please check this issue........
a19try this and copy across and made necessary changes as per your needPlease Login or Register to view this content.
please see attached file
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Thank you for your replay nflsales.
7 Row not coming in statement of account table, plz check attachment
sorry did not cross check $ sing was missed and rows function missed
see the attached file
Hi Friends,
Any suggestions.
attach file not working?Originally Posted by rajeshn_in
Hi Friends,
Any suggestions.
Actually some bank entries don't have A column. So, we can't use Column A in formula. Please see attachment for better understanding.
Thank you.
in that case which columns to look
H, I for USD,
N, O for SGD
H, I for USD,
N, O for SGD
Two bank entries updated in above file.
Thank you...
For SGD
in I19Enter with Ctrl+Shift+EnterPlease Login or Register to view this content.
Copy across and change range as required
You should have been able to work this out for yourself
Use previous formula given for USD
see the attached file little bit modified formula
Thank you friend, thank you very much.
Thank you John, thank you very much.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks