OFFSET parameters as variables or auto increment a value

[mzmrizu]

Hello - I have the following formula, and I am looking for a way to copy this formula down a row, and have the row-variable "-11" auto increment:

=IFERROR(DATEDIF(J16,OFFSET(J16,-11,4),"d"),"TBD")

When copying this formula down, although it properly updates J16 to J17 and so on, I also need the "-11" to increment to "-12", "-13" and so on.

Ideally I would like this to be solved using a formula as this is a template form that can be appended in succession within the same worksheet, and when pasted (via macro) the cell referred to using OFFSET also changes - but fortunately the J16 is relative and updates to the row within which the new template is copied.

I have spent hours looking at INDEX, MATCH, creating hidden reference cells to calculate a value I can refer to -- the root of the problem is I can easily create an array and search down, however I have not found a way to search up a column where the number of rows are dynamic/changing with multiple matching values. So part B or the BEST solution would be a way to translate the following into a formula:

"from cell (e.g. D100), search up within column D until you find the first occurrence of the string "DATE" and return the row (or alternatively the cell coordinates)...."

Thoughts??

 

[ukmikeb]

Hi

Try :-

=IFERROR(DATEDIF(J16,OFFSET(J16,-11-(ROWS($1:1)-1),4),"d"),"TBD")

hth

 

[ukmikeb]

Or a neater version :-

=IFERROR(DATEDIF(J16,OFFSET(J16,-(10+ROWS($1:1)),4),"d"),"TBD")

hth

 

[mzmrizu]

SOLVED -- Excellent, works exactly as needed. I have created very complex formulas and VB scripts, and rarely used (years ago) ROWS to create an iteration before - bravo! I had to search on this, and also found the nuances of using ROWS vs ROW for this approach. Thanks ukmikeb!

 

[Jonmo1]

Just out of curiosity..

Why are we using Offset in the first place?
Why not just use

=IFERROR(DATEDIF(J16,$N$5,"d"),"TBD")

 

[mzmrizu]

Hi Jonmo1, the reason for the use of OFFSET is this that $N$5 would be static, and needs to be relative -- here is why.... this is a summary report table, that varies in length, but will then also have additional summary report tables appended below the previous - I have a macro that will paste a new blank summary report table below the previous, this paste will need to keep the relative relationship. The formula with the use of OFFSET keeps the relative relationship to allow this "template" approach. Thanks for digging in and asking the question, always helps to have other eyes looking and catching a way to improve and simplify the formula - this thread will no doubt help many others in the use of both of these, thanks again!

 

[Jonmo1]

I still don't get it..
It's still static (in a roundabout way) using this offset

=IFERROR(DATEDIF(J16,OFFSET(J16,-11,4),"d"),"TBD")

When copying this formula down, although it properly updates J16 to J17 and so on, I also need the "-11" to increment to "-12", "-13" and so on.

When you drag down, If the J16 incriments to J17, AND the -11 incriments to -12, then the offset still ultimately points to N5

The offset in both suggested solutions
OFFSET(J16,-11-(ROWS($1:1)-1),4)
and
OFFSET(J16,-(10+ROWS($1:1)),4)

While they both do accomplish what you've asked (to incriment the -11 to -12 as dragged down)
Because J16 is also incrimenting to J17 and J18 etc..,
Then They both still ultimately end up referring to N5 as the formula is dragged down, it doesn't move to N6 N7 etc..

So why not just use $N$5 instead of the offset.

Excel Workbook
	K	L	M	N
1	hello	hello
2	hello	hello
3	hello	hello
4	hello	hello
5	hello	hello		hello
Sheet2

 

[ukmikeb]

Hi

Thanks for the feedback.

Pleased to have helped solve your problem.

Good luck with your project.

@Jonmo1 I opted for negating (10 + Rows($1:1)) as that seemed cleaner than (-11-Rows($1:1)-1). I can understand the OPs comment about not using cell $N$5 as he then has one standard formula across his report.

 

[Jonmo1]

I can understand the OPs comment about not using cell $N$5 as he then has one standard formula across his report.

How does that standardize anything...
You still have to hard code J16.
It hasn't made anything easier, quite the opposite if you ask me.

Again, this is just curiosity. I'm not knocking your solution, it absolutely accomplishes the goal the OP had of incrimenting -11 to -12.
If the solution works for mzmrizo, great.

I just don't get what advantage this formula
=IFERROR(DATEDIF(J16,OFFSET(J16,-(10+ROWS($1:1)),4),"d"),"TBD")
has over this formula
=IFERROR(DATEDIF(J16,$N$5,"d"),"TBD")

 

[mzmrizu]

I understand the point that Jonmo1 is making - and upon further testing
I discovered this does accomplish my initial goal (increment line to
line when dragged down), however after testing this out a bit more it
does not work in my "template" approach.

The summary report (table) is appended by use of a macro:
Step 1. After the last row of data in the worksheet, the macro skips a line (leaving it blank)
Step 2. The macro copies from a hidden worksheet an empty summary report (table)
Step 3. The macro finally pastes (appends) the empty summary report (table) below the blank line
Note -- at this point my original OFFSET(J16,-11,4) would paste and adjust relative to its new position in the worksheet
(worked as designed)

Issue that has been found, given I was looking for a way to incorporate into the original
formula the ability to increment the value "-11" when dragged down by the user (as the number of rows can vary), by
replacing this static value with "-(10+ROWS($1:1))" when pasting/appending a new template this becomes
relative and $1:1 became $1:22, at this point it fails to OFFSET properly as Jonmo1 suspected.

After churning on this for a bit, I am reverting back to my first approach, which I have found to be more difficult and complex
-- I had hoped for a more straight forward solution.

For all and those following this thread, below is my final solution and formula which does accomplish my goal, I have included a description of its operation:

=IF(J16<>"",IFERROR(DATEDIF(J16,OFFSET(J16,-(ROW()-((SUMPRODUCT(MAX(($A$1INDIRECT("A"&ROW()))="Skull")*ROW($A$1INDIRECT("A"&ROW()))))))-9)),4),"d"),"TBD"),"--")

DESCRIPTION
* First I include error correction to account for blank/null values, then I state that I need to find the number of days between two dates.
* HOWEVER the second date's location is dynamic. To find the relative position of my current cell to the primary date field, I am using OFFSET.
* The offset parameter for the row (initially -11 up from my first/primary position in my original example) is determined by identifying the row of the closest
occurrence of the string "Skull" based on a range created on the fly -- this range must end on my current cell's row,
e.g. ($A$1:A16) that is parallel to me at J16 to avoid a circular reference (cannot define my range while within my range).

---It is at this point that my need for this solution comes to light---
After the last row of this report, a new blank report can be appended with the same structure as the first, HOWEVER the offset for the
rows below it now varies from the previous report AND there is a newly added occurrence of "Skull" to which I can refer to and create my new offset and
create my on-the-fly range.

This one hurt my head -- thanks to both Jonmo1 and ukmikeb, I appreciate the time and effort to help support me, and I will contribute actively where I can as well!

UPDATE: if you see frowny face, replace them with ....they are part of the formula and not intended to be emoticons!!