Unicom Computers

Watch this space for all our insider tips and tricks!

Microsoft Excel Tips

The INDIRECT function in Excel prevents #REF to display if the original data for the formula was deleted and replaced with new data.  This is especially handy when working with macros where data is changed before the macro is executed.

If you have an extra sheet linking to the sheet with the data that will be changing, you can also insert extra calculations on this extra sheet.

The cell reference in the example, must be referenced with inverted commas, i.e. “B2”

The CELL function gets information about a cell.  In this case we need the row of the cell , to fill copy the formula down, i.e. = Cell(“row”,C2) - resulting in 2

Example

=IF($A2="","",INDIRECT("Turnstile!C"&CELL("row",C2)))      The C2 could be any cell in the row.  It picks up the row number so that the formula can be filled down.