#207 Excel Data from Elsewhere

Excel Data From Elsewhere

Do you ever have spreadsheets that reference data from different tabs or even from other workbooks entirely? For example, a tracking sheet that has a tab for each month of the year and a sheet up fron that aggregates all the data. Or maybe sales data from different regions that you’d like to summarize in an overview page.

From Another Sheet

To pull data from each region’s sheet into a summary page you’ll need to know how to reference cells from another worksheet. The syntax looks like this:

=SheetName!CellReference

So in our example, if we wanted the number for the East Region Qtr 1 Totals we would use

=EAST!B9

From Another File

Similarly, you can pull data from another workbook by adding the file name in brackets to the front. They syntax looks like this:

=[Filname.xls]SheetName!CellReference

=[Summary.xls]EAST!B9

Make It Easy By Pointing To It
The best part is that there is a super easy way to do this without having to remember any of that cryptic syntax or manually typing it all it. Simply enter = into the cell where you want the formula, then navigate to the cell in other sheet or file you want to reference and then hit the Enter key on your keyboard.

Here’s an example of referencing another worksheet within the same file by pointing.

[click image to see animation]

..and another one that references data from a different spreadsheet file.

[click image to see animation]

Bing. Bang. Boom! You ‘automagically’ have your formula. And we love it when that whole ‘automagic’ thing kicks in!!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s