Page 177 - Finanancial Management_2022

The intersection of the row and column selected returns The table looks slightly different (see the screenshot
the pivoted value. “Amended Example Dataset”).
Example dataset Amended example dataset

Returning to our scenario Four things have changed (only two of which are visible):
Therefore, in our situation (see the screenshot “Example 1. Filter dropdowns have been added to the first row. We don’t
Dataset”) to determine a value, we would simply use the require these, so these may be removed by highlighting the
generic formula table and clicking on the Filter button in the Sort & Filter
section of the Data tab on the Ribbon (ALT+A+T).
=INDEX(Table_Data, MATCH(Item, Item_List, 0), 2. The top left-hand cell has had text added, which defaults
MATCH(Month, Month_List, 0)) to the highly imaginative “Column1”. This is because all
columns (fields) in a table must be named and contain text,
But what if the number of rows and columns were to not formulas. This must not be deleted, but it will remain
extend? Table_Data (the array of input cell values), Item_List invisible in my example due to the cell formatting.
(the vertical list of items in grey), and Month_List (the 3. Alternate rows are shaded differently. Again, this is not
horizontal list of months in grey) would all be of variable size. noticeable, as I have already included my own formatting,
It’s not just the ranges that need extending; it’s the idea, too. which overwrites this formatting. If my formatting were
Whenever we have extendable ranges, we should use a to be removed (eg, change the cell style to “Normal”, ie,
Table. I highlight the table and go to Insert -> Table (CTRL+T) Home -> Styles -> Normal), this shading would become
— see the screenshot “Insert Table”. apparent.
4. In the bottom right-hand corner, a green, irregular hexagon
Insert Table is visible, which highlights the fact the table may be
extended both to the right and downwards; ie, we have a
range that may be extended.
I now name this table “Data” (simply name it in the Table
Name: section of the Properties group on the Table Design
tab of the Ribbon). Then, on a separate sheet I will call “Lookup
Data”, I have created two formulas (see the screenshot “Lookup
Data”).
Lookup data
This calls the Create Table dialog (see the screenshot,
“Create Table Dialog Box”).
Create Table dialog box

Ensuring you have checked “My table has headers”, our
table is converted into a Table.

FM-MAGAZINE.COM October 2022 I FM MAGAZINE I 19