Help with formula to stop cell references changing

Novicepay

New Member
Joined
Apr 23, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

Data Sheet.jpg


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.

Summary.jpg


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!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
ABCDE
1
22,364,5982,641,796
Summary
Cell Formulas
RangeFormula
B2B2=INDEX(Validation!B:B,4)
E2E2=INDEX(Validation!B:B,8)
 
Upvote 0
Solution
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
ABCDE
1
22,364,5982,641,796
Summary
Cell Formulas
RangeFormula
B2B2=INDEX(Validation!B:B,4)
E2E2=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
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top