Help with formula to stop cell references changing

[Novicepay]

Hi There!

Hoping this is pretty simple, I just can't work it out, I know a plain vlookup isn't the answer, nor is using absolute $, I've tried indirect and another couple of options but to no success. The spreadsheet has more info, however for example purposes I have simplified it.

What I'm trying to do is always compare the current fortnight to previous on a summary sheet, which is feed to from a data sheet. This data sheet format cannot be changed as I don't create it, what happens is each fortnight rows are inserted at the top of the data sheet and that then becomes the current fortnight. The current fortnight values will always be B4 and previous fortnight will always be B8 regardless of how many cells are inserted.

Example Data Sheet

This is then pulled through to a summary sheet (simplified for this example), where I always need Current to reference B4 from the Data sheet and Previous to reference B8. I've included vlookup to show where it's referencing.

If only there was an absolute option that didn't allow for cells to change when rows were inserted... any help is greatly

Thanks in advance!

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

The current fortnight values will always be B4 and previous fortnight will always be B8

In that case, try these on your Summary sheet.

Novicepay.xlsm
	A	B	C	D	E
1
2		2,364,598			2,641,796
Summary

Cell Formulas
Range	Formula
B2	B2	=INDEX(Validation!B:B,4)
E2	E2	=INDEX(Validation!B:B,8)

 

[kvsrinivasamurthy]

Try

=VLOOKUP(A2,INDIRECT("Validation!$A$2:$B$5"),2,0)

 

[Novicepay]

Welcome to the MrExcel board!

For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.


In that case, try these on your Summary sheet.

Novicepay.xlsm
	A	B	C	D	E
1
2		2,364,598			2,641,796
Summary

Cell Formulas
Range	Formula
B2	B2	=INDEX(Validation!B:B,4)
E2	E2	=INDEX(Validation!B:B,8)

Hi Peter,

Thanks for your help! This worked exactly how I was wanting it to. Noted re XL2BB

Thank again,

Richie

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.