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

• 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.




#895 Displaying years in chart axis labels

Posted by Jonathan on 04 April 2014 - 02:34 PM

Hi Richard,

 

I see the problem now.  When Excel sees your years, it recognizes them as numbers instead of text and tries to include them in the chart instead of using them as labels:

 

badchart3.png

 

You can rectify this by creating your chart, then right-clicking it and clicking Select Data from the shortcut menu, then setting the Series and Axis Labels settings appropriately.

For an in-depth look at how this is done, see the PDF and video lesson: Essential Skills Lesson 5-14: Change source data using the Select Data Source dialog tools.

 

The correct settings will look something like this:

 

badchart4.png

 

If you have trouble doing this, or don't want to go to the bother of changing the settings every time you create a chart, you can work around this problem by entering the dates as text instead of numbers.  Excel will then automatically use them as labels when you create a chart.

 

Entering numbers as text is covered in the PDF and video lesson: Essential Skills Lesson 2-1: Enter text and numbers into a worksheet.

 

I have attached my example spreadsheet with the years formatted as text.

 

Attached File  expenditurebyyear.xlsx   13.07KB   287 downloads

expenditurebyyear.png

 

If you need any more assistance, please feel free to reply.




#785 Creating a formula to add letters

Posted by Jonathan on 12 March 2014 - 09:47 PM

Hi Paula,

 

I've given it some thought, and I think you can achieve what you want by using the VLOOKUP and SUMIF functions, along with the skills needed to work with Tables.

 

You'll first need to create a Table containing all of the letters of the alphabet, along with the value that you want to assign to each letter and whether or not the letter is a vowel.

 

charcodes1.png

 

If you're not clear on the concept of tables, you can learn all about them in the video lesson: Expert Skills Lesson 1-11 Convert a range into a table and add a total row.

 

Next you'll need to create another Table to contain your letters.  It's important to define it as a Table so that it can contain any number of letters without any formulas needing to be adjusted.

 

You can use the VLOOKUP function in this Table to automatically extract the type and value for each letter.

 

charcodes2.png

 

For a more detailed explanation of the VLOOKUP function, see the video lesson: Expert Skills Lesson 3-22: Use a VLOOKUP function for an exact lookup.

 

Now that you have the values you need, there are many different ways that you could summarize them.  I've chosen to use the SUMIF function to create the summaries that you require:

 

charcodes3.png

 

Because you are storing your values in a Table, these functions will work no matter how many letters you add.

 

For more details on how to use the SUMIF function, see the video lesson: Expert Skills Lesson 3-6: Use the SUMIF and COUNTIF logic functions to create conditional totals.

 

I have attached my example spreadsheet.

 

Attached File  NamesToNumbers.xlsx   10.98KB   467 downloads

namestonumbers.png

 

I hope this is helpful.

 

If you need any more assistance, please feel free to reply.




#579 Calculating Payments, Present Value, Interest Rate and Period in Excel

Posted by Jonathan on 10 December 2013 - 11:36 PM

Hi TJ,
 
You seem to be asking if you can calculate any of the four values (Payments, Present Value, Interest Rate and Number of Periods) as long as you know three of them.  Fortunately, Excel has a separate function to calculate each one of these!  These are the PMT, PV, RATE and NPER functions.
 
You can calculate Payments by using Excel's PMT function, as covered in Expert Skills Lesson 3-3: Use the formula palette and the PMT function.
Each of the four functions works in a very similar way, so I would recommend starting here and making sure that you completely understand the PMT function before continuing with the others.
 
If you wanted to calculate the Present Value when you knew the Payments, Interest Rate and Number of Periods, you could use the PV function in the same way as you would use the PMT function. The PV function is fully explained in Expert Skills Lesson 3-4: Use the PV and FV functions to value investments.  This lesson also covers the FV function, which allows you to calculate the Future Value - see the lesson for more on this.
 
If you wanted to calculate the Interest Rate when you knew the Payments, Present Value and Number of Periods, you could use Excel's RATE function.
 
If you wanted to calculate the Number of Periods based on the Present Value, Interest Rate and Payments, you could use Excel's NPER function.
 
The PMT, PV, RATE and NPER functions all work in a very similar way.  The only difference between them is which of the four values they calculate.
 
I have attached an example spreadsheet showing how to calculate each of the four values.
 
Attached File  Interest.xlsx   11.67KB   264 downloads
interest.png
 
If you are having difficulty understanding how to use any of the four functions, I recommend reviewing the following lessons and making sure that you understand all of the concepts covered:
Expert Skills Lesson 3-3: Use the formula palette and the PMT function
Expert Skills Lesson 3-4: Use the PV and FV functions to value investments
 
I hope this information is useful. If you are having trouble implementing this, please reply to this post with further details of what you are trying to do.




#2604 Excel practice

Posted by Mike on 23 January 2017 - 12:25 PM

Hi Alexu

 

An exercise (and exercise answers)  is provided at the end of each session to allow you to test your skills and make sure that you remember everything you have learned. If you’re able to complete the exercise without referring back to the lessons you know that you have absorbed all of the skills taught.  You can see examples of the exercises in our free Basic Skills course.

 

If you have purchased the book/e-book, or have subscribed to a video courses,  for an Essential Skills or Expert Skills course you will be able to access an exercise at the end of each session.

 

We don’t currently offer exercises apart from the ones at the end of each session, but we will soon be publishing a new series of ‘construction kit’ books that take you through the process of building specific projects in Excel and might be of interest to you.

 

You also might be interested to browse this forum.  You'll find hundreds of Excel questions that have been asked by our users detailing the application of Excel skills to real-world business problems.  The huge difference between this forum and public Excel forums is that each solution is provided by one of our professional consultants. You might find that some of the questions and answers on the forum are useful as exercises to test your own skills (or as ideas for exercises if you’re teaching a class).

 

I hope this is the information you were looking for, but please let us know if you need any further assistance.




#2556 Applying an advanced filter with function driven criteria

Posted by Jonathan on 11 November 2016 - 02:18 PM

Hi Pandasia,

 

If you're following the same steps as in Expert Skills Lesson 1-7, you'll be using the following formula:

 

=MOD(A7,5)

 

This should work if you have placed ascending numbers in column A, starting in cell A7. My guess is that your numbers may start in cell A6 or A8, which would explain why you're not getting the correct results.

 

I hope this helps, but please feel free to reply if you're still having problems. If you'd like to attach a copy of your workbook I should be able to find the problem (instructions here).




#2351 Apply an advanced filter with function-driven criteria (Excel 2013)

Posted by Jonathan on 24 May 2016 - 05:48 PM

Hi Humaira,

 

Yes, it is possible to do this, and you'll see how in the next video lesson: Expert Skills Lesson 1-8 Extract unique records using an advanced filter.

 

The trick is to invoke the advanced filter from the worksheet that needs to receive the results instead of from the worksheet that holds the source data. If you're still not sure how to do this after watching the lesson above, please feel free to reply.




#2166 Is there a way to concatenate multiple cell formulas (instead of resulting va...

Posted by Jonathan on 29 January 2016 - 07:38 PM

Hi bennyb,

 

We can't offer VBA support on these forums, but there is an undocumented way to do this without using VBA.

 

There is a 'secret' EVALUATE function that can only be accessed by creating a range name. If you're not sure how to do this, you can find an explanation in the video lesson: Expert Skills Lesson 4-2 Manually create single cell range names and named constants.

 

For example, to execute a formula that is stored as text in cell A1, you could define the following range name:

 

Evaluate.png

 

The EVALUATE function will not work if you try entering it as a cell formula. It can only be accessed by creating a range name in this way.

 

After creating the range name, you can extract the result with the formula: =EvaluateResult

 

Note that, even though this doesn't appear to be a macro, it will actually cause Excel to run a macro 'behind the scenes'. This means that it will only work within a macro-enabled workbook.

 

I have attached a working example:

 

Attached File  Evaluate.xlsm   9.37KB   66 downloads

Evaluate2.png




#2063 Project 3 years out from date entered minus 1 day

Posted by Jonathan on 02 December 2015 - 09:28 PM

Hi phoenixrizing,

 

I think the problem here is that you seem to be using the TEXT function to try to convert the date into text instead of using the DATE function to turn your entered date into a date serial number. I think the solution is to use the DATE function.

 

To get the date 3 years in the future minus one day, you can use the following formula:

=DATE([Year]+3,[Month],[Day])-1

 

You can see an explanation of how the DATE function is used to offset dates like this in the video tutorial: Expert Skills Lesson 3-11 Use the Excel DATE function to offset days, months and years.

 

This works fine with your workbook except for with the month value, which is entered in the form of text instead of as a number. To solve this, I've simply created a table of months with their appropriate numbers and used VLOOKUP to return the number of the month entered so it can be used in the DATE function.

 

How to use VLOOKUP is explained in the video lesson: Expert Skills Lesson 3-22 Use a VLOOKUP function for an exact lookup.

 

I have attached a copy of your workbook with the DATE and VLOOKUP formulas applied:

 

Attached File  date forecast.xlsx   12.53KB   58 downloads

DateForecast.png

 

This seems to give the result you were looking for, but please feel free to reply if you need any more help.




#2057 Is it possible to immediately display a comment or something on a locked shee...

Posted by Murph on 30 November 2015 - 09:13 PM

Hi,

 

My team will not allow me to use VBA code.

However, I do want the worksheet to be user friendly.

 

Is there a way to automatically pop a comment box or something?

 

Here are the cell contents, (which are locked) and non-modifiable:

Is the QuarterlyPatchingDevelopment team engaged at the customer's site?

 

Here is the content of the proposed comments box:

xxxxxxxxx:  aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

xxxxxx:        bbbbbbbbbbbbbbbb

xxxxxxxxx:  ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc

 

My thinking being is the cell following this cell allows an entry of either N or Y and has the data validation of:

Please enter a Y or N for Yes or No

 

So what I would like is for the user to see the reason for the N or Y choices via the comments box.

 

If my way is not feasible, is there a way to accomplish something similar?

 

Cell is locked and only certain fields are allowed entry.

 

Many Thanks in advance!

 

Murph

  :unsure:




#1653 Using VLOOKUP, if Column 5 is blank, get value from Column 6

Posted by Jonathan on 08 May 2015 - 11:51 AM

Hi Simon,

 

I think you can achieve this using an IF function rather than IFERROR.

 

You will need a formula that looks something like this:

=IF(VLOOKUP([@CustomerID],Customer,3)="",VLOOKUP([@CustomerID],Customer,4),VLOOKUP([@CustomerID],Customer,3))

 

This first checks whether your VLOOKUP for column number 3 returns a blank, then returns column number 4 if it does and returns column number 3 otherwise.

 

For a video walkthrough of the IF function, see: Expert Skills Lesson 3-5 Use the IF logic function.

For a video walkthrough of VLOOKUP, see: Expert Skills Lesson 3-22 Use a VLOOKUP function for an exact lookup.

 

You'll also notice that my formulas are using structured table references like [@CustomerID]. You can use cell references in place of these, but structured table references do help to make formulas easier to understand and maintain.

 

You can see an introduction to tables and structured table references in the following video lessons:

Expert Skills Lesson 1-11 Convert a range into a table and add a total row

Expert Skills Lesson 1-17 Name a table and create an automatic structured table reference

 

I have attached an example workbook showing this formula in action:

 

Attached File  VLOOKUPIF.xlsx   10.08KB   606 downloads

VLOOKUPIF.png

 

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




#1581 Reconcile information

Posted by Jonathan on 03 April 2015 - 09:38 AM

Hi Hongmin,

 

The first thing I've done is to convert both of your data sets into Tables named Changepoint and LaborDistribution. This makes it easier to create pivot tables based upon them.

If you're not familiar with Tables, you can find an introduction to them in the video lesson: Expert Skills Lesson 1-11 Convert a range into a table and add a total row.

 

It's then relatively simple to create pivot tables from your data by using the skills taught in the video lesson: Expert Skills Lesson 5-1 Create a pivot table.

 

I've created two pivot tables that show a sum of Hours for each ELCID and placed them side by side:

 

pivot1.png

 

This almost works for your purposes, but it won't allow you to reconcile your figures easily because the Changepoint table contains one more ELCID than the LaborDistribution table (FIJUDICIAL) so the two tables go out of sync after a certain point.

 

You could resolve this by making sure that both data sets contain the same ELCID values, but you could also solve this by using Excel 2013's new Data Model features to define a relationship that joins the tables together.

 

To achieve this gracefully, I first created a table that contains all of the ELCID values and then created relationships to join the ChangePoint and LaborDistribution tables to it:

 

pivot2.png

 

For an introduction to creating relationships, see the video lesson: Expert Skills Lesson 6-2 Create a simple data model.

 

Once the relationships have been defined, I can create a single 'OLAP' pivot table that is able to work with data from both of your tables at the same time:

 

pivot3.png

 

The last thing you wanted was to check whether the Changepoint and Labor Distribution values match for each ELCID. In an ordinary pivot table, you could do this using a Calculated Field, but unfortunately these aren't supported in OLAP pivot tables. The only solution at this point in time is to place a formula alongside the pivot table to check whether the values match.

 

You can do this using a simple IF function:

=IF(B2=C2,0,1)

 

This will place a 1 in the cell if the values do not match, so you can find out how many values do not match by simply summing the column.

For an introduction to the IF function, see the video lesson: Expert Skills Lesson 3-5 Use the IF logic function.

 

I have also applied conditional formatting to make it easier to spot the records that do not match.

For more on conditional formatting, see the video lesson: Essential Skills Lesson 4-15 Use simple conditional formatting.

 

pivot4.png

 

Of course, one more thing to consider is that you don't really need to use pivot tables for this at all! You could achieve the same thing by simply using formulas with the SUMIF function like this one:

=SUMIF(Changepoint[ELCID],[@ELCID],Changepoint[HRS])

 

The references like Changepoint[ELCID] are structured table references. You can learn about these in the video lesson: Expert Skills Lesson 1-17 Name a table and create an automatic structured table reference.

You can learn more about the SUMIF function in the video lesson: Expert Skills Lesson 3-6 Create an Excel SUMIF function and Excel COUNTIF function.

 

I have attached a copy of my example workbook showing all three different ways you could do this in the Std Pivot Table, OLAP Pivot Table, and Formulas Only worksheets.

 

Attached File  Copy of Pivotable practice.xlsx   1.11MB   197 downloads

pivot5.png

 

 




#1401 Error message while creating data relationship for OLAP Pivot Table

Posted by SueK on 19 January 2015 - 09:21 PM

While attempting to create a data relationship for an OLAP Pivot Table, I get the following error message:

 

Vertipaq property 'CustomerID' cannot have name binding

 

CustomerID is the primary and foreign key in the two tables.

 

What does the error message mean?


  • Own likes this