Find month end and locate 2 values

[aleksjones87]

Hi There,

Would you please advise on the below, any help would be greatly appreciated.
I receive a spreadsheet with values each month, of which I extract only MTD and YTD performance for numerous entities. This question is just for 1 entity as a sample. I was able to make the VBA work by identifying the cells by exact location, but I am concerned that if one roll moves, then I am screwed and I definitely do not want to be screwed. So now I am trying to do it the hard way which is by making excel realize that if today is 1/9/15, then end of month is 1/31/15. Look for that value and locate MTD and YTD.
Please see the following for clarification,
https://www.dropbox.com/s/wb98psdje2qas8g/Excel Question.jpg?dl=0

Thank you in advance. I appreciate anyone taking the time to look into this.

 

[taurean]

To get the last date of month you can use in B3:
=EOMONTH(B2,0)
Then Lookup function can be used to locate data
for MTD
=VLOOKUP(B3,B16:L1000,11,0)
And for YTD
=VLOOKUP(B3,B16:O1000,14,0)

 

[aleksjones87]

Hi Shrivallabha,

It is not just one entity, but a list of them. I am trying to do it through VBA. Correct me if I am wrong, but your suggestion is not VBA.
I wrote the below but it is not good enough since just like vlookup, excel would not understand if the report is pasted one row to the right. Then the numbers will be off. I am trying to avoid that.

Sheets("AA").Select
Windows("ReturnBook.xlsm").Activate
Range("B7").Select
ActiveCell.FormulaR1C1 = "='[Current Month Performance.xlsx]AA'!R10C12"
Range("C7").Select
ActiveCell.FormulaR1C1 = "='[Current Month Performance.xlsx]AA'!R10C15"

*Current Month Performance is the spreadsheet I receive every month.

Thanks again.