I am trying to convert a google sheet to normal excel sheet, i did get it to convert, but I have a problem with one of the formulas
in google sheet i have in inligting sheet the following formula in C4
but in excel it does not show a formula in it, and I need help with this please.
Please Login or Register to view this content.
this is my google sheet
https://docs.google.com/spreadsheets...it?usp=sharing
and my excel sheet
Last edited by hendrikbez; 02-13-2024 at 03:43 AM. Reason: Working now
Please explain what the formula is meant to do in WORDS. It will need some adjustment. What result should it return (value)?
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.
Hope I can explain it to you, so you understand me.
Convert from Google Sheet to Excel 2021
This is my info on main page "Inligting"
B1= Uitgawes (Heading)
E1= Inkomste (Heading)
B2= My dropdown list (No range), added text only Jan, Feb, Mar, Apr, Mei, Jun, Jul, Aug, Sep, Okt, Nov, Des
B3= Text (Totaal)
C3= Sum of all amounts below starting at C5
E3= Text (Totaal)
F3= Sum of all amounts below starting at F5
C4= =map(B4:B, lambda(topic, let( sum, sumif(indirect(B2 & "!F:F"), topic, indirect(B2 & "!D:D")), if(or(isblank(topic), sum = 0), iferror(ø), sum) )))
F4= =map(E4:E, lambda(topic, let( sum, sumif(indirect(B2 & "!N:N"), topic, indirect(B2 & "!K:K")), if(or(isblank(topic), sum = 0), iferror(ø), sum))))
B5= first entry name
E5= first entry name
So the formula in C4 is first looking at My dropdown list in B2, if I choose Feb, it wil get the info from sheet Feb.
When I then add an item like Uiteet, in Feb, then it will add all the uiteet total in and show it in "Inligting" Uiteet with the full amount (of all uiteet together)
Same with F4 in Inligting.
Klink vir my jy eet uit veels to veel LOL
It looks like a SUMIFS() should do what you want.
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
Was dit maar waar om so baie uit te ett, net 'n toests om die formulla te laat werk.
Did try this, but not workingand gets error Getting thisPlease Login or Register to view this content.
There is aproblem with this formula
Not tryig to type a formula
When the first character is an equal ("=") or minus ("-") sign, Excel thinks its a formula
you type = 1+1, cell shows: 2
To get around this, type an apostrophe (') first
*you type =1+1, cell shows =1+1
I am not that good in Excel
A few errors that I see...
...ISBLANK(B4:B)...
excel range refs need a col and a row ref - you are missing the 2nd row ref
B4:B where?
INDIRECT(B2 & "!F:F")....
Not sure what that is supposed to be doing, unless B2 contains a sheet name?
The way I sometimes construct an INDIRECT formula is to make a dummy formula just like the INDIRECT would do, but without the INDIRECT.
So if B2 contains Sheet1 shere SHEET1 is an actual existing sheet you want to reference, then...
=SUM(sheet1!R:R)
so the INDIRECT would be...
=SUM(INDIRECT(B1&"!R:R"))
where B1 contains the text Sheet1
It's the missing quotes that are causing the issue, I think. The INDIRECT should need to be this:
=SUM(INDIRECT("'"&B1&&"'!R:R"))
Thank you, I did try something else and it is working now.
Did add this in B5, and drag it down
Please Login or Register to view this content.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Happy to help and thanks for the feedback
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks