Budmustang

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!

 

What kind of problem?

 

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.

 

I bet he wished he'd never asked now lol

 

That's what I thought as I typed it - but felt rude not to answer!!

 

Wow! I can’t believe you need help with something as simple as that,

 

I need help too. Getting my hands on one of these..

 

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.

 

Have you tried nesting if statements?

 

I use this site when I'm looking for something in particular.

https://www.theexceladdict.com/

 

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

 

Yer Daft Bugger!

 

And they say accountants are boring...