top of page

EXCEL SHORTCUTS

Indirect function

Allows for dragging a cell across a worksheet horizontally

Example:

=INDIRECT("ProductX!D"&COLUMN(I10)-3)

​

Syntax

INDIRECT(ref_text, [a1])

The INDIRECT function syntax has the following arguments:

  • Ref_text    Required. A reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.

    • If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

      Note    External references are not supported in Excel Web App.

    • If ref_text refers to a cell range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD), INDIRECT returns a #REF! error.

      Note    This behavior is different from Excel versions earlier than Microsoft Office Excel 2007, which ignore the exceeded limit and return a value.

  • A1    Optional. A logical value that specifies what type of reference is contained in the cell ref_text.

    • If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.

    • If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.

Reference

​

Create a drop-down list

  1. Select the cells that you want to contain the lists.

  2. On the ribbon, click DATA > Data Validation.

  3. In the dialog, set Allow to List.

  4. Click in Source, type the text or numbers (separated by commas, for a comma-delimited list) that you want in your drop-down list, and click OK.

Excel

Index Match

The index Match method is a powerful function Excel offers. It allows the user to find a particular instance of a column in another table. It is preferable over the V-Look up since it does not limit the user to the first row.

Syntax =index(Array you want to display, Match(Item being looked for, Array where that item is found, 0))

Great resource below!

https://www.deskbright.com/excel/using-index-match/

​

INDEX MATCH SCREENSHOT.png
INDEX MATCH SCREENSHOT 2.png

Agile Methodology

Agile is a form of technology development that has grown in popularity in recent years. It was created by a group of technology experts/enthusiast with the goal of improving the delivery of technology. With Agile, it is easier to deliver what your client desires and fix your course in case you are developing the wrong tech. Agile is a mindset, as its name implies, it quick and fluid. Agile, as opposed to Waterfall, allows for quicker and more efficient delivery of products. Through agile you have deliverables called epics, that can be broken down to features and then further broken down to stories. A story is typically assigned to one or two individuals that need to deliver the story in about a sprint (two weeks typically). 

Key concepts:

     Epic

     Feature

     Story

     Sprint

     Scrum

     Waterfall

​

​

​

 

​

​

​

SQL (Structured Query Language)

What is SQL? SQL is a database made up of various tables that relate to each other. For example. Imagine a sports league. A sports league has teams, players, stadiums, and a point system amongst various other aspects. To keep organized one could create a set of tables to differentiate the former. One table for just teams, their names, city, state, stadium name, and capacity. Another table could include the team and its players and coach. Another table could hold information on the stadium, capacity, location, etc. Lastly, another table could be used for information on players, the number of assists, goals, games played etc. These tables can then be linked together creating a relation database. Players relate to teams, teams relate to the stadium table and so on.

Now queries is the way we can get that information. We can find all the players with more than 25 goals, or stadiums with at least 55,000 seating capacity. There is a particular syntax to be used with queries to get the information you want.

Joins – A join is the merging of two tables to find. In SQL there are several different types of Joins these are (Left joins, inner joins, right joins, self-join, full join).

The most common join is the left join

bottom of page