Going back a long way - like 2007 back - you helped me with with some complicated excel functions. Are you up for a bit more help? And if so would you drop me a PM with your current email address. I'm having a mental block on something and no amount of reading help pages is helping!
Honestly, I'd struggle to explain in words, but will give it a go. I have a table that contains numeric values from 1960 through to 2020. These values will change depending upon certain drop down selections by the user - that's all fine. I have another table where the user enters employment dates so, from 1970 to 1975, does job X for Y length of time. That works out another value using vlookups from a third table and depending on other criteria calculates a value. However, someone may be doing more than one role at different times. So one line might be: 70 - 78 - Role A - Calculated Value X 75 - 78 - Role B - Calculated Value Y Then on my separate Table I have: 1968 - Limit - 1969 - Limit - 1970 - Limit - Etc, etc. I want to say for each individual year - If this line falls within the year range on the other Table, take the calculated value from that table and compare it with the limit. Where there are more than one role in a given year, aggregate them. I can't use sumproduct as the other table isn't set out as: 1960 1961 1962 And nor can it be from an end user perspective. A pivot table may do it but I still wouldn't know how to turn a range of say 70-75 into 1970, 1971, 1972 etc, etc - and as I then have to arse about refreshing the pivot every time new data goes in I don't want to do that.
Blimey I think if I was sent it at work on Monday morning I’d come up with something (probably inelegant) But I’m afraid I’m not nerdy enough to give up my weekend at my age. Sorry, thought it might have been a bit simpler.
I haven't played about in excel for years and I'm a little unsure of the ask but potentially - and as a quick workaround - could you create a temporary table with your ranges and then lookup from that? Something like... Range, Year 70 - 74, 1970 70 - 74, 1971 70 - 74, 1972 70 - 74, 1973 70 - 74, 1974 75 - 79, 1975 75 - 79, 1976 75 - 79, 1977 75 - 79, 1978 75 - 79, 1979
