refer to a prior row in a named range?

[NNN]

Hello! I'm writing a time-based model in Excel, with variables set up as columns, and would like to calculate output based on the values of in previous timestep (with rows representing timesteps). if I have variables setup as named ranges, is there a very slick way to refer to the previous row of a named range, either from within the range, or in a different range?

The solution I've come up with:
Naming a new range that refers to a helper column where every cell is set equal to the value of the cell in the previous row of the important variable column/range. I name the new range "VARRR_Prev" if the important variable/range is named "VARRR." This method works but requires a bit of construction to repeat for all important variables.

Other possibilities I've come across:
using index and match somehow - I can see this working, but it's a bit kludgy
offset - volatility of command would make the workbook sloooow
a relatively-referenced named range - this was very promising, but I can't get it to work across different sheets. Being able to name a relative range "Prior" and have it refer to the prior row, and then unioning the row-range "Prior" with the column-range "VARRR" is very, very slick. But, again, I haven't gotten it to work across sheets.

So if anyone out there has ideas, let me know, and thanks!

 

[Hui]

NNN

Firstly, Welcome to the Chandoo.org Forums

I would use Index() to access the previous records

Can you post a sample file with notes for a more specific answer

 

[NNN]

Thanks! A good point, and it works. I was worried that using INDEX would make the equations hard to read, and I've worked to name many variables to increase readability. But at your suggestion I tried using INDEX, and it comes out succinct - there's no nesting of more functions required (INDEX(MATCH(IF... and so forth).