Probably need to use OFFSET but am not sure

[Ally72]

Hi

I had some answers to a post a couple of weeks ago which were amazing, and have solved that problem. But the person I was doing it for has asked for one step further.

Old post is here

(I still can't figure out how to do the attachment thing - have tried all of them - might be because I'm on a university network)? Hope the pastes work okay.

I have a list of labs, with the appropriate information about the number of machines, when last updated etc. The Year column is the number of years in the future that the machines are potentially going to be replaced.
[TABLE="width: 578"]
<tbody>[TR]
[TD]Location[/TD]
[TD]# Machines[/TD]
[TD]Cost[/TD]
[TD]Purchase Date[/TD]
[TD]Age[/TD]
[TD]Year[/TD]
[TD]Age at Replacement[/TD]
[/TR]
[TR]
[TD]Lab A[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]£27,500.00[/TD]
[TD="align: right"]28/07/2016[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Lab B[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]£19,800.00[/TD]
[TD="align: right"]08/01/2014[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Lab C[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]£45,100.00[/TD]
[TD="align: right"]31/07/2013[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Lab D[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]£19,800.00[/TD]
[TD="align: right"]31/07/2015[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Lab E[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]£19,800.00[/TD]
[TD="align: right"]31/07/2015[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Lab F[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]£84,700.00[/TD]
[TD="align: right"]28/07/2016[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Lab G[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]£29,700.00[/TD]
[TD="align: right"]01/07/2014[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Lab H[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]£22,000.00[/TD]
[TD="align: right"]31/07/2014[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Lab I[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]£19,800.00[/TD]
[TD="align: right"]31/07/2015[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Lab J[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]£116,600.00[/TD]
[TD="align: right"]29/03/2017[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Lab K[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]£41,800.00[/TD]
[TD="align: right"]09/01/2014[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Lab M[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]£60,500.00[/TD]
[TD="align: right"]01/07/2014[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Lab N[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]£44,000.00[/TD]
[TD="align: right"]29/07/2016[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Lab O[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]£27,500.00[/TD]
[TD="align: right"]18/05/2017[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Lab P[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]£18,700.00[/TD]
[TD="align: right"]30/07/2013[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Lab R[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]£13,200.00[/TD]
[TD="align: right"]30/07/2013[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Lab S[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]£44,000.00[/TD]
[TD="align: right"]31/07/2015[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


We now would like to show in another table, the lab and the year that it was updated with machines - which I have worked out (with this formula in Lab A / 2013) copied across and down:
{=IFNA(INDEX($B$3:$E$56,MATCH($K6&L$5,$B$3:$B$56&YEAR($E$3:$E$56),0),2),"")}

[TABLE="width: 475"]
<tbody>[TR]
[TD][/TD]
[TD]2013[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[/TR]
[TR]
[TD]Lab A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lab B[/TD]
[TD][/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lab C[/TD]
[TD]41[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lab D[/TD]
[TD][/TD]
[TD][/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lab E[/TD]
[TD][/TD]
[TD][/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lab F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]77[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lab G[/TD]
[TD][/TD]
[TD]27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lab H[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lab I[/TD]
[TD][/TD]
[TD][/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lab J[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]106[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lab K[/TD]
[TD][/TD]
[TD]38[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lab M[/TD]
[TD][/TD]
[TD]55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lab N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lab O[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lab P[/TD]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lab Q[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lab R[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lab S[/TD]
[TD][/TD]
[TD][/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


But now, we'd like the lookup to look at the column Year, and from the column 2018 onwards, put how many years from this year that replacements are due, ie, Lab A is currently, 2016, and its "no of years" to be replaced is 5, we would like it to then appear again in 2021 and so on. I hope that all makes sense.

I know I probably need to use OFFSET but am quite bewildered about how, especially with quite a nested formula already.

I'd be grateful for any help at all.

Many thanks

Ally

 

[Norie]

Ally

So you would want 25 to appear under 2021 for Lab A, 18 under 2015 for Lab B and so on?

 

[Ally72]

Ally

So you would want 25 to appear under 2021 for Lab A, 18 under 2015 for Lab B and so on?

Hi Norie - thanks for replying.

Almost - we're looking at replacements from this year, so Lab B would be 18 under 2018 (anything that's 1 year would be 2018, 2 years, 2019, 3 years 2020). So even though Lab B and C were last replaced in different years, because they're both replace in 1 year, they would both be 2018. Then if the first table was updated, ie, Lab C was decided not to be updated for 2 years, then that would then jump to 2019.

 

[Ally72]

I'm guessing I need to use VBA?

 

[Mike Szczesny]

Can you use something like this. I use a very small sample. Assume A1:D4 contains data table (including headers). Assume B7 is cell in result table below 2013. The formula that I use is [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =IF(IF($A$2:$A$3=$A$7,IF(YEAR($D2)=B$6,$B2)),$B2," "). Make sure about your $'s in formulas. Copy down.[/TD]
[/TR]
</tbody>[/TABLE]





[TABLE="width: 534"]
<colgroup><col span="3"><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Location[/TD]
[TD]Number[/TD]
[TD]Cost[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27500[/TD]
[TD="align: right"]03/04/16[/TD]
[TD][/TD]
[TD="align: right"]2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19800[/TD]
[TD="align: right"]07/08/13[/TD]
[TD][/TD]
[TD="align: right"]2013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]09/06/15[/TD]
[TD][/TD]
[TD="align: right"]2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Ally72]

Thanks for your reply Mike. This is so very nearly there. I'm trying to attach (if it works) an image of what it looks like with the formula and what I'd like it to be. The formula is adding the number of years onto the year it was originally last replaced, but I need it to be from now (2017), therefore if it's one year, then it's 2018, 2 years = 2019 etc etc.

Edit: I still can't upload images, so I've just done a temporary invisible page on my website which I'll delete afterwards:
Temp

Many thanks

Ally

 

[Frank_Excel]

Hi Ally72,

Please check if the following formula works for you.

=IF((YEAR(VLOOKUP($J2;$A$2:$D$18;4;0))+$G2)=K$1;INDEX($B$2:$B$18;MATCH($J$2:$J$18;$A$2:$A$18;0));"")

 

[Ally72]

Thanks Frank. It's not working quite yet - I'm still trying to translate the cell references to mine. Am going home for the day now, but will have another look at it in the next few days shortly and see how I go.

 

[Ally72]

Right. I realised after speaking to the person who wants this, that we actually want it going from 2016 (so 1 year replacement = 2017 and so on) - apart from Lab J which is on a 6 year cycle and confusing the whole issue - so for now I'll leave that one out.

With a mixture of all your help above, I have got a formula that works to a degree, but only if the Labs are in order, and when I add all the rest of the information (those that aren't Labs that will need to go onto another table), it all goes to pot.

I've updated the images on my website: Temp

and my formulas are here:

O4:
=IF($A$4:$A$20=$I4,IF($M$3+$F$4:$F$20=O$3,VLOOKUP($I4,$A$4:$B$20,2,TRUE),""))
- copied across and down

U4 and V4 are just crosschecks for me to check the returns in the preceding data (which are fine until I add more rooms)
=VLOOKUP(I4,$A$4:$F$20,6,TRUE)
=U4+$M$3 (adding no of years replacement to 2016)

I'm going to work on some more MATCH / INDEX formulas but any ideas as to why my vlookups aren't working, or any other tips would be greatly appreciated. I'm guessing the MATCH / INDEX will help with the problem of it going all skewy when I re-sort it.

Thank you all so much for your help.

 

[Ally72]

Hi all

I have managed this now with a bit of tweaking. Thanks for all your help - it was with your ideas that I got to INDEX and MATCH again - I had started out thinking I needed to look at the data in the results table rather than going back to the source.

I've updated this: Temp

In the end, I added a column for replacement year in the source data.

The formula I used in the end was:
(This one's in AD3 copied across and down)

{=IFNA(INDEX($B$3:$I$56,MATCH($K4&AD$3,$B$3:$B$56&$I$3:$I$56,0),2),"")}

The only thing I couldn't do was nest the calculation I needed within the INDEX MATCH which I ended up putting in column I (the new col - Replacement Year, which is just a calculation of 2016 + Year).

I was trying to do ...
{=IFNA(INDEX($B$3:$I$56,MATCH($K4&AD$3,$B$3:$B$56&SUM($AB$3(YEAR($G$3:$G$52))),0),2),"")}


If anyone has any ideas of how to do this, that'd be great - thank you!