Log In
Register
Home
New Member
December 16, 2022
Offline
Hi Everyone
Hope it's no repost 😉
I struggle with the offset’s value error. I tried to use 3 index matches in offset function for: reference, rows, cols.
My aim is to find the range of entries depending from specific task (row) and week number (column) in a particular year (as the offset's reference)
Separately all the matches still work correctly, but when all are combined an error is given. I’ve tried 2 alternative syntaxes: one where ranges are in the entire column and rows, and 2nd where MATCH ranges are limited to a year range. I’ve also tried using Sum at the beginning of the formula syntax or array {}- but still no joy.
Where is the problem?
Many thanks
Ps. work on Office 365 for Windows
July 16, 2010
Offline
Hi and welcome to our forum!
Can you please upload your file or an example file so we can see what you're trying to do in the context of the worksheet?
Mynda
New Member
December 16, 2022
Offline
Apologise Mynda.
Please find attached
July 16, 2010
Offline
Thanks for sharing your file. The issue is that your data is not in a suitable layout to lookup the results you want. Firstly, you have the years in merged cells. When you merge a cell the cell reference is still the first cell in the merged range. It can only identify the year for one week in each period.
You should store your data in a tabular layout if you want to use Excel's tools the way they were designed.
Personally, I would use Power Query to unpivot the data, and then summarise it in a PivotTable.
See example attached. Sheet 'Tabular Layout' contains the unpivoted data from Power Query (in Table1) and two PivotTables (in column J).
I hope that points you in the right direction.
Mynda
1 Guest(s)
