0

I have a spreadsheet that I'm using to track weekly performance metrics and want to create a summary page that only shows me the value of each metric for the current week.

The worksheet is currently set up with dates across the top in the first row and metric names down the side in column B.

Screenshot of current spreadsheet layout

My summary page is set up with metric names in column A and current value in column B.

Screenshot of summary page

I'm looking for a formula that will insert the present value of each metric based on the current date into the summary page. The dates in row 1 indicate the last date of the reporting period (Saturdays).

The name of the sheet where the data is being pulled from is "KPIs" and the full range of the data is C2:BB80 (C1:BB1 is the dates and A and B are metric categories and names)

Improve this question
2
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    May 23 at 4:39
  • Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely.
    – doubleunary
    May 23 at 6:15

1 Answer 1

Reset to default
0

Use filter() and weeknum(), like this:

=filter(KPIs!C2:BB, weeknum(KPIs!C1:BB1) = weeknum(today()))

To get KPI names as well, use an { array expression }, like this:

={ KPIs!B2:B, filter(KPIs!C2:BB, weeknum(KPIs!C1:BB1) = weeknum(today())) }

See filter() and weeknum().

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.