This is quite hard to explain but hopefully the document will help!
I have a s/sheet looking at a five year replacement plan for their PCs.
I have a list of Locations (Labs) in a uni that contains the number of machines, how much it will cost to replace and a year that (1-5).
At the moment they were populated manually. But if they decide they want to change, for example Lab B from the first year to the 3rd, it then needs to be done manually. What I'd like to be able to do, is, if the year is changed in column E, then the boxes / fields to the right get updated. I know I can do this with multiple pivot tables, but know that it could also be done with formulas.
In G5 for Year 1, I have used the Index and Match functions to return Lab B. I'm guessing I might need OFFSET, (which I don't know much about as yet) but before I went ahead and researched it all, thought I would ask.
Current formula (in G5) here is:
Solved by I. U. in 23 mins