• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula automatically get changed with change of month

Hi,

I have a data, wherein, monthwise information is maintained in Month and YTD. I have developed formulas for current month. But into next month I have to change the formula manually into all the sheets.

Is there any way that formula automatically gets updated

I have attached file for reference

Regards
Neeraj Kumar Agarwal
 

Attachments

  • Excel Query.xlsx
    8.4 KB · Views: 15
Hello Neeraj,

Don't know your actual data setup. This works on the sample provided.

I5:

=VLOOKUP($H5,$A$4:$E$8,MATCH(I$4,$A$4:$E$4,0),0)

J5:

=SUM(INDEX($A:$A,MATCH($H5,$A:$A,0)):INDEX($A:$E,MATCH($H5,$A:$A,0),MATCH(I$4,$A$4:$E$4,0)))
 
hi @neeraj2050 ,

J5:
Array Formula ( Press Ctrl + Shift + Enter )
=SUM(INDEX($B$5:$E$8,MATCH($H5,$A$5:$A$8,0),ROW(INDIRECT("1:"&MATCH($I$4,$B$4:$E$4,0)))))

Or
Array Formula
=SUM(OFFSET($A$4,MATCH($H6,$A$5:$A$8,0),1,1,MATCH(I$4,$B$4:$E$4,0)))
 
Using Microsoft 365 (beta or monthly) it all looks very different.
Code:
= LET(
  activeRecord, XLOOKUP(@selected, recordHdr, data),
  selectedData, XLOOKUP( @month, monthHdr, activeRecord ),
  openingData, INDEX( activeRecord, 1 ),
  YTD, openingData : selectedData,
  SUM(YTD) )
@selected and @month are relative references to the lookup values;
activeRecord is the record returned that corresponds to the selection {"Y";"A"};
selectedData is the cell from that record that corresponds to the lookup month;
openingData is the first cell in the active record;
YTD is the range from first to current;
the value returned is the sum of the cells in YTD.

ps To return the figure for the month and the year to date, the final parameter becomes,
Code:
   IF( {0,1}, SUM(YTD), selectedData )
 
Last edited:
Back
Top