Google-sheets – How to refer to data in a column under this month

formulasgoogle sheetsgoogle-sheets-datesgoogle-sheets-query

In the below screenshot, I'm trying to get B42 to show the value of whichever cell on row 40 is under the current month's column:

As the dates are automatically generated using a transpose(arryaformula) function, each month is actually written as DD/MM/YYYY. However, I've formatted it so only the M and Y show.

The formula in the bar was lifted from another answer on here but, understandably, it can't find today's date as each column name is actually the first of each month.

Could someone help me, please?

Best Answer

=INDIRECT(ADDRESS(40, MATCH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), C33:33, 0)+2))

Related Solutions

Google-sheets – Error using “sumif” together with “address” function

The problem is that address returns a string, so you need to turn that string into a reference.

Try this:

=sumif(indirect(address(F2,G2,2)&":"&address(ROW(B2),COLUMN(B2))),B2,indirect(address(F2,H2,2)&":"&address(ROW(C2),COLUMN(C2))))

Google Sheets – Copy Data to Another Sheet Based on Month of Date

You don't need a script: the Filter command (which is different from filters and filter views) does this easily.

Suppose your master sheet is named Master and the closing date is in column B. Then in Sheet "Jan" you would enter

=filter(Master!A:Z, month(Master!B:B) = 1)

Here A:Z are the columns of Master sheet (of course there may be more, you can use A:ZZ, etc). The condition that month of the date in column B is equal to 1 means it's January.

In the sheet named "Feb" you would put

=filter(Master!A:Z, month(Master!B:B) = 2)

and so on. The edits to Master sheet will be immediately reflected in the month sheets.

Best Answer

Related Solutions

Google-sheets – Error using “sumif” together with “address” function

Google Sheets – Copy Data to Another Sheet Based on Month of Date

Related Topic