Excel Classes

[iowagirl]

Does anyone know of a good excel class? I know the basics and a few thing beyond that but I need to learn to write code and macros for it. I use excel daily in my job and the formulas need to change in my spreadsheets and I have no idea how to change them. I also need to create more spreadsheets to do other things that I am taking on. I'm really trying to advance in my job so this is important.

I'm looking for something that is free and that I can work on in my spare time.

[Cwadda]

Youtube might have a fair bit of information.
Possibly your local library?
The best-rated Excel books on Amazon?

Hope this helps!

[katsiki]

I saved this from another site but have not tried them yet.  Hope it helps you!

Excel 2016 Foundation
Use Code EX2016Foundation

Excel 2016 Intermediate
Use Code EX2016Intermediate

Excel 2016 Advanced
Use Code EX2016Advanced
 
Udemy.com

[Lookilu]

Take a look at http://gcflearnfree.org/

Self-paced and free!

[BigBigote]

Goodwill Job Connection offers free Microsoft Office courses and they provide a certificate of completion at the end. Only limitation may be that I believe they hold them during the work week usually, so this may conflict with your work schedule.

[syednaeemul]

The best place to learn Excel, including the basics of macros, is chandoo.org. The author's a Microsoft MVP. Once you've gone through his content, your next levels are the MS Office MSDN library (https://msdn.microsoft.com/en-us/library/office/ee861528.aspx) and the MrExcel forums.

[iowagirl]

Thanks I will check them all out. Some of them I recognize as I did a search. Some that I looked at really only showed you could do that but never taught you how. I figured someone on here would have some knowledge or experience with one.

The company said it would pay for this 2 day crash course on excel. For the money it didn't look like it was going teach more than the basics and look you can do this so it looked more like a waste of my time than anything. I'm a hands on learning so I need to see it and do it for it to stick.

[galliver]

Have you done any/much programming in other languages? If you have, my favorite way to learn how to write macros for any program is to use the macro recording feature (for example, to do the thing I want in one file, manually) and then open that up in a text editor and edit (for example, so it can do that thing across different files), like "oh, I don't want to just pick file ABCD.xlsx here, I want to actually pick a file, how do I do that?" I Google "excel macro pick file" and look for likely candidates. Over time you pick it up.... This might be entirely unhelpful.

[iowagirl]

No I haven't done any programming. I can follow some of the html and understand what its doing but have never tried to actually write anything.

[MDM]

...I need to learn to write code and macros for it.
...the formulas need to change in my spreadsheets and I have no idea how to change them.

The second line does not necessarily require the first line.

It might, and macros and Visual Basic can be very useful, but you can also do much with "normal" Excel functions such as VLOOKUP, filtering, etc.

Is something like http://www.excel-easy.com/introduction/formulas-functions.html "too easy" or "about what you need now"?

[iowagirl]

...I need to learn to write code and macros for it.
...the formulas need to change in my spreadsheets and I have no idea how to change them.

The second line does not necessarily require the first line.

It might, and macros and Visual Basic can be very useful, but you can also do much with "normal" Excel functions such as VLOOKUP, filtering, etc.

Is something like http://www.excel-easy.com/introduction/formulas-functions.html "too easy" or "about what you need now"?

To easy. Although I've never used countif because I've never had the need too. I know there are several things that are pretty simple that I have never needed. I will post what I'm using when I have access to the spreadsheet again. It has vlookup which my boss wrote, he did most of the code. I had someone on fivrr start it but it got even more complicated and again needs changed. My boss is hard to pin down with time most of the time and even worse now because of people retiring and moving to new positions. He took on a lot of extra work for until that all gets resolved.

I also need to figure out a comparison/match spreadsheet. I would have to copy and paste in all of the numbers as each spreadsheet will never be in the same format due to converting it to excel. Its a bank rec vs bank statement vs gl vs other spreadsheet. Would make my life a lot easier.

[iowagirl]

This is what I'm currently working with. It needs changed again and for some reason continues to fail on things. Some months it works fine and then something changes even though nothing changes. The changes the company is making is requiring this to either change consistently over the next 2 years or I have to manually code over 2000 lines of stuff monthly which takes about 4 days. So if I learn it I can keep this working and make other parts of my job easier. This will also open up other opportunities for me in the future which will help me get out of debt faster and meet my goals faster.

=IF(D452=20000,"51-20-5200",IF(D452=30000,"10-30-5300",IF(D452=10032,"10-10-4125",IF(D452=10031,"10-10-4105",IF(A452="","",IF(INDEX(settings!L:L,MATCH(D452,settings!I:I,0),1)="",INDEX(settings!B:B,MATCH(A452,settings!A:A,0),1),"31")&"-"&INDEX(settings!F:F,MATCH(B452,settings!E:E,0),1)&"-"&INDEX(settings!J:J,MATCH(D452,settings!I:I,0),1))))))

So education seems the way to go but I do not have the time or resources to take official classes nor does that piece of paper saying I did it will make any difference.

[erp]

+1 for the Chandoo.org recommendation. I still end up picking up useful tricks from there and have seen several co-workers develop into extremely proficient Excel users based pretty much exclusively on tutorials from his website.

[ShoulderThingThatGoesUp]

To understand a complex formula, split its parts into different cells and use the Formulas->Evaluate Formula tool to follow along.

[galliver]

This is what I'm currently working with. It needs changed again and for some reason continues to fail on things. Some months it works fine and then something changes even though nothing changes. The changes the company is making is requiring this to either change consistently over the next 2 years or I have to manually code over 2000 lines of stuff monthly which takes about 4 days. So if I learn it I can keep this working and make other parts of my job easier. This will also open up other opportunities for me in the future which will help me get out of debt faster and meet my goals faster.

=IF(D452=20000,"51-20-5200",IF(D452=30000,"10-30-5300",IF(D452=10032,"10-10-4125",IF(D452=10031,"10-10-4105",IF(A452="","",IF(INDEX(settings!L:L,MATCH(D452,settings!I:I,0),1)="",INDEX(settings!B:B,MATCH(A452,settings!A:A,0),1),"31")&"-"&INDEX(settings!F:F,MATCH(B452,settings!E:E,0),1)&"-"&INDEX(settings!J:J,MATCH(D452,settings!I:I,0),1))))))

So education seems the way to go but I do not have the time or resources to take official classes nor does that piece of paper saying I did it will make any difference.

Ahhhh! You DO need help!

From a quick search, this looks promising? Older version so you might have to hunt or Google to find commands they mention...

http://excelvbatutor.com/vba_tutorial.html

Another thought is that maybe Excel   isn't the right tool to use here, but I also know things like that can be nearly impossible to change for the person actually working on it...

Sent from my SM-G900V using Tapatalk

[iowagirl]

Ahhhh! You DO need help!

From a quick search, this looks promising? Older version so you might have to hunt or Google to find commands they mention...

http://excelvbatutor.com/vba_tutorial.html

Another thought is that maybe Excel   isn't the right tool to use here, but I also know things like that can be nearly impossible to change for the person actually working on it...

Sent from my SM-G900V using Tapatalk

Thanks I will take a look at that. The information will only download out of our system to excel. If I can easily copy and paste it into something better I'm willing to take a look as I'm the main user. As long as I can have something similar to the pivot table that spits out the final results for me to enter into each account and it doesn't cost the company extra money my boss would be able to adapt as he uses it for some of the information I spit out of there at the end of the month.

I bookmarked that link so I can take a look at it after today's deposits are done.

[hope2retire]

what the formula does is either directly get the (say) account numbers based on If Else condition or build it from scratch based on pattern matching.
for example if it is 20000 then 51-20-5200, same for 30000 -> 10-30-5300, 10032 -> 10-10-4125 and 10031 -> 10-10-4105. if all this does not work. then it goes and checks if A452 is empty then -> empty. If all this does not work, then it goes and does pattern matching based on certain conditions using INDEX() and MATCH() functions on "settings tab" coupled with "&" to concatenate "-" and construct the account numbers from scratch. Hope this helps.


=IF(D452=20000,"51-20-5200",
 IF(D452=30000,"10-30-5300",
 IF(D452=10032,"10-10-4125",
 IF(D452=10031,"10-10-4105",
 IF(A452="","",
 IF(INDEX(settings!L:L,MATCH(D452,settings!I:I,0),1)="",INDEX(settings!B:B,MATCH(A452,settings!A:A,0),1),"31")
 &"-"
 &INDEX(settings!F:F,MATCH(B452,settings!E:E,0),1)
 &"-"
 &INDEX(settings!J:J,MATCH(D452,settings!I:I,0),1))))))





H2R

[iowagirl]

what the formula does is either directly get the (say) account numbers based on If Else condition or build it from scratch based on pattern matching.
for example if it is 20000 then 51-20-5200, same for 30000 -> 10-30-5300, 10032 -> 10-10-4125 and 10031 -> 10-10-4105. if all this does not work. then it goes and checks if A452 is empty then -> empty. If all this does not work, then it goes and does pattern matching based on certain conditions using INDEX() and MATCH() functions on "settings tab" coupled with "&" to concatenate "-" and construct the account numbers from scratch. Hope this helps.


=IF(D452=20000,"51-20-5200",
 IF(D452=30000,"10-30-5300",
 IF(D452=10032,"10-10-4125",
 IF(D452=10031,"10-10-4105",
 IF(A452="","",
 IF(INDEX(settings!L:L,MATCH(D452,settings!I:I,0),1)="",INDEX(settings!B:B,MATCH(A452,settings!A:A,0),1),"31")
 &"-"
 &INDEX(settings!F:F,MATCH(B452,settings!E:E,0),1)
 &"-"
 &INDEX(settings!J:J,MATCH(D452,settings!I:I,0),1))))))





H2R

That actually makes sense and what you just told me (event though you didn't know it) its not that formula that is giving me the grief its actually column P or S which you don't have. I'm sure the other 1 is correct so I need to look closer at that one. If I look back to the beginning of last year before one other guy got his hands on it for a couple of month I would bet I will see the mistake (I hope).

Thank you. So I still need to learn this stuff but that gets me going fixing this one.

[MDM]

...for some reason continues to fail on things. Some months it works fine and then something changes even though nothing changes.

what the formula does is...

H2R gave a good summary of what the formula does.

In general Excel (or any other program) will do exactly what you tell it to do.  But you have to tell it in language that it understands, and sometimes things get "lost in translation."

Your first task is to change "for some reason continues to fail..." to "it fails because...".  After you know why it fails, then* you can work on fixing it.

*This approach does run the risk that your code will become a mishmash of ad hoc fixes.  In theory, designing the program from scratch could provide a more "elegant" solution.  No way to know this point which approach is best.  If it were me, I'd try the ad hoc fixes first....

ETA: ...and from your reply while I wrote this you may have already reached this conclusion.  Good luck!

[galliver]

Ahhhh! You DO need help!

From a quick search, this looks promising? Older version so you might have to hunt or Google to find commands they mention...

http://excelvbatutor.com/vba_tutorial.html

Another thought is that maybe Excel   isn't the right tool to use here, but I also know things like that can be nearly impossible to change for the person actually working on it...

Sent from my SM-G900V using Tapatalk

Thanks I will take a look at that. The information will only download out of our system to excel. If I can easily copy and paste it into something better I'm willing to take a look as I'm the main user. As long as I can have something similar to the pivot table that spits out the final results for me to enter into each account and it doesn't cost the company extra money my boss would be able to adapt as he uses it for some of the information I spit out of there at the end of the month.

I bookmarked that link so I can take a look at it after today's deposits are done.

So, my recommendation was based more on a personal rule of thumb that "Excel is not good for managing 7 layers of nested logic" rather than having a specific suggestion in mind... my bf works with a tool that forces him to generate similar expressions and we've had numerous conversations about how coding that functionality with actual code would be easier and more readable and more manageable than working within the limitations imposed by the tool...

At the moment, you should probably solve your problem in Excel (/VBA if necessary) since that's what you're familiar with...but if you have any free time, you could try poking around, for example, https://www.codecademy.com/learn/python It seems like a little coding background could open up some doors for you (and it's not fundamentally hard; like above posters mentioned, you're basically just giving the computer a list of instructions and telling it how to make decisions...it's kind of like a game!). And if you like it and want to try applying it to your work, there is apparently a Python package that will open/modify excel files (but learn the basics first!). :)

[iowagirl]

Definitely a better understanding of all of it would help a lot. I know just enough to be dangerous but not enough to do what I need to do. I see that Codecademy is up updating their site this summer. I may hold off on that one at least until I can get this one problem figured out. That may be a option for the other problem that needs solved. Now if anyone can tell me how to wake up tomorrow morning and just know all of this stuff. :) I mean you've all been great with helping me find solutions and getting me in the right direction I really do appreciate it.

[CptCool]

I highly recommend the books by John Walkenbach for VBA and for excel. They help give a clear understanding of what functions do and when to use them. It will likely be available at your local library - I'd make sure to get the same version as your company uses (e.g. get VBA for Excel 2013 if your company uses Excel 2013).

reddit.com/r/excel and /r/vba are great resources and can be good practice if you try to help others solve their problems as well.

If youtube/videos is your preferred learning route over books/text then the youtube channel ExcelVbaIsFun is great.

Most importantly, learn how to properly search for answers using google or any other search engine. I don't think I've ever had a completely unique situation that hasn't already been asked or answered on sites like mrexcel, stackexchange, reddit, etc. - the trick is searching for the right terms.


I think it's already been said, but try to stay away from VBA unless it is necessary. It is messy, very prone to errors, and nearly impossible to maintain, especially with turnover. I'd say the vast majority of spreadsheets benefit from VBA mostly for presentation, formatting, and data normalization only. Calculations are rarerly, if ever, done in VBA as it is very difficult to track & is much slower than typical excel functions


Edit: I forgot to add that you should try to avoid so many nested IF statements like that - it's very messy and hard to understand what is happening. There are lots of other methods to use instead of the nested ifs that are faster & easier to read. At the very least, I'd separate each if statement into it's own column as that would also help you to figure out what the megaformula is actually doing.

[mustachepungoeshere]

Great thread.

I should look into some of the tutorials mentioned here, instead of my current method of complaining to marty998 until he texts me instructions.

[robartsd]

=IF(D452=20000,"51-20-5200",
 IF(D452=30000,"10-30-5300",
 IF(D452=10032,"10-10-4125",
 IF(D452=10031,"10-10-4105",
 IF(A452="","",
 IF(INDEX(settings!L:L,MATCH(D452,settings!I:I,0),1)="",INDEX(settings!B:B,MATCH(A452,settings!A:A,0),1),"31")
 &"-"
 &INDEX(settings!F:F,MATCH(B452,settings!E:E,0),1)
 &"-"
 &INDEX(settings!J:J,MATCH(D452,settings!I:I,0),1))))))

The first several layers of this nested if could easily be replaced with VLOOKUP and a lookup table. I'd use the IFERROR function to bridge to building the string based on looking up values in the settings worksheet. This has the advantage that the table of hard coded values could be expanded without needing to edit the formula.

Code: [Select]

=IFERROR(VLOOKUP(D452,lookup table range,lookup table column number,FALSE),
         IF(ISBLANK(A452),"",
            IF(VLOOKUP(D452,settings!I:L,4,FALSE)="",
               VLOOKUP(A452,settings!A:B,2,FALSE),
               "31"
            )
            &"-"
            &VLOOKUP(B452,settings!E:F,2,FALSE)
            &"-"
            &VLOOKUP(D452,settings!I:J,2,FALSE)
          )
 )

[letired]

+1 on learning how to use google/search engine of choice to look up info on the problem you are having and how to solve it. Once you master that skill, you can apply it to a wide variety of technical problem-solving and everyone will think you are a wizard (unless you get into programming, in which case that's what everyone else is doing all day too).

And I'm sure you know this, but save off a backup copy of the file, experiment with getting Excel to do the thing you need to do in one file, and if it all goes to hell, you can just delete the file and go back to the backup copy.

[galliver]

And I'm sure you know this, but save off a backup copy of the file, experiment with getting Excel to do the thing you need to do in one file, and if it all goes to hell, you can just delete the file and go back to the backup copy.

Yesssss