CODEWITHSUNDEEP
excelexcel-formula
laureapresa
laureapresa

Reputation: 179

Dynamic reference to the above cell

In my Excel worksheet, every 25 rows or so (this number is subject to change), a cell in column C contains a progressive number, identifying a set. Right now, there is a simple formula, e.g. in Cell 34:

= C9 + 1

When The number of rows above C34 changes, the formula updates automatically. No issue with that.

I am now experimenting with a macro that inserts a set in between two existing sets; therefore another number that should fit in the progression, and the following numbers should adapt.

I thought that formulas should change, to find the first number above the current cell and add +1.

Trying a solution from this link, I came up with the following formula:

=LOOKUP("z";$C$9:OFFSET(INDIRECT(ADDRESS(ROW();COLUMN()));-1;0))+1

It seems however that it only searches for text, giving a #N/Awhen there is no text and a #VALUE! when there is text; if I do not add the +1, it works fine.

I also tried 

=LOOKUP(2;1/(C$9:C9<>"");C:C)

As explained in the link but all I get is 0

How do I modify these formulas to give me the expected result?

Upvotes: 0

Views: 1594

Answers (1)

g.kov
g.kov

Reputation: 333

Assuming than the number of the first set is in A1, enter this formula 

=INDEX(A:A,MATCH(1E+100,OFFSET(A$1,,,ROW()-1)))+1

in the counting cells (A5,A10, A16 in the example):

enter image description here

Then you can insert rows for a new inserted set, for example:

enter image description here

enter image description here

When ypu copy the same formula into C10, the set numbers update accordingly:

enter image description here

And you can fill the text elements of the set:

enter image description here

Upvotes: 1

Related Questions