Monthly Archives: July 2019

Lookup Images in Microsoft Excel

This video explains how to use the Indirect function and the DGet function to lookup images and data in an Excel sheet. The tutorial covers how to use named cells and ranges, also how to use data validation lists.

Training courses are available at https://www.itseasy.co.uk/courses/excel-training/ courses can be delivery onsite or at our site in Gateshead. Online courses are available at http://www.itseasytraining.co.uk We have laptop which are available to use at your site and convenience. Call or email steve@itseasy.co.uk Business Mobile 07966503354

Lookup Images

The process in this video starts off explaining named cells and ranges, each photo sits inside a named cell. The tutor explains how to create a data validation list that displays the names of the photos. The indirect function is added in the name manager to reference the data validation box.

Indirect

The Indirect function is added to the name manager, by selecting new and creating a name. The Indirect function returns data from a referenced cell, in this case the image.

Images

Any of the images in the data set can be copied and placed in the relevant location on the lookup sheet. With the image selected the tutor explains that the formula must be typed using the Indirect name, (Flag) in the video example.

DGet

The DGet function is used to bring the remaining data across using the Data Validation list as its trigger. The DGet function works in a similar way to a Lookup working with a database of information.

I think that the best uses of these functions is in a catalogue of stock or parts. The parts or stock could have a photo which would be returned as and when required along with all the other data normally associated with lookups. Check out our YouTube channel at http://www.itseasytraining1st for more videos like this. Feel free to ask questions or ask for more videos.

Database Functions in Microsoft Excel

This video explains how to use Database Functions in Microsoft Excel.
The tutorial covers the DSum, DMax, DMin, DAverage and the DGet functions. Each example uses named ranges and the tutor explains how to create a two way lookup using the DGet function.
Some of the examples use the date validation list option.

Classroom based training courses are available at https://www.itseasy.co.uk/courses/excel-training/ Online training courses are available at http://www.itseasytraining.co.uk

Database Functions

To get the best out of Database functions you need to create a database of records and then a selection area where the column headings match the database headings.

Set the functions to look at the selection area to calculate the required data. Basically the Database functions use the set criteria to match your selection from a database. It is easy to set up multiple criteria and may be easier to use than the very popular Vlookup or Hlookup. The functions are smaller than comparable SumIFs and CountIFs therefore use less memory in large spreadsheets.

DGet function

The DGet function enables users to create a two way lookup similar to the Match and Index functions. The DGet function required a Database and a field followed by a selection area. If you use named ranges and cells the formula can be copied across without worrying about using dollar signs to lock cells and ranges.

Visio brainstorming diagram

This video explains how to create a Visio brainstorming diagram and how to create a diagram using charts and marketing diagrams. The tutorial also explains how to use the developer tab to protect shapes and shape elements. The final section covers how to combine and fragment shapes and add the features to the quick access toolbar. Online courses at http://www.itseasytraining.co.uk

Details about Microsoft Visio classroom based courses can be found at https://www.itseasy.co.uk/courses/visio-training/ all courses are delivered either at our site in Gateshead or your site if required. We can provide ten laptops and all the software versions anywhere in the UK. Prices can be found at out prices section and can be tailored for multiple groups. If you don’t ask you don’t get.

Visio Brainstorming

This video explains how to create a Visio brainstorming diagram and how to add topics which will automatically update the legend when added.

Charts and Graphs

This part of the video explains how to create each element of a bar chart, a pie chart and how they work. Adding the marketing shapes stencil also shows how diagrams are edited and modified.

Protection

The last section of the video covers why some shapes are protected and where the protection can be found. Using the combine feature with fragment utilises the add to the quick access toolbar process.

Visio linked data

This video explains how to create a flowchart using the Visio linked data wizard, pulling the data from Microsoft Excel into a Visio flowchart diagram. The video also explains how to import data from Microsoft Project in to a Visio timeline diagram. The tutorial covers how to edit data graphics in Microsoft Visio and how to update linked data using the refresh option.

Contact

The course details for the topics covered in this video can be found at https://www.itseasy.co.uk/courses/visio-training/ online courses can be found at http://www.itseasytraining.co.uk

Importing data

The process to import data from other programs is quite simple in Microsoft Visio, you can use the import wizard or select the link option on the timeline tab for Microsoft Project for example. The video uses the Timeline diagram and selects data from a Microsoft project file. The resulting Timeline is a bit cluttered and extra effort is required to sort out the labelling and positioning of the tasks however, the end product looks great.

Visio Linked Data

If you have used the Visio linked data option, the process as shown in the video, proceed as follows: Select the shape type you require and then select the linked field dragging it into position on the diagram. You can update data in the source files which will be refreshed when selected in the Visio diagram. You can add additional shapes and connections to enhance your diagram that don’t have to be linked to a source file.

Data Graphics

The video explains how to edit linked data in the data graphics feature. The graphics can be moved deleted or added to through the wizard. If you remove the data graphic you will still be able to type in the shape boxes and format them as normal. The linked data will appear in the Shape Data window if it is active.

Visio Reports

This video explains how to run and create reports in Microsoft Visio and send them to Microsoft Excel. The tutorial demonstrates how to create shape data and add the new shape data field to a shape report. Once the new shape data is added, the field appears in the wizard ready for selection, once all the required fields are selected the report is generated by following the wizard through to the end. The video also covers how to add subtotals to your report, to total, either number or currency fields. Finally, in the last section, how to edit the Excel output is explained.

Overview

The course details for Microsoft Visio are at https://www.itseasy.co.uk/courses/visio-training/ online courses are available at http://www.itseasytraining.co.uk

Visio Reports

All Visio shapes have shape data fields which are either preset or can be added by the user. Once the fields are used to store data the data can only be sent one shape at a time. This can be slightly irritating. To solve this issue Microsoft Visio provide a report feature.

Shape Reports

There are several preset report but it is very easy to create your own report. Simply add all the filed required, including new shape data fields and then follow the wizard through. There are several report options but i tend to send my report in to an Excel file, which can then be formatted in Excel.

How to create a Floor Plan using Microsoft Visio

This video explains how you can use Microsoft Visio to create a floor plan to convert your garage or other areas of your house. The tutorial covers how to set outside walls and how to create windows and set the size, how to add doors and flip them in to the correct position. The video also explains how to create a layer and lock the contents of that layer. The search feature is explained and adding new shapes to your own stencil is also covered. Training at www.itseasy.co.uk Online courses at www.itseasytraining.co.uk

The details shown in the video are covered on the Visio overview course, details of the topics covered on the course can be found at https://www.itseasy.co.uk/courses/visio-training/ online courses can be found at http://www.itseasytraining.co.uk

Floor Plan

This video explains how to use Microsoft Visio to plan a conversion in your house. The video shows how I used Microsoft Visio to plan for my own garage conversion and some of the mistakes I made. The number one error was not measuring the furniture and walls correctly. Because of my mistakes I was left with a wall with no door, and a section of the house with no lights.

The personal stencil

The video demonstrates the use of a personal stencil. I always recommend to my students that they should create a personal stencil that they can add shapes to, which in the end will speed up development of diagrams.

The stencils will be by diagram groups, for example a floor plan shape may not work in an organisational diagram, the scale may be too small to fit. I try to create a stencil for each diagram type I use. You can drag and drop shapes on to your stencil and then double click the icon to rename it. You must save the stencil and note where it is saved.