Referencing named cells from other rows in formulae

[patrowles]

Hi, everyone - new member with a first post.

I am trying to solve a problem which doesn't really exist (story of my life!), but I like neatness in my Excel formulae and figure that there should be an easy way to achieve my objective in this instance.

I have created a spreadsheet to document drum programming for my song demos. It has three adjacent columns (B, C and D) which I have named (using the Name Box, i.e. not just by creating column headings) LENGTH, START and END. On any given row, LENGTH (B) records the length in bars of the drum pattern I've used, START (C) records the bar number in the song at which that pattern begins, and END (D) records the bar number at which that pattern finishes. I want to define formulae in the START and END columns which enable me to populate them cumulatively on the basis of the values I type as I go down the LENGTH column.

The spreadsheet's first row (let's ignore the column headings for simplicity and call it row 1) has 2 in the LENGTH column and 1 hard-coded in the START column, on the basis that the song starts at the first bar. I can then enter the formula "=START+LENGTH" in the END cell for that row, and the calculated value "3" appears therein.

My problem (such as it is) begins when I try to define the formula for the START cell in the next row (and all subsequent rows). It should be "=(x)+1", where x = "the END value from the previous row". However, I don't know how to refer to that cell by its defined name. I could, of course, simply specify "=D1+1", and so on down the column, but that defeats the object of using name references.

I did some Googling before coming here, but all the solutions I could find weren't quite I was looking for, or were for problems so much more complex than mine that even when I did try to adapt them, all I got were #REF! errors. Thanks for reading - any help anyone could give would be greatly appreciated.

 

[circledchicken]

Hi and welcome to the forum,

Not sure this makes it any easier / clearer - but you can try something like this in C2:
=INDEX(End, ROWS(D$1:D1)) + 1

or use the intersection operator like:
=(End D1) + 1

Also, rather than defining names for every column, you may want to use structured references with smart tables (assuming Excel 2007+).
See here for more details:
Working with Tables in Excel 2013, 2010 and 2007

 

[patrowles]

Hi circledchicken,

Many thanks for the welcome, and for such a prompt and helpful reply. Both your examples are new to me in terms of Excel functionality, and both do the job. I shall also follow up your link to investigate the use of structured references with smart tables, as I'm using Excel 2010.

Thanks again!

(A bit of "forum strangeness" at work here - I could have sworn I submitted this reply within minutes of being notified of circledchicken's reply, but on revisiting the site this evening, having logged out, it wasn't on the thread. I clicked <reply> to write it again, and saw a link in the resultant 'Your message' area with the intriguing label "Restore auto-saved content". I clicked it, and voila - the text of the post I thought I'd submitted earlier reappeared!) </reply>

 

[circledchicken]

You're welcome - thanks for the feedback.

That does seem a bit strange, I've had issues with slow or no notifications at times but not that before - hopefully it doesn't happen again.