Formula to extract data based on Month & Year

[howard]

I have source data on Sheet1 for Jul 2017 (cell K1) on a particular workbook which is imported-see sample Data Below


I have July 2017 on another Workbook (K4) and would like to extract the figures for each dept pertaining to the month Applicable


I would like a formula to extract the data for the applicable month in J1 for e.g. data in row 2 applicable for Jul 2017 which in this example is-56,986, row3 which is -3,606 etc


It would be appreciated if someone can assist me

Book1
	I	J
1		BR1 NETT (PROFIT)/LOSS SUMMARY FOR Jul 2017
2	Dept1	-56,986
3	Dept2	-3,606
4	Dept3	-16,004
5	Dept4	-30,869
6	Dept5	22,689
7
Sheet1

 

[howard]

I have also posted on

Formula to extract data based on Month & Year

 

[piotrekp1]

I don't understand, you want to have a function that after receiving the month tells you some data from other column based on the month it was given, right?

 

[howard]

I want a formula to extract the data from the source workbook (Sheet1) where the month and year are the same in both workbooks


For if the source Workbook contains Jul 2017 in say J1, then I want to extract the values from the source workbook that pertain to Jul 2017 in the destination workbook

 

[howard]

I have shown what the end result should look like. I have linked the cells in the source data.

I am look for a formula that extract the values in the source workbook sheet1 from J2:J6 pertaining to the same month

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K
4		Oct 2016	Nov 2016	Dec 2016	Jan 2017	Feb 2017	Mar 2017	Apr 2017	May 2017	Jun 2017	Jul 2017
5											-56,986
6											-3,606
7											-16,004
8											-30,869
9											22,689
Sheet1

Cell Formulas
Range	Formula
C4	=+B4+31

 

[howard]

I have come up with a solution, but feel it can be improved upon. It would be appreciated if there is a better solution if someone come kindly assist me

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K
4		Oct-2016	Nov-2016	Dec-2016	Jan-2017	Feb-2017	Mar-2017	Apr-2017	May-2017	Jun-2017	Jul-2017
5											-56,986
6											-3,606
7											-16,004
8											-30,869
9											22,689
10
11
12
13
14
15
Sheet1

Cell Formulas
Range	Formula
C4	=+B4+31
D4	=+C4+31
E4	=+D4+31
F4	=+E4+31
G4	=+F4+31
H4	=+G4+31
I4	=+H4+31
J4	=+I4+31
K5	=IF($K$4='[Branch Profits.xlsx]Sheet1'!$I$1,OFFSET('[Branch Profits.xlsx]Sheet1'!$J$1,ROW(1:1),0),0)