LinkBack URL
About LinkBacks
i have two worksheets, on GL-MO-10 I add the data, and on worksheet SAO-MO-10 I show the data, but I only want to show it here uf it does have data from GL-MO-10 in it.
i have two worksheets, on GL-MO-10 I add the data, and on worksheet SAO-MO-10 I show the data, but I only want to show it here uf it does have data from GL-MO-10 in it.
If I understand you correctly in that you want to hide all the rows with a value of zero then this macro might help.
Right click on your tab>ViewCode and insert this code in the window that appears
Please Login or Register to view this content.
Are you still on version 2016? If you've upgraded to MS365 or V2021, it will probably make this a lot easier.
I am on 2021
Thank you, Nearly working, it works, but when I add a new amount in, it does not update it, even if I run the Macro, still not update.
Update.... Working now, thank you
Update 2. Does not update when you add new data in
Last edited by hendrikbez; 12-18-2022 at 12:27 AM.
Then please update your profile.
Try this in cell B11:
=IFERROR(LET(a,FILTER('GL-MO 10'!A9:B495,ISNUMBER(MATCH('GL-MO 10'!A9:A495,'Chart of Accounts'!A9:A62,0))),B,IF(a=0,"",a),CHOOSE({1,2,3},CHOOSECOLS(B,1),CHOOSECOLS(B,2),LET(a,FILTER('GL-MO 10'!E9:E495,'GL-MO 10'!B9:B495="Totaal"),IF(a=0,"",a)))),"")
In cell B73:
=IFERROR(LET(a,FILTER('GL-MO 10'!A498:B1000,ISNUMBER(MATCH('GL-MO 10'!A498:A1000,'Chart of Accounts'!A64:A116,0))),B,IF(a=0,"",a),CHOOSE({1,2,3},CHOOSECOLS(B,1),CHOOSECOLS(B,2),LET(a,FILTER('GL-MO 10'!D498:D1000,'GL-MO 10'!B498:B1000="Totaal"),IF(a=0,"",a)))),"")
In Cell B11, I have already getting info from a other page, (I see what you are doing here) but when I add your info in B11 it gives error " the first argument of LET must be a valid name"
Last edited by hendrikbez; 12-17-2022 at 11:45 PM.
Take a look at the attached. To me, it shows what your sample file shows, but uses the formulas I show above. Does it show the same for you?
I did look at the one you make,
1. When I open it, is still shows all info.
2. When I click or enter on the first one, it only show the amount
Screenshot_38.png
Screenshot_39.png
I may do somthing not correct here.
Ah, I think I see. It looks like you don't have the "CHOOSECOLS" formula available to you yet (it's preceded by "_xlfn"). It's being rolled out to all 365 users, so I would think you will have it soon, but until you get it, this won't work unfortunately. Sorry about that.
Thank you for your help and info, will wait for it.
Don't forget to please update your profile.
Thank you did change it to v121 on my profile
So all this making it a 'lot easier' didn't really work?
No, and it the code look promising, just do not owrk in v 2021.
Any other solution?
Well it worked for me and I thought you said (in post #5) "Update.... Working now, thank you"
If you want my help you need to provide more feedback than just "do not owrk in v 2021".
So the answer to your question in Post #16 is "No".
At firs it looks like it worked, but when i opend the file again and add new value, it dit not update the worksheet with the new info
SHOW us - provide a workbook where it is NOT working.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
One tiny tweak to Greg's formula... to remove CHOOSECOLS which you do not have. Is this version OK?
Formula:
Please Login or Register to view this content.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
I just noticed the OUTGOINGS as well... The same approach should work there, too:
=IFERROR(LET(a,FILTER('GL-MO 10'!A498:B1000,ISNUMBER(MATCH('GL-MO 10'!A498:A1000,'Chart of Accounts'!A64:A116,0))),B,IF(a=0,"",a),CHOOSE({1,2,3,4},INDEX(B,,1),INDEX(B,,2),"",LET(a,FILTER('GL-MO 10'!D498:D1000,'GL-MO 10'!B498:B1000="Totaal"),IF(a=0,"",a)))),"")
Here is the file.
on G m0 10 have added aount of E25, but it does not show ion SAO Mo 10 bwtween 11 and 14 and i did run macro again
Thank you, but it does not work on my file (attahced) t shows spill when I add it on more lines
Delete ALL the results that you have there currently. Add the formula.
The formula spills out and down automatically. You get the spill error if there are other values/formulae in the way. Delete them.
Ok, I have try it again, copy your code to B11 (is that the right place to add the code) first line and press enter, it gives error "The first argument of LET must be a valid name"
Last edited by hendrikbez; 12-18-2022 at 10:36 AM.
I cannot diagnose what I cannot see. Post the file.
Paste this:
=IFERROR(LET(a,FILTER('GL-MO 10'!A9:B495,ISNUMBER(MATCH('GL-MO 10'!A9:A495,'Chart of Accounts'!A9:A62,0))),B,IF(a=0,"",a),CHOOSE({1,2,3,4},INDEX(B,,1),INDEX(B,,2),"",LET(a,FILTER('GL-MO 10'!E9:E495,'GL-MO 10'!B9:B495="Totaal"),IF(a=0,"",a)))),"")
into the SINGLE yellow cell in this sheet.
I cannot safe the file to show you, but here is a screenshot
Looks like you are running a European locale, so you may need to change commas in the formula to semi-colons.
READ Post 27 and do exactly as I asked. In your last screenshot, the spill error is caused by the presence of expected results in the other cells.
Do say I must change all the , to ; in the formula, how do I see what type i am running
Was it working in the file that I posted at Post 21.
if so... are there commas or semicolons in the formula?
NO not yet. Answer my Qs at Post 32.Originally Posted by hendrikbez
This is how it look when I copy it to B11
=IFERROR(LET(a,FILTER('GL-MO 10'!A9:B495,ISNUMBER(MATCH('GL-MO 10'!A9:A495,'Chart of Accounts'!A9:A62,0))),B,IF(a=0,"",a),CHOOSE({1,2,3,4},INDEX(B,,1),INDEX(B,,2),"",LET(a,FILTER('GL-MO 10'!E9:E495,'GL-MO 10'!B9:B495="Totaal"),IF(a=0,"",a)))),"")
STOP !!
This is getting massively frustrating. I do not know WHO you are replying to or which POST you are replying to.
Go back to Post 32. Answer the questions asked.
I am sorry that you are getting massively frustating, But how I understand post 32, (you ask if there are commas or semicolons in the formula?) I see only Commas in it.
Fine. I understand that. I now know that your Excel uses commas as separators.
However, you have STILL NOT answered the other question. DID IT WORK in the file that I posted at Post 32. Open it. Enable editing. Is it working???
Edit: the file referred to in Post 32. The file is actually at Post 21.
Last edited by Glenn Kennedy; 12-18-2022 at 11:14 AM.
I have used the file you gave me, after enable it change all to ;
=IFERROR(LET(a;FILTER('GL-MO 10'!A498:B1000;ISNUMBER(MATCH('GL-MO 10'!A498:A1000;'Chart of Accounts'!A64:A116;0)));B;IF(a=0;"";a);CHOOSE({1\2\3\4};INDEX(B;;1);INDEX(B;;2);"";LET(a;FILTER('GL-MO 10'!D498:D1000;'GL-MO 10'!B498:B1000="Totaal");IF(a=0;"";a))));"")
So... IS IT WORKING??? Does it give you results or an error???
YES or NO?
NO it does not work, it shows the code in all the cells, but not working
Post a screenshot.
Here is the screenshot
So... how is that NOT working? What DO you expect to see?
What I need it to Show,is only the ones that have a amount in them, so if column/row E11:E64 (this is for income, and it can get more rows in future, and colum/row E73:124 (exspence and row can get more)
so it must not show here if the amount is 0,
Only must show all info of rows that have an amount in it.
Right. Now we know:
1. You use ; not ,
2. That it IS working (the formula delivers words and numbers, not error messages). There is no fundamental Excel version incompatability now.
3. That is is not doing EXACTLY what you want.
4. Back in 2 minutes.
OK. To use a Northern Irish expression, are we "suckin' diesel" with this version??
(approximate translation... "really getting going"...)
Yes, thank you, it is working, I do not know why it use thast one as I am in South Africa
Whewre do you see the code it onlky show me =LET(
Phew!! We got there.
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution. Especially GREG, who did all the hard work setting it up in the first place. I just did the "easy" bit... making it compatible with Excel 2021!!
Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
I did add Reputation a few mintes ago, will also do it for Greg
Just one thing, I cannot see the code thast you have used, as I need to use it on my main file.
OK. That's EASY (by comparison). I have removed the line breaks in the formula, so you can see more of it. To see it all...
Position the cursor at the base of the formula bar (inside the red shape in the pic below) and move it up& down untiul you see the double-headed arrow. Left click (hold) & drag down.
Or... (how many years have I been using Excel and I never noticed this...) click on the arrow here... to expand/contract
Thank you once again, I will now just figure out, when I copy the formula to my file, it does not ask to activate the code, it only do #SPILL.
You need to be 110% sure that there is nothing AT ALL in any of the cells where the results are expected to go.
Click on the cell with the spill error. It will show the outline of a rectangle where it wants to spill the results. (blue dash border). ALL those cells MUST be empty.
Like here. The junk at the bottom right prevents the formula from spilling.
I got it, I delete all info and copy the code, now it is working, Thank Youi
Last edited by hendrikbez; 12-18-2022 at 01:28 PM.
1. What, exactly, is in the cell IMMEDIATELY to the right of the #SPILL?
2. What, exactly, is in the cell IMMEDIATELY below the #SPILL?
3. Create an EMPTY sheet. Paste the formula into A1 in that sheet.
4. Highlight - yellow - the cell where you are attempting to paste the formula, tell me what sheet it is in.
5. Save & post the file.
I'm only here for about 30 mins or so... so be quick!
Thank you, I got it to work, you help me a lot.
OK. All the best!!
Sorry to bother you, I have see that the second part is giving the same amount as the first part, I have changed it to
,Please Login or Register to view this content.
but now it only shows @CALC! and not the correct data
See SAO-MO 10 row B73
Last edited by hendrikbez; 12-19-2022 at 02:32 AM.
Provide a sample workbook.
Done see post #64
Last edited by hendrikbez; 12-19-2022 at 02:36 AM.
in several of the ranges, they were non-identical. They HAVE to be the same.
Also, the outgoings are in column D not column E.
=LET(
A,FILTER('GL-MO 10'!A498:B976,ISNUMBER(MATCH('GL-MO 10'!A498:A976,'Chart of Accounts'!A64:A116,0))),
B,IF(A=0,"",A),
C,CHOOSE({1,2,3,4},INDEX(B,,1),INDEX(B,,2),"",LET(a,FILTER('GL-MO 10'!D498:D976,'GL-MO 10'!B498:B976="Totaal"),IF(a=0,"",a))),
FILTER(C,INDEX(C,,4)<>""))
I tidied this up a bit.
Incoming:
=LET(A,'GL-MO 10'!A9:E494,B,'Chart of Accounts'!A9:A62,C,SEQUENCE(ROWS(A)),D,INDEX(A,C,{1,2}),E,FILTER(D,ISNUMBER(MATCH(INDEX(D,,1),B,0))),F,FILTER(INDEX(A,,5),INDEX(A,,2)="Totaal"),G,CHOOSE({1,2,3,4},E,E,"",F),FILTER(G,INDEX(G,,4)<>0))
Outgoing:
=LET(A,'GL-MO 10'!A498:E976,B,'Chart of Accounts'!A64:A116,C,SEQUENCE(ROWS(A)),D,INDEX(A,C,{1,2}),E,FILTER(D,ISNUMBER(MATCH(INDEX(D,,1),B,0))),F,FILTER(INDEX(A,,4),INDEX(A,,2)="Totaal"),G,CHOOSE({1,2,3,4},E,E,"",F),FILTER(G,INDEX(G,,4)<>0))
You need to change ONLY the red bit on other sheets.
and set the blue bit to 5 for incoming & 4 for outgoings.
Sorry, I take back my post. It was not entirely correct since things have changed since when I first looked at it. I believe the formula can be written so you don't have to worry about the first range, but I haven't done that. And don't know if it's needed.
Last edited by Gregb11; 12-19-2022 at 07:33 AM.
Thank you, it is working, I am not good in coding, if posible, can you explai this code to a noob like me.
I only got to the code now, as our power was off for 4Hours (loadshedding)
=LET(
A,'GL-MO 10'!A9:E494,
B,'Chart of Accounts'!A9:A62,
C,SEQUENCE(ROWS(A)),
D,INDEX(A,C,{1,2}),
E,FILTER(D,ISNUMBER(MATCH(INDEX(D,,1),B,0))),
F,FILTER(INDEX(A,,5),INDEX(A,,2)="Totaal"),
G,CHOOSE({1,2,3,4},E,E,"",F),
FILTER(G,INDEX(G,,4)<>0))
A - selects the relevant data block
B - selects the relevant chart of accounts
C - counts the rows in A and returns them in a sequence: 1,2,3,4....
D - returns ALL the rows in A, columns 1 & 2
E - filters D, returning those rows where there is a match in the FIRST column with the CoA defined in B
F - filters the 5th column of A, where the 2nd column of A = Totaal
G - returns 4 columns the first two being the two-column array of E, the 3rd a blank column (I have NO IDEA why you wanted that...) and the 4th the one-column array returned at F
The last line is the calculation that supplies the result.
It returns only those rows in G, where the 4th column is non-zero.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks