CODEWITHSUNDEEP
excelexcel-formula
user466534
user466534

Reputation:

Use a reference from one workbook in a formula of a different workbook

Suppose that in one excel sheet, I have following data:

Month           Activity                                Expanes
January         Office Renovation                       8200
                Purchasing  furniture                   2200
                Purchasing  computer                    3830
                Contracts                               850
                Salary                                  2630
                sum                                     17710

In another excel file I have:

enter image description here

I want to use a formula to compare the budget for January to the expenses for the same month, using the IF function. Can I reference data from one excel file in the other? I have tried, but I can't choose both at the same time, because another excel file is minimized (closed window).

Upvotes: 0

Views: 597

Answers (1)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15641

The answer is Yes, you can. How?

  1. Start with only one window of each of the two workbooks that you need open, i.e., close all other Excel files. Do View -> Arrange All -> Vertical. this leaves a view as shown in the fig. Activate the cell that you want to hold the formula (C3 in Sheet1 of wkbk_2.xlsx in the figure). Start typing the formula just as you would normally (=IF(B3> in the fig.). Without typing , click on cell C2 in Sheet1 of wkbk_1.xlsx to activate wkbk_1.xlsx, and click again on the cell to add the reference that you need in the formula (=IF(B3>[wkbk_1.xlsx]Sheet1!$C$2). Keep typing your formula as you would normally.

    enter image description here

    If instead of having the windows redistributed you have both maximized, when you have entered =IF(B3> go to the taskbar in Windows, select the window for wkbk_1.xlsx, and select C2.

  2. As described in the comments to your question. What they suggest is that you manually enter what the method described above does for you.

Upvotes: 1

Related Questions