How to Reference Cell or Pull Data from Another Workbook in Excel?
In this advanced excel tutorial we are about to tell excel to reference another workbook without opening. Also we are about to reference cell from another workbook dynamically. I am your trainer kazi and in this tutorial i am using Microsoft Excel 2019 for the demonstration. Lets get started:
To reference a cell from another workbook

Technique 1:

Referencing a single cell in another workbook:

Referencing a sheet in from a different workbook requires writing the workbook name inside an Squire Bracket then sheet name followed by an exclamatory sign (!) after that you'll have to write the cell no/name. All of this begins with a = sign.

Example: =[January.xlsx]Sheet2!A1 This will pull the value from cell A1 within Sheet 2 from the workbook named January.xlsx and place the data where you've written the formula.

Referencing a Single cell from a different workbook which has sheet name with spaces and different characters:

Suppose your worksheet name has spaces or different characters then you'll have to add single quote ( ' ) before and after your workbook and worksheet name and everything will be the same.

Example: ='[Customer Database.xlsx]Customer Name'!A1 This will pull the value from cell A1 within Sheet Customer Name inside customer database workbook and place the data where you've written the formula.

Cross Sheet Calculation:

Suppose you want to sum values from Sheet 2 and Place the results in Sheet 1. You'll have to write =Sum([1.xlsx]Sheet2!A1:A5) Now this formula will sum up values from cell A1 to Cell A5 from sheet 2 inside workbook 1.xlsx and place the results where you put the formula. Now if you have spaces within your worksheet name you'll have to add Single Quote (' ') to your workbook and worksheet name and the rest remains the same.

Technique 2:

Referencing a Single Cell:

Referencing a sheet requires writing = and the Click on the workbook name and the worksheet name on the sheet tab and after that click on the cell no and press enter.

Example: Write = and click on the Sheet 2 and then click on the cell A1. This will pull the value from cell A1 within Sheet 2 and place the data where you've written the formula.

Cross workbook calculation:

Suppose you want to sum values from Sheet 2 from a different workbook and Place the results in Sheet 1. You'll have to write =Sum( now open that workbook and click on the Sheet 2 from the sheet tab and select cell A1 to Cell A5 now close parenthesis and press enter. Now this formula will sum up values from cell A1 to Cell A5 from sheet 2 from that workbook and place the results where you put the formula.

This is how you can pull data from different workbooks or do cross sheet calculations.
How do I pull data from one excel workbook to another without opening?
Very simple. Just add the file path before everything else. For Example:
Workbook name= 1.xlsx
Worksheet name= Sheet1
Cell no A5
File Path= D:\
So if I pull that data to master workbook we will have to write this
=’D:\[1.xlsx]Sheet1’!A5
Done this is how you pull data from one excel workbook to another or reference cell from another workbook dynamically


#ReferenceCell #DifferentWorkbooks

Thanks for watching.
-------------------------------------------------------------------------------------------------------------
Support the channel with as low as $5
[ Ссылка ]
-------------------------------------------------------------------------------------------------------------
Please subscribe to #excel10tutorial
[ Ссылка ]

Here goes the most recent video of the channel: [ Ссылка ]

Playlists:
Advance Excel Tutorial: [ Ссылка ]
Excel Tutorial for Beginners: [ Ссылка ]
Excel Case: [ Ссылка ]
Excel VBA Programming Course: [ Ссылка ]

Social media:
Facebook: [ Ссылка ]
Twitter: [ Ссылка ]
Blogger: [ Ссылка ]
Tumblr: [ Ссылка ]
Instagram: [ Ссылка ]
Hubpages: [ Ссылка ]

свернуть