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

[Dougie1]

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

 

[FormR]

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

Amend the index() range and alter the row counter to start on F12.

=INDEX(Sheet1!$G$11:$R$25,ROWS(F$12:F12),MATCH(EOMONTH(TODAY(),-1)+1,Sheet1!$G$3:$R$3,0))

Take a look at the built in help for the various formula at play to try and get an understanding of what happening.

 

[Dougie1]

hmmm - I thought I did understand it before I asked again

For the F12 formula - I just changed the row counter to be F$12:F12 and as row 11 was covered in the original index range:

=INDEX(Sheet1!$G$5:$R$11,ROWS(F$12:F12),MATCH(EOMONTH(TODAY(),-1)+1,Sheet1!$G$3:$R$3,0))

- I thought I was good to go - but it didn't like it!

Thanks for all your help FormR

 

[Dougie1]

arrgghhhh!!

so why does this not work in cell F26 of Sheet2 (which is really called NLN):

=INDEX(NLN!$G$11:$R$25,ROWS(F$26:F26),MATCH(EOMONTH(TODAY(),-1)+1,NLN!$G$3:$R$3,0))

 

[Dougie1]

it's ok - fixed it now

the Index cell reference has to start with the row I need - so by changing $G$11 to $G$19 - I am now in business!

"every day is a school day" - especially if you are me.

thanx again for all ur invaluable help - very much appreciated.

Dougie

 

[Dougie1]

ah ha - but now I have another issue!

how can I amend the formulae so that they look at the current month and year e.g. but pull back the values for the previous month instead e.g. currently, it is January 2018 and all through this month I will now want to pull the values through for December 2017 then in February 2018 and all through that month I will want to pull the values through for January 2018 and so on.

TIA

Dougie

 

[FormR]

how can I amend the formulae so that they look at the current month and year e.g. but pull back the values for the previous month

Hi, you would alter this part, for example:

...EOMONTH(TODAY(),-2)+1...

 

[Dougie1]

spot on! - thank you kind sir

all the best,

D