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.  




#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




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



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




#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




#541 Complex matching using HLOOKUP in Excel

Posted by Jonathan on 22 November 2013 - 02:46 PM

Hi Ajay,

 

It's possible to solve the problem by using HLOOKUP formulas as suggested before.  However, using the simple HLOOKUP formulas that I explained in my previous post would require you to do a lot of work filling in all of the formulas manually.

 

My attached example spreadsheet shows this approach in the Simple Formulas sheet.

 

hlookup6.png

 

It's possible to modify the formulas so that they can be automatically 'filled', which makes the task a lot easier - but only if you can understand how the formula works!

To create a 'fillable' formula, you'll need to use Excel's INDIRECT function.  If you're not familiar with INDIRECT, take a look at Expert Skills Lesson 4-5: Use intersection range names and the INDIRECT function.

 

As you can see in the screenshot above, your first task is to create a formula that will 'skip' the correct number of rows above it.  For example, in column P the formula must skip the first two rows.

 

This can be achieved by using the ROW and COLUMN functions to get the numbers of the row and column that the formula is inside:

=IF((COLUMN()- ROW()-12) <= 0,[HLOOKUP Search Formula Here],"")

 

This formula subtracts the row number from the column number and subtracts 12 from the result.  For example, column O is column number 15, so in cell O2 the formula will calculate 15 - 2 - 12 = 1.  Then in cell O3, the calculation will be 15 - 3 - 12 = 0.  The IF function will only perform the search if the result is 0 or less, so cell O2 will be left blank but all subsequent cells in column O will be searched.

 

If you're having trouble understanding this, refer to Expert Skills Lesson 3-5: Use the IF logic function.

 

The next step is to modify your HLOOKUP search formula to automatically search the correct row.  You can achieve this using the INDIRECT function.

 

Your original HLOOKUP formula looks like this:

=IF(ISERROR(HLOOKUP(A2,B2:M2,1,FALSE)),FALSE,TRUE)

 

In cell O2, you want the formula to search for the value in cell A3 in the range B2:M2 (the previous row), so you want the formula to look like this:

=IF(ISERROR(HLOOKUP(A3,B2:M2,1,FALSE)),FALSE,TRUE)

 

To achieve this automatically, you'll need to use the INDIRECT function in conjunction with the COLUMN function that you used previously:

=IF(ISERROR(HLOOKUP($A3,INDIRECT("B" & (COLUMN()-13) & ":M" & (COLUMN()-13)),1,FALSE)),FALSE,TRUE)

 

The important part here is:

INDIRECT("B" & (COLUMN()-13) & ":M" & (COLUMN()-13))

 

This INDIRECT function will extract the range that you need based on the column number.  For example, column O is column number 15, so column O will search B2:M2.  Column P is column number 16, so column P will search B3:M3, etc.  The INDIRECT function is covered in greater depth in Expert Skills Lesson 4-5: Use intersection range names and the INDIRECT function.

 

Notice that I have also used a $ symbol when specifying the number to search for.  This is an absolute reference.

Absolute references are explained in Essential Skills Lesson 3-12: Understand absolute and relative cell references.
 

If you now combine your formulas, you have a final formula that you can fill to as many rows and columns as you need:

=IF((COLUMN()- ROW()-12) <= 0,IF(ISERROR(HLOOKUP($A2,INDIRECT("B" & (COLUMN()-13) & ":M" & (COLUMN()-13)),1,FALSE)),FALSE,TRUE),"")

 

Filling formulas is covered in Essential Skills Lesson 2-15: Use AutoFill to adjust formulas.

 

I have attached a spreadsheet with two sheets.  One sheet shows the solution using simple HLOOKUP formulas, and the other shows the solution with the more complex (but fillable) formulas.

 

Attached File  221113 Excel Help Match Spreadsheeet.xlsx   288.78KB   399 downloads

hlookup7.png




#462 Remove gaps between bars in an Excel chart?

Posted by Jonathan on 25 October 2013 - 01:27 PM

To do this, first create your chart, then right-click on one of the bars and click Format Data Series:

gaps1.png

 

You should then see a setting called Gap Width.  If you set this to zero, the gaps will disappear.

gaps2.png

 

Note that this may appear differently depending on the version of Excel that you are using, but the setting should still be available and should still work correctly.

gaps3.png

 

For more information about formatting the different parts of charts, see Essential Skills Lesson 5-7 Manually format a chart element.

 

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




#29 Can I use the course to learn Excel on my Mac?

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

You can watch the videos on a Mac as our video courses will work on any computer that has a correctly functioning web browser.

 

The courses teach Excel 2013, Excel 2010 and Excel 2007 (the only versions widely used in the commercial world). These are Windows only programs and cannot be installed on a Mac (though it is possible to install both Windows and Excel 2007/2010/2013 onto a Mac).

 

Mac versions 2011 and 2008 (that can run on the native Mac operating system without Windows being installed) are cut-down versions of Excel that can’t open many of the sample files as the features being taught are not available on the cut-down Mac versions.

 

To learn Excel 2013, 2010 or 2007 and to use the book you need access to a Windows computer running the applications being taught or you need to load Windows and Excel 2013, 2010 or 2013 onto your Mac.




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




#2307 Changing from Excel 2007 to Excel 2013

Posted by Jonathan on 16 April 2016 - 11:18 AM

Hi gavin,

 

I've looked into this and Excel 2013 is compatible with Windows 7, but may require you to update to Windows 7 Service Pack 1. If you have the earliest version of Windows 7 that may explain why you're not being allowed to install Excel 2013.

 

I would suggest making sure that you're updated to Windows 7 SP1 and then trying to install again. If you're still unable to install Excel 2013 you may need to revisit your Microsoft account, make sure that you have the correct subscriptions, and contact Microsoft's support if necessary.

 

I hope this helps and you're able to install the latest version of Excel, but please feel free to reply if I can offer any more help.




#2258 Creditors Payment plan with criteria

Posted by Jonathan on 20 March 2016 - 10:16 PM

Hi Ayaz,

 

I'm not completely sure I understand your priority rules, but hopefully you'll be able to use my example as a starting point and adapt it to your needs.

 

I'm assuming that your "One Time Payment" creditors will pay the full balance on the first month, each of your monthly priorities will pay an equal portion of the amount split across a certain number of months and your NxtYear priority will pay nothing this year and pay the full balance next year.

 

Apart from the NxtYear priority, all of these are different versions of the formula:

 

IF the current month is less than the number of Repayment months, [Payable Amount] / [Number of Months]

 

To achieve this, you can use the Excel formula:

 

=IF(COLUMN()-4<=[Repayment Months],[Payable Amount]/[Repayment Months],0)

 

If you don't understand the IF function, you can find a full explanation in the video lesson: Expert Skills Lesson 3-5 Use the IF logic function.

The COLUMN function simply returns a number corresponding to the current column. For example, column A would return 1, column B would return 2, etc. In this case, I'm deducting 4 from the number so that it will return the number 1 for month 1, 2 for month 2, etc. An alternative approach would be to insert a new row containing numbers for each of the months.

 

A final detail is that your current table doesn't contain a simple value for the number of repayment months. Instead, you're using a list of Priority codes like OTP, Monthly, etc. To work around this, I've added the repayment months values to your table of priorities. You can then use the VLOOKUP function to extract them into your main table.

 

With the VLOOKUP function added, you now have the final formula:

 

=IF(COLUMN()-4<=VLOOKUP([@Priority],Priorities,4,FALSE),[@[Payable Amount]]/VLOOKUP([@Priority],Priorities,4,FALSE),0)

 

If the VLOOKUP function doesn't make sense to you, you can see how to use it in the video tutorial: Expert Skills Lesson 3-22 Use a VLOOKUP function for an exact lookup.

 

You might also be wondering about the references such as [@[Payable Amount]] instead of, for example, D2. These are called structured references, and you can find out all about them in the video lesson: Expert Skills Lesson 1-17 Name a table and create an automatic structured table reference.

 

One final detail is your NxtYear priorities which don't pay anything in the current year, but pay the whole balance in the next year. These can be catered for with just one more IF function in column Q:

=IF([@Priority]="NxtYear",[@[Payable Amount]],0)

 

I have attached my example workbook showing these formulas in action:

 

Attached File  Query.xlsx   15KB   70 downloads

Query.png

 

I hope this helps you to achieve what you need. Please feel free to reply if you need any more help with this.




#2186 Validate negative values

Posted by LT27 on 08 February 2016 - 12:39 PM

Good day!

 

Hope all is well with you! I want to create a formula when the values are negative to put 0 and change the spend type from Cost Savings to Cost Avoidances.

 

Any help will be greatly appreciated.

 

Thank you so much!

Attached Files




#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:




#1871 How do I split delimited text in excel 2010

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

Hi Sapine,

 

The feature you're probably thinking of in Excel 2013 is called Flash Fill, but you should still be able to achieve the same result in Excel 2010 using the Text to Columns feature.

 

For anyone who does have Excel 2013 Flash Fill offers a much easier solution, and you can learn all about it in the video lesson: Essential Skills Lesson 2-18 Use automatic Flash Fill to split delimited text.

 

You can learn how to split text in Excel 2010 using the Text to Columns feature in the video lesson: Expert Skills Lesson 2-2 Split delimited data using Text to Columns.

 

I hope you're able to split the names after reviewing the lesson on Text to Columns, but if you can't please feel free to reply if you continue to have problems. You can even attach a copy of your workbook if you'd like me to take a look at it. You can find instructions for attaching workbooks by clicking here.




#1769 How to do Data Validation with Conditional Formatting?

Posted by Jonathan on 21 July 2015 - 09:50 AM

Hi Mahesh,

 

It looks like you need 3 conditional formatting rules - one for "Done", one for "Inprogress" and one for "Yet to Start".

 

To make this work, you'll need the formula that I mentioned in my earlier reply to check whether the words appear anywhere within each cell.

This formula will look something like this:

=NOT(ISERROR(FIND("Yet to Start",F4)))

 

I have attached a copy of your workbook with these rules applied:

 

Attached File  Data Validation with Conditional Formatting.xlsx   11.81KB   212 downloads

Data Validation with Conditional Formatting.png

 

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




#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


#1374 Consolidate figures from raw data

Posted by Jonathan on 09 January 2015 - 04:47 PM

Hi Ganga,

 

I think you will be able to get what you need by using Pivot Tables based on the data in your Weighment sheet.

 

In order to allow a Pivot Table to automatically pick up all of the data in your Weighment sheet, I have converted the data there into a Table.  Tables are a relatively new feature of Excel that allows you to define a table of data that can grow and shrink and still be used by formulas, charts and pivot tables.

 

For a more in-depth explanation of Tables, see the video lesson: Expert Skills Lesson 1-11 Convert a range into a table and add a total row.

 

After the Table has been created, you can create Pivot Tables for your DSR, STACKWISE and WAGONWISE sheets.  Pivot Tables are used to summarize large amounts of data into totals, which is exactly what you need.  I've attached an example based on your first workbook, containing the Pivot Tables with the correct settings:

 

Attached File  BRGA.xlsx   76.17KB   169 downloads

BRGA.png

 

Pivot Tables won't automatically update as you enter data, but they can be refreshed very quickly and easily by right-clicking on them and clicking Refresh.  Pivot Tables will also be automatically refreshed every time the workbook is opened.

 

Setting this up requires quite a few of the skills that are covered in Expert Skills Session 5: Pivot Tables.  I'd recommend working through the entire session, but here are the lessons that are relevant to this task:

 

First, you'll need to create a Pivot Table and select the values you need, as covered in: Expert Skills Lesson 5-1 Create a pivot table.

To apply the styles you need, I've created a custom Pivot Table style, as covered in: Expert Skills Lesson 5-13 Create a custom PivotTable style.

Next, I've applied the Tabular layout.  Pivot Table layouts are covered in: Expert Skills Lesson 5-14 Understand pivot table report layouts.

To make the data tidier, I've removed the subtotals, as covered in: Expert Skills Lesson 5-15 Add and remove subtotals and apply formatting to pivot table fields.

For more on how Pivot Tables are refreshed, see: Expert Skills Lesson 5-5 Refresh a Pivot Table and understand the Pivot Table data cache.

 

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




#1003 Macro to copy data from a form into a table

Posted by Jonathan on 12 June 2014 - 08:38 PM

Hi Rathin,

 

This is the kind of task that is far more suited to Microsoft Access.  I'd suggest that you look into using Access instead of Excel for this purpose.

 

Having said that, it's possible to achieve that you want by recording a macro using a combination of absolute and relative references.

 

In order to copy the details into a new record on your Record sheet, you must start recording a macro with absolute references, copy the details from your invoice, and then use the <Ctrl> + <Down Arrow> shortcut key to move to the end of your table of invoice details.  You must then switch to relative references and move down to the next line to paste your data, then switch back to absolute and repeat the process as many times as are needed.

 

For instructions on recording macros and using absolute and relative references, see the video and e-book lessons:

Expert Skills Lesson 9-11: Record a macro with absolute references

...and:

Expert Skills Lesson 9-15: Record a macro with relative references.

 

In order to copy just the values of your invoices and not the formulas, you can use the Paste Values option.  This is covered in the video and e-book lesson: Essential Skills Lesson 3-5: Use Paste Values and increase decrease decimal places displayed.

 

In order to extract the next invoice number to use in your invoice, I suggest using the MAX function to create a simple formula like: =MAX(Table1[Invoice Number])+1

For more on the MAX function, see: Expert Skills Lesson 3-2: Use common functions with Formula AutoComplete.

 

I have attached an example workbook with a working example:

 

Attached File  invoicemacro.xlsm   17.02KB   379 downloads

invoicemacro.png

 

Note that this is a macro-enabled workbook, so you may have some issues if you attempt to download it.  For more about macro-enabled workbooks and the issues surrounding them, see Expert Skills Lesson 9-12: Understand macro security.

 

You'll notice that I have created a 'macro button' to enable the macro to be quickly accessed.  This technique is covered in Expert Skills Lesson 9-16: Use shapes to run macros.

 

Please feel free to reply if I can be of any more assistance with this.