Forums

Any spreadsheet experts?

• ScottHope 10,624 Posts

  Fri, Apr 11 2014 8:14 AM

  Thank you DK, CJ.

  Some more handy tips about tables that I didn't point out in the video are...

  1.) Click anywhere in the table ① to bring up the design tab on the ribbon ②

  2.) In the table style options if you tick the total row box ③, it will add a totals row to the bottom of the table ④

  3.) If you then click in the cell at the bottom of each column, a drop down arrow will appear ⑤, giving you various options for calculating the data in that column, you can also customise the cell by inserting any formula you wish.

  4.) There is a downside to this addition to the table and that is if you want to add new data to the table you will need to extend it manually by using the drag handle at the bottom right corner ⑥ or by selecting the last cell (bottom right corner but not in the totals row) and hitting the tab key, whereas before you could just add your new data to the bottom of the table and it would incorporate it automatically.

  

• DaddysKat 3,554 Posts

  Fri, Apr 11 2014 8:55 AM

  I'm not very well versed in entering formulas ... I prefer writing macros.  Pretty much everything you can do in Excel can be automated in a macro and I find debugging a macro easier than trying to debug a complex formula.

  I really like your solution to this problem, Scott ... definitely a lot more knowledgeable with spreadsheets.  Thanks for the insight.  Maybe someday I'll actually mess around with the spreadsheet functionality instead of writing macros.

• ScottHope 10,624 Posts

  Fri, Apr 11 2014 9:10 AM

  Thank you very much Kat. I admire what you do with VB, your applications, the ones I've seen on here anyway, look very professional and a lot neater than a spreadsheet, but that's because they're designed for a specific purpose I guess, unlike a spreadsheet.

  I did download Microsofts VB 2010 a while ago to have a look at it and ran through the tutorials, but to be honest its way over my head so I have a lot of respect and admiration for what you can do.

• DaddysKat 3,554 Posts

  Fri, Apr 11 2014 10:17 AM

  Visual Studio is actually pretty easy, once you get the hang of object-oriented programming.  The hardest part of writing code is to try to anticipate what the end-user is going to do.  I'm finding with the macro-driven spreadsheets and programs I've written is that I'm not very good at anticipating that.  There's always a few that will find something I didn't think of.  

  I think the hardest part of programming is relaying the "how-to-use" to everyone.  I'm not one for reading documentation until I find myself unable to figure out what the designer had in mind.  I'm pretty sure most are like me.  Writing up the "how-to" is what I hate the most ... followed closely by that pesky bug that seems to never want to go away.

  Anyone got any virtual Raid?  I have a few that are driving me absolutely crazy right now!

• ScottHope 10,624 Posts

  Tue, Apr 29 2014 2:37 PM

  @oilyrag, I'm pretty sure that you're quite happy with your spreadsheet and the way it works and you don't want other people messing with it, but it seems a shame not to make excel work harder for you when it's such a capable program. Anyway, I've had a play with the first worksheet in your workbook and you're welcome to have a look at it then dump it in your recycle bin if you want.  : )   DOWNLOAD or VIEW

• DaddysKat 3,554 Posts

  Tue, Apr 29 2014 7:39 PM

  ScottHope:

  Anyway, I've had a play with the first worksheet in your workbook and you're welcome to have a look at it then dump it in your recycle bin if you want.  : )   DOWNLOAD or VIEW

  Scott ... how do you do the links to download or view a file?  This sure could be handy with the CC reports I'm currently e-mailing.

  And ... for all you do for the community ... thanks!

  Kat ♥

• ScottHope 10,624 Posts

  Wed, Apr 30 2014 4:01 AM

  Hi Kat,  and thank you.

  You use Google Drive don't you?  Isn't it just a question of selecting the file you want to share, and copy the share link and paste it HERE, or wherever.

  As for creating a download link, well, with OneDrive which I use I had to Google how to do that because I couldn't find a download link anywhere, and the trick involves modifying the URL!  That sucks Microsoft!

  I guess you might have to do the same thing to your Google Drive links, I'm trying to find out how but it's proving a little tricky, but you may be more successful than me.

  Scott.

• DonCaron 5,502 Posts

  Wed, Apr 30 2014 5:10 AM

  I copy the URL and paste it in my post for stat sheets for the Club. Since last month I got a screenshot editor and convert it to Jpg for our facebook page.  On a spreadsheat you have to change your permissions though to allow others to view it

• oilyrag 875 Posts

  Wed, Apr 30 2014 5:26 AM

  ScottHope:

  it was basic, as i only use it for a few minutes, once every other month, so didn't bother trying to jazz it up, but i'll have a look :)

  something i do use more is here http://solentgolfsociety.co.uk/resultssheet.html

  a complete society scoring and handicap system in excel, always looking for ways to improve it.

   

• ScottHope 10,624 Posts

  Wed, Apr 30 2014 6:33 AM

  oilyrag:

  something i do use more is here http://solentgolfsociety.co.uk/resultssheet.html

  That's far too complicated for my little brain, haha. Nicely put together though, but I wouldn't even attempt to understand or modify it.