Forum Discussion
Excel Not Recognizing Reference Pattern
Good morning!
Looking to see what the best/easiest option is as I've been trying to use an offset and lookup formula to no avail.
In columns A & B, I have a set of tables with dates by week and a total for every week to track hours. In column D & E, I'm attempting to reference the total cells in columns a & b, titled Week of Dec 9-15 for instance, and the summed value respectively.
My current pattern results in:
D4 = $A7
D5= $A16
D6= $A25
So on and so forth in intervals of 9 cells down each time. To avoid having to manually write the remaining 49 cells for the remainder of the weeks I'm building, I attempted to build the pattern and use autofill to do so and build an offset formula to reference the Total/"week of" cells in column a to no avail. I feel as if this should be an easy fix and I'm just overlooking the solution. Any help would be appreciated. Thanks!
I may need to see a sample sheet but this is the idea.
Dates being Col A:
=LET(wrapped, WRAPROWS(dates, 9, 0), TAKE(wrapped, , 1))This one didn't quite work I believe due to not trying to pull the dates themselves, but rather a text value of "Week Total (Sat, Dec 9 - Fri, Dec, 15)" that is in the Total Row of a table with the dates themselves. I'm trying to pull the text from the total row and the summed value into consecutive rows in columns D & E through references versus having to manually assign the references or copy paste the text. However, I'm going to try to post a sample, but it may be difficult. Thank you!
Without seeing your file I would recommend to re-design it as it sounds like you are capturing daily information and then added subtotals by week. Now you want to extract those sub-totals in consecutive rows.
Try this in D4 and copy down:
=INDEX(A:A,(ROW()-ROW($D$3))*9-2)However, much better to get rid of the sub-totals and use Excel's built-in functions to summarise the daily entries by week, month, quarter, year. Whatever. Share a file if you think it's feasible in your situation.
- That sounds like exactly what I'm trying to do, but my origination point was a bit off. I'm going to try to post the file here directly. Thank you!
- I believe this is the problem I am searching answer for. Could you please assist me with this?
