Help with formula to pull data to a second sheet - depending on the current Month and Year

Dougie1

Board Regular
Joined
Jul 27, 2007
Messages
212
Using Excel 2016

I have a sheet (Sheet1) that contains monthly values in the columns - e.g. column G has the September 2017 values, column H has the October 2017 values etc. all the way through to the August 2018 values in column R.

Each column names the month and year in row 3 and then there is data in each column in rows 5-11.

What I want to do is pull the relevant value through to cells on Sheet2 depending on the current month and year e.g. Currently, I want to pull the values through for January 2018.

So what I am trying to do is set formulas on Sheet2, in cells F3 to F8 that will identify the current month and year – then find this month and year in row 3 of Sheet1 and pull the value from row 5 to cell F3 on Sheet2, pull the value from row 6 to F4 on Sheet2 and so on down to F8.

Can anyone help please?

TIA

Dougie
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Hi, what exactly is on row 3 of sheet1 - is it text like "September 2017" for example - or is it dates formatted to look like text? If dates, are they the first of the month?
 
Upvote 0
.......sorry - should have said - row 3 contains dates e.g. 01/09/2017, 01/10/2017 formatted to look like Sep-17, Oct-17 etc
 
Upvote 0
OK - you can try this in F3 copied down.

=INDEX(Sheet1!$G$5:$R$11,ROWS(F$3:F3),MATCH(EOMONTH(TODAY(),-1)+1,Sheet1!$G$3:$R$3,0))
 
Upvote 0
thanx FormR - didn't work though - when I hit return - the formula doesn't seem to calculate - just sits in the cell as the text entered - probably user error though!
 
Upvote 0
Sheet1 was just the name I used for example though - the actual sheet name is NLN if that helps!
 
Upvote 0
the formula doesn't seem to calculate - just sits in the cell as the text entered

Hi, format the cell in which you are going to place the formula as general or number and then re-enter it.
 
Upvote 0
Excellent! - Thank you so much.

One final question (honest) -

How can I enter the formula so it also works in cells F12 and F26 of Sheet2 as well?
 
Last edited:
Upvote 0
apologies - using the same methodology - I want to pull the value from row 11 of Sheet1 into cell F12 of Sheet2 AND I want to pull the value from row 19 of Sheet1 into cell F26 of Sheet2

TIA

Dougie
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top