Jump to content


Start a free course now!

Most Liked Content


#25 Do you have similar courses for Word, PowerPoint and other applications?

Posted by TheSmartMethod on 02 September 2013 - 03:27 PM

The only Smart Method courses that have been published as books/videos so far are:

 

• Learn Access 2003 VBA With The Smart Method ISBN-13: 978-0955459900

• Learn Excel 2007 Essential Skills with the Smart Method ISBN-13: 978-0955459924

• Learn Excel 2007 Essential Skills with the Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2007 Expert Skills with The Smart MethodISBN-13: 978-0955459931

• Learn Excel 2007 Expert Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2010 Essential Skills with The Smart Method ISBN-13: 978-0955459979

• Learn Excel 2010 Essential Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2010 Expert Skills with The Smart Method ISBN-13: 978-0-9554599-8-6

• Learn Excel 2010 Expert Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2013 Essential Skills with The Smart Method ISBN:  978-1-909253-06-3

• Learn Excel 2013 Essential Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2013 Expert Skills with The Smart Method ISBN:  978-1-909253-07-0

• Learn Excel 2016 Essential Skills with The Smart Method ISBN: 978-1-909253-08-7

• Learn Excel 2016 Expert Skills with The Smart Method ISBN: 978-1-909253-09-4

Learn Excel 2016 for Mac Essential Skills with The Smart Method ISBN: 978-1-909253-11-7

Learn Excel 2016 for Mac Expert Skills with The Smart Method ISBN: 978-1-909253-12-4

Excel Construction Kit #1: Calendar and Year Planner ISBN: 978-1-909253-10-0

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Essential Skills with The Smart Method ISBN-13: 978-0955459962

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Essential Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Expert Skills with The Smart Method ISBN-13:978-0-9554599-9-3

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Expert Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

• Learn ASP.NET 4.5, C# and Visual Studio 2012 Essential Skills with The Smart Method ISBN-13: 978-1-909253-04-9

• Learn ASP.NET 4.5, C# and Visual Studio 2010 Essential Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

• Learn ASP.NET 4.5, C# and Visual Studio 2012 Expert Skills with The Smart Method ISBN-13: 978-1-909253-05-6

• Learn ASP.NET 4.5, C# and Visual Studio 2010 Essential Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

 

We're currently working on an (almost completed) VBA book  The VBA book will teach the Visual Basic for Applications (VBA) programming language which is generic to Office 2013 and not specific to Excel or any other application. 

 

Eventually Mike Smart will also write up his Outlook, Access, Word, Project, Power Point… and other courses as books and videos but it will take many years to complete them all. The Smart Method are currently exploring ways of increasing productivity by employing proof-readers,  testers and video editors to free more of Mike's time for course design and recording.  




#26 Do you have an Excel VBA (Visual Basic for Applications) course available?

Posted by TheSmartMethod on 02 September 2013 - 03:31 PM

VBA is not an Excel skill but a programming language that can be used (amongst other things) to automate Excel and other applications. The Smart Method have run VBA classroom courses (in the context of both the Access and Excel object models) for over ten years.

 

After many years of classroom courses for some of the world’s largest companies it has become apparent that very, very few business users actually need Visual Basic programming skills. In most business scenarios Excel is more than capable of providing every corporate need by correct use of its in-built functionality.

 

We've often been asked to provide a VBA classroom training course but then changed the course to Expert Skills on the day. This has happened because when delegates describe the business problems that they think VBA will help them solve, the solution is easy to implement by correctly understanding Excel’s advanced functionality.

 

If you have a specific business problem that you think that you need VBA to solve why not post it on our support forum.  One of our Excel Experts will, in almost all cases, be able to provide a solution that does not involve bespoke programming.



#927 Do you offer any training for Word

Posted by Prasanna on 17 April 2014 - 06:16 AM

Hi

Is there any training for Microsoft Word. This training is best in the world for Excel. If you have training for Microsoft Word such WordCentral.Com, i am very happy to get this one.

 

 

Regards

Prasnana




#1742 Preparing for the Microsoft MOS Expert Exam 77-427 and 77-428 using The Smart...

Posted by Mike on 04 July 2015 - 09:56 AM

Dear Ishaq

 

The new MOS exam format (for Office 2013) doesn't seek to test any individual Excel skills but, instead, poses a real-world business problem that the student must solve.  This is a very welcome change from earlier exams for which it was impossible to "guess" which skills Microsoft actually were going to test (due to very loosely defined objectives).

 

This is from the Microsoft site:

 

"The new exam format for MOS 2013 presents a short project that the candidate must complete, using the specifications provided. This creates a real-world testing experience for candidates". 
 
Microsoft advise that Excel Skills are tested in the way that they are taught in our courses -  in the context of solving a real business problem.  Our teaching method -  with every skill taught using an example workbook from the real world of business and commerce - should prove ideal for preparation for your exam.  You'll also learn many more extremely useful skills that go well beyond those included in the MOS "Expert" certification. 
 
If you complete our Expert course I know that you will be able to address any real-world business problem for which Excel is an appropriate tool.  If you look at some of the real-world business problems posed on this forum by professional Excel users,  you'll see that many are extremely complex but that all have been solved using only the skills taught in our course. (The solutions are always cross-referenced back to video and e-book lessons that teach the skills used in the solution) .
 
I'd suggest that you proceed as follows:
 
1/ Work through the 2013 Essential Skills and Expert Skills courses until you can complete all of the exercises at the end of each session without referring back to the book or video lesson.
 
2/ Study the problems posed on this forum until you thoroughly understand the solutions we have provided.
 
If you do take (and hopefully pass) the test it would be very interesting if you could post details of the project you were asked to complete and we'll provide a worked example of the solution by referring to the relevant lessons in the Essentials and Expert course.
 
I should also mention that  you will receive our own certification of course completion at the end of our course that you can print out and e-mail directly from your control panel (available when you log-in).    
 
Best Regards

 

Mike Smart




#86 Forum Rules - Please read before posting

Posted by TheSmartMethod on 03 September 2013 - 01:03 PM

Forum Rules

 

What happens if you break the rules?

The moderator will remove or edit your post and e-mail you explaining why your post has been edited/removed. 

In extreme cases you may be banned (or suspended) from making further forum posts.

The interpretation and application of “the rules” is done by forum moderators.  Their decision is final.

 

The Rules

There are only four simple rules:

 

Rule #1 – Be Nice

If you are a nice person you don’t really even need to read this part!

  • All posts must remain polite and respectful. 
  • User names that have the potential to cause offence to any other user may not be used.
  • Images that have the potential to cause offence to any other user may not be used as avatars.
  • Spam is prohibited.  Spam means the promotion of products or other web sites rather than a genuine contribution towards solving the problem being discussed.  Signatures may not include links to third party websites.
  • You may not use this board to offer solutions in exchange for money.
  • Posts may not promote any illegal activity (such as by-passing copyright protection measures).

Rule #2 – Make the forums as useful as possible

  • Posts must be made in English. 
  • This forum is about using Excel rather than extending it with the VBA programming language.  Suggested solutions to problems must use standard Excel features (including recorded macros) and not be implemented using VBA program code.
  • Do not suggest that a user moves a discussion to private messages or another forum. 
  • Do not post duplicate questions.  
  • If you think another member has broken one of the forum rules use the "Report" button underneath the post to refer it to a moderator. Do not refer to the violation within the discussion thread itself. 
  • The moderators may correct your posts for typos or grammatical errors if this will make them easier for others to read. 

Rule #3 – Do not link to other sites

Your post may still be on our forum in several years’ time. The Internet is constantly changing and if you include links in your posts it is probable that the linked data will change (or disappear completely) in the future. 

 

We can only be sure that links to pages and posts on our own site will always work correctly.

For this reason you can only post hyperlinks to:

  • Any ExcelCentral.com video or e-book lesson
  • Any other thread or posting in the Excel Central forum.

Apart from these two types of links, information should always be provided within the post and not by a link to another resource.

 

Rule #4 – Stay focused

  • Keep scope as narrow as possible.  Don’t ask questions such as: How do I create an accounting system using Excel
  • Use concise and accurate thread titles.  Don’t create threads with abstract names such as Please Help, Need Advice etc.
  • Keep answers on topic.  Don’t post a new unrelated problem on a focused thread.  Start a new thread instead.
  • Do not post one-liners that add no value to the discussion such as OK, I agree etc.



#85 Forum Rules - Please read before posting

Posted by TheSmartMethod on 03 September 2013 - 01:03 PM

Forum Rules

 

What happens if you break the rules?

The moderator will remove or edit your post and e-mail you explaining why your post has been edited/removed. 

In extreme cases you may be banned (or suspended) from making further forum posts.

The interpretation and application of “the rules” is done by forum moderators.  Their decision is final.

 

The Rules

There are only four simple rules:

 

Rule #1 – Be Nice

If you are a nice person you don’t really even need to read this part!

  • All posts must remain polite and respectful. 
  • User names that have the potential to cause offence to any other user may not be used.
  • Images that have the potential to cause offence to any other user may not be used as avatars.
  • Spam is prohibited.  Spam means the promotion of products or other web sites rather than a genuine contribution towards solving the problem being discussed.  Signatures may not include links to third party websites.
  • You may not use this board to offer solutions in exchange for money.
  • Posts may not promote any illegal activity (such as by-passing copyright protection measures).

Rule #2 – Make the forums as useful as possible

  • Posts must be made in English. 
  • This forum is about using Excel rather than extending it with the VBA programming language.  Suggested solutions to problems must use standard Excel features (including recorded macros) and not be implemented using VBA program code.
  • Do not suggest that a user moves a discussion to private messages or another forum. 
  • Do not post duplicate questions.  
  • If you think another member has broken one of the forum rules use the "Report" button underneath the post to refer it to a moderator. Do not refer to the violation within the discussion thread itself. 
  • The moderators may correct your posts for typos or grammatical errors if this will make them easier for others to read. 

Rule #3 – Do not link to other sites

Your post may still be on our forum in several years’ time. The Internet is constantly changing and if you include links in your posts it is probable that the linked data will change (or disappear completely) in the future. 

 

We can only be sure that links to pages and posts on our own site will always work correctly.

For this reason you can only post hyperlinks to:

  • Any ExcelCentral.com video or e-book lesson
  • Any other thread or posting in the Excel Central forum.

Apart from these two types of links, information should always be provided within the post and not by a link to another resource.

 

Rule #4 – Stay focused

  • Keep scope as narrow as possible.  Don’t ask questions such as: How do I create an accounting system using Excel
  • Use concise and accurate thread titles.  Don’t create threads with abstract names such as Please Help, Need Advice etc.
  • Keep answers on topic.  Don’t post a new unrelated problem on a focused thread.  Start a new thread instead.
  • Do not post one-liners that add no value to the discussion such as OK, I agree etc.



#793 Length of Courses

Posted by Mike on 14 March 2014 - 12:28 AM

Hi

 

How long it takes you to compete either course is (of course) extremely variable. A lot depends upon whether you are completely new to Excel or whether there is a degree of past exposure.

 

Most users of Excel do not need Expert Skills.  To work in the average office job you will usually be hugely in advance of the average other users if you "only" have the skills described in the "Essential Skills" course. 

 

The books are based upon Smart Method classroom courses.  With well motivated and above average intelligence adult course attendees, with zero previous Excel experience, we have trained thousands of Excel users (without exception) to "Essential Skills" standard in one eight hour intensive day.  Unfortunately this is in a classroom environment (and hugely expensive compared to the cost of the book or video course).

 

Just to listen to each Essential Skills video lesson will take around eight hours.  In a (non classroom) environment, using only books or videos as the learning material, I would guess that three days (of intensive attention) would be the maximum needed by most adult learners to master every skill presented.

 

Expert Skills is a little more advanced. 

 

To truly achieve Expert status I ask my corporate clients to allocate two days of intensive classroom training (for users with existing good Excel skills).  In effect most go for one day and it is possible to "cherry pick" different skills to cover those most of relevance to the class in this time frame.  For self study it would be good to double this estimate to 2-4 days depending if you need to know "everything" or a subset of the skills presented.  But once again, you need to ask yourself if you really need Expert Skills (few users really do).

 

Best Regards

 

 

Mike Smart




#55 Do you have similar courses for Word, PowerPoint and other applications?

Posted by TheSmartMethod on 02 September 2013 - 04:34 PM

The only Smart Method courses that have been published as books/videos so far are:

 

• Learn Access 2003 VBA With The Smart Method ISBN-13: 978-0955459900

• Learn Excel 2007 Essential Skills with the Smart Method ISBN-13: 978-0955459924

• Learn Excel 2007 Essential Skills with the Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2007 Expert Skills with The Smart Method ISBN-13: 978-0955459931

• Learn Excel 2007 Expert Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2010 Essential Skills with The Smart Method ISBN-13: 978-0955459979

• Learn Excel 2010 Essential Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2010 Expert Skills with The Smart Method ISBN-13: 978-0-9554599-8-6

• Learn Excel 2010 Expert Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2013 Essential Skills with The Smart Method ISBN:  978-1-909253-06-3

• Learn Excel 2013 Essential Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2013 Expert Skills with The Smart Method ISBN:  978-1-909253-07-0

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Essential Skills with The Smart Method ISBN-13: 978-0955459962

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Essential Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Expert Skills with The Smart Method ISBN-13:978-0-9554599-9-3

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Expert Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

• Learn ASP.NET 4.5, C# and Visual Studio 2012 Essential Skills with The Smart Method ISBN-13: 978-1-909253-04-9

• Learn ASP.NET 4.5, C# and Visual Studio 2012 Expert Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

• Learn ASP.NET 4.5, C# and Visual Studio 2012 Expert Skills with The Smart Method ISBN-13: 978-1-909253-05-6

• Learn ASP.NET 4.5, C# and Visual Studio 2012 Expert Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

 

Eventually Mike Smart will write up his Access, Word, Project, PowerPoint… and other courses as books and videos but it will take many years to complete them all. The Smart Method are currently exploring ways of increasing productivity by employing proof-readers, testers and video editors to free more of Mike’s time for course design, writing and recording.




#949 Advice for Intro College Course

Posted by Mike on 09 May 2014 - 06:31 PM

Dear John

 

Pivot Tables are most definitely an Expert skill.  I’ve taught Excel classes to corporate executives for over ten years and it is rare to find any Excel business users that understand Pivot tables.   This skill is even rare amongst professional accountants.

 

Perhaps the first step would be to establish whether your students really need Pivot Table skills.  These skills are mainly used to analyze de-normalized data extracts from relational databases rather than to analyse data that has actually been input into Excel.

 

Assuming that your students really do need pivot table skills they should also be able to understand tables.  Since Excel 2007 was released,  tables (rather than ranges) are best practice for the data source underpinning a pivot table.  Tables are covered in Session One: Tables and Ranges (Expert Skills) 

 

As a minimum it would be possible to proceed directly to Session Five: Pivot Tables of Expert Skills after digesting Session One: Tables and Ranges of Expert Skills but many of the functions taught in Session Three: Advanced Functions of Expert Skills are often needed in order to prepare the data table (when extracted from a relational source) prior to creating a pivot table from it.

 

Excel 2013 has also added a new spin to Pivot Tables with the introduction of the OLAP Pivot Table and the data model.  There’s an entirely new session in 2013 Expert Skills devoted to this subject Session Six: The Data Model, OLAP, MDX and BI that also covers relational database theory (essential to the understanding of OLAP Pivot Tables).  I’d be surprised if even one percent of Excel business users, now and in the future, will ever have any understanding of OLAP (though it is tremendously useful when working with relational database data sources).

 

When I teach my Excel classroom courses it is rare to have a student who has no prior exposure to Excel.  For this reason it is usually possible to “cherry pick” lessons from different sessions based upon existing skills and the need for certain features.   This approach only works with small classes with similar prior experience (my maximum class size is six).

 

Essential Skills is designed to give students excellent Excel Skills (skills that will put them ahead of the average office worker and an employer’s expectations for “good” Excel skills).  Expert Skills covers just about every Excel feature and also provides the skills to use any other feature that isn’t explicitly covered.  These are skills that are not needed or expected in most occupations.

 

Best Regards

 

 

Mike Smart




#480 How to share worksheets in Excel

Posted by Jonathan on 01 November 2013 - 01:59 PM

Hi Silvia,
 
There are three different ways to share a workbook, the Locked Document method, The Merge method and by sharing workbooks on a network (the differences are discussed in: Expert Skills, Lesson 8-10: Understand the three different ways to share a document).
 
There's also an (almost hidden) feature called "compare and merge workbooks" that can help you with the merge process.  This is discussed in: Expert Skills, Lesson 7-12: Share a workbook using the merge method.
 
Having said that, from your description it sounds like all of your problems would be solved if you could implement security to correctly address the business need (all of Excel's security features are covered in: Expert Skills, Session Seven: What If Analysis and Security).  You'd find it very rewarding to revise this session as there's a high probability that you'll discover the best solution to the problem when you are fully conversant with Excel's extensive security features.
 
If you decide to continue down the route of having 3 separate spreadsheets and merging them together, you can also potentially highlight any discrepancies by using an IF formula.  You can then use conditional formatting to highlight the discrepancies.
 
The formula to check whether the answers are all equal will look something like:
=IF(AND([Answer1]=[Answer2],[Answer2]=[Answer3]),"YES","NO")
You can learn more about the IF and AND functions in the following lessons:
Expert Skills Lesson 3-5: Use the IF logic function
Expert Skills Lesson 3-16: Use the AND and OR functions to construct complex Boolean criteria
 
To learn about conditional formatting, see Essential Skills Lesson 4-15: Use simple Conditional Formatting.
 
I have attached an example spreadsheet showing how this might work:
Attached File  Questions.xlsx   10.42KB   326 downloads
questions.png
 
I hope this information is useful, and please reply if you need any more assistance.




#2816 Video Not Working 2013 Expert Lesson 1-8: Extract unique records ...

Posted by Mike on 07 October 2017 - 12:20 PM

Hi Nero

 

I can see that you are using Firefox version 52.0 (released about seven months ago).   I tested myself using Firefox but noted that the latest version (the version I have installed) is Firefox (version 56.0).  

 

I think you will find that you have disabled updates in Firefox.  If you enable updates Firefox should automatically update to the current version and I think you'll find that everything then works perfectly.  You should also find that the video views perfectly in the current version of the other four browsers in common use (Internet Explorer, Edge, Google Chrome and Safari).

 

Best Regards

 

Mike Smart




#2319 Charting using {=TABLE()}

Posted by Jonathan on 22 April 2016 - 07:14 PM

Hi Budana,

 

The TABLE function only works in conjunction with Excel's Data Table feature, so you will only see it where a data table has been used.

 

You can see a complete explanation of data tables in the following video lessons:

Expert Skills Lesson 7-1 Create a single-input data table

Expert Skills Lesson 7-2 Create a two-input data table

 

If you create a data table using the skills taught in those lessons you should be able to apply it to charts like the one in your example.

 

I hope this helps, but please feel free to reply if you need any more help with this.




#2265 number formats

Posted by JPK1066 on 29 March 2016 - 08:28 PM

Hi

 

 I;m trying to copy a WS with a lot of 1-0 and 2-0 and 1-3 counts and Excel keeps reading it as a date ... how do I get an exact copy of the numbers?




#1885 how to title/name columns

Posted by Jonathan on 11 September 2015 - 09:29 PM

Hi Beloved,

 

If you create a Table, the headings in the Table will always appear at the top of the Excel screen even if you have scrolled down so that the first row is no longer visible.

 

You can see this in action, as well as how to create a Table in the video lesson: Expert Skills Lesson 1-11 Convert a range into a table and add a total row.

 

I hope this is what you were looking for, but please feel free to reply if you need any more help with this.




#1795 Custom View button is disabled

Posted by Jonathan on 05 August 2015 - 06:47 PM

Hi jegllaume,

 

There are a number of treasons why this can happen:

I hope that you're able to solve the proble with this information, but if not please feel free to attach your workbook and I will take a look at it.

 

For instructions on attaching workbooks to posts, click here.




#1752 analyzing discontinuous number strings

Posted by Jonathan on 08 July 2015 - 04:06 PM

Hi David,

 

While you could produce a correct result using Excel, this sounds like a problem that could be more suited to programming as it may be a very laborious process to create all of the formulas that are needed.

 

If you do want to proceed using Excel, you should be able to extract the strings by using Excel's FIND function along with several other functions including LEN and MID.

 

I have attached an example workbook showing how this might be achieved:

 

Attached File  StringSplit.xlsx   11.1KB   209 downloads

StringSplit.png

 

As you can see, the first thing I have done is to concatenate all of the values together into a single string using the concatenation operator (&). This makes it possible to use the FIND function on the resulting string.

You can find a more in-depth look at how concatenation is done in the video lesson: Expert Skills Lesson 3-18 Concatenate strings using the concatenation operator.

 

Next, I extract the first string using the following rather complicated formula:

=MID([@Concatenated],FIND("0",[@Concatenated]),FIND("1",[@Concatenated],FIND("0",[@Concatenated]))-FIND("0",[@Concatenated]))

 

This formula finds the first zero in the string and the first 1 in the string and then uses the MID function to extract the characters between them.

 

In order to find the correct place to start to find the next string, the following formula is used:

=FIND([@String1],[@Concatenated])+LEN([@String1])

 

This simply searches for the string in the text and adds the length of the string to the result.

 

You can find more about the FIND and LEN functions in the video lesson: Expert Skills Lesson 3-21 Extract text from delimited strings using the FIND and LEN functions.

You can learn more about the MID function and other string handling functions in the video lesson: Expert Skills Lesson 3-20 Extract text from fixed width strings using the LEFT, RIGHT and MID functions.

 

Now the process can be repeated as many times as are needed to extract all of the strings. In my example I have also added an IFERROR function to return a blank result if there are no more strings to extract. You can find more about IFERROR in the video lesson: Expert Skills Lesson 3-23 Use an IFERROR function to suppress error messages.

 

Now that the strings are extracted, it's a simple matter to find their lengths using the LEN function and discover the longest, shortest and average using the MAX, MIN and AVERAGE functions. These are covered in the video lessons in Essential Skills Session 2: Doing Useful Work with Excel.

 

I hope this is what you were looking for, but please feel free to reply if I can offer any further assistance.




#1377 Consolidate figures from raw data

Posted by Jonathan on 10 January 2015 - 04:19 PM

Ok sir..This what actually i want....And i have one doubt...If i am creating a template using the file you have send, but by deleting all the sourse data from 'weighment sheet', keeping the pivote table format as such, won't it be work?...Because, if somebody else is using this file, by entering data in weighment sheet itself, they will get the consolidated data they need...

 

You should be able to use this as a template if you delete every row from the table except for the first one. You need to keep the first row, since deleting the first row would remove all of your formulas.

 

Because the data is held in a Table, the pivot tables will automatically pick up any new data that is added by the user when they are refreshed.

 

As I mentioned in my previous post, you can find more about Tables in: Expert Skills Lesson 1-11 Convert a range into a table and add a total row.  I really recommend reviewing this lesson if you're not sure about Tables.




#1140 Formatting a range using an Excel macro

Posted by Jonathan on 11 September 2014 - 08:13 PM

Hi Frank,

 

I'm not quite sure if I'm understanding what you're trying to do, but it sounds like you could do this by recording a Macro that cuts cells A2:A5 and pastes them into cells A1:A4, then cuts cell B3 and pastes it into cell A5.

 

You wouldn't be able to make this happen automatically with a macro, but you could use a shortcut key or link a button to your macro in order to run it very quickly and easily.

 

For instructions on how to record a macro, see the video lesson: Expert Skills Lesson 9-11 Record a macro with absolute references.

For instructions on linking macros to buttons, see the video lesson: Expert Skills Lesson 9-16 Use shapes to run macros.

 

I hope this helps, but if you'd like me to take a look at your workbook, please feel free to reply and attach it.  For instructions on how to attach files to posts, see this thread.




#113 Adding a Second Combo Box to a Combo Box

Posted by Jonathan on 04 September 2013 - 06:11 PM

Hi Owls1982,
 
I don't think comboboxes can be selectively hidden and shown using Excel's built-in features. 
 
Of course, a programmer could add this capability by writing custom VBA (Visual Basic for Applications) program code. 
 
However, you can solve this problem without using comboboxes (or program code) by using list validation on your cells instead. List validations add a control that resembles a combo box to worksheet cells.
 
You can then use the INDIRECT function within your validation to link a two list validations to produce the functionality you describe.
 
This is a complicated technique that requires knowledge of list validation, so you might want to start by reviewing Expert Skills Lesson 2-11: Add a table-based dynamic list validation.  Table-based list validation lets you make cells act like comboboxes and display a list of items from a specific area on the page.
 
To learn how to link two validation drop-down menus together, take a look at: Expert Skills Lesson 4-8: Create two linked drop-down lists using range names. The technique works by creating range names that match each entry in your first 'drop down menu' and point to some cells containing the items to be displayed in your second 'drop down menu'.
 
If all of this is completely new to you, you might want to start at the beginning of session 4 and work your way through to lesson 4-8.  The earlier lessons in session 4 will teach you about range names and the INDIRECT function, which will help a lot when trying to implement this.




#10 Forum Rules - Please read before posting

Posted by TheSmartMethod on 02 September 2013 - 02:05 PM

Forum Rules

 

What happens if you break the rules?

The moderator will remove or edit your post and e-mail you explaining why your post has been edited/removed. 

In extreme cases you may be banned (or suspended) from making further forum posts.

The interpretation and application of “the rules” is done by forum moderators.  Their decision is final.

 

The Rules

There are only four simple rules:

 

Rule #1 – Be Nice

If you are a nice person you don’t really even need to read this part!

  • All posts must remain polite and respectful. 
  • User names that have the potential to cause offence to any other user may not be used.
  • Images that have the potential to cause offence to any other user may not be used as avatars.
  • Spam is prohibited.  Spam means the promotion of products or other web sites rather than a genuine contribution towards solving the problem being discussed.  Signatures may not include links to third party websites.
  • You may not use this board to offer solutions in exchange for money.
  • Posts may not promote any illegal activity (such as by-passing copyright protection measures).

Rule #2 – Make the forums as useful as possible

  • Posts must be made in English. 
  • This forum is about using Excel rather than extending it with the VBA programming language.  Suggested solutions to problems must use standard Excel features (including recorded macros) and not be implemented using VBA program code.
  • Do not suggest that a user moves a discussion to private messages or another forum. 
  • Do not post duplicate questions.  
  • If you think another member has broken one of the forum rules use the "Report" button underneath the post to refer it to a moderator. Do not refer to the violation within the discussion thread itself. 
  • The moderators may correct your posts for typos or grammatical errors if this will make them easier for others to read. 

Rule #3 – Do not link to other sites

Your post may still be on our forum in several years’ time. The Internet is constantly changing and if you include links in your posts it is probable that the linked data will change (or disappear completely) in the future. 

 

We can only be sure that links to pages and posts on our own site will always work correctly.

For this reason you can only post hyperlinks to:

  • Any ExcelCentral.com video or e-book lesson
  • Any other thread or posting in the Excel Central forum.

Apart from these two types of links, information should always be provided within the post and not by a link to another resource.

 

Rule #4 – Stay focused

  • Keep scope as narrow as possible.  Don’t ask questions such as: How do I create an accounting system using Excel
  • Use concise and accurate thread titles.  Don’t create threads with abstract names such as Please Help, Need Advice etc.
  • Keep answers on topic.  Don’t post a new unrelated problem on a focused thread.  Start a new thread instead.
  • Do not post one-liners that add no value to the discussion such as OK, I agree etc.

  • jon likes this