• Status: Solved
• Priority: Medium
• Security: Public
• Views: 552

# Offset current cell

Morning experts.  Does anyone know how to reference the current cell minus 3 columns minus 1 row?
Thanks
0
Jenedge73
• 5
• 4
1 Solution

Commented:
=OFFSET(X5,-1,-3) where formula is in X5.. the current row
0

Author Commented:
yes, but I need to put it in the current formula so that I always reference the (-1,-3)
0

Commented:
not sure what you mean... can you give example of "current formula" and what you need?
0

Author Commented:
IFERROR(SUMIF(INDIRECT("'"&TEXT(\$D\$21,"dd-mmm-yyyy")&"'!\$A\$3:\$A\$14"),H\$3,INDEX(INDIRECT("'"&TEXT(\$D\$21,"dd-mmm-yyyy")&"'!\$A\$3:\$Q\$14"),,MATCH(\$D22,INDIRECT("'"&TEXT(\$D\$5,"dd-mmm-yyyy")&"'!\$A\$3:\$Q\$3"),0))),0)

This formula is in H22 and I want to always one row up and 3 rows left.  in this instance I would return D1
0

Author Commented:
IFERROR(SUMIF(INDIRECT("'"&TEXT(\$D\$21,"dd-mmm-yyyy")&"'!\$A\$3:\$A\$14"),H\$3,INDEX(INDIRECT("'"&TEXT(\$D\$21,"dd-mmm-yyyy")&"'!\$A\$3:\$Q\$14"),,MATCH(\$D22,INDIRECT("'"&TEXT(\$D\$5,"dd-mmm-yyyy")&"'!\$A\$3:\$Q\$3"),0))),0)

This formula is in H22 and I want to always one row up and 3 rows left.  in this instance I would return D21
0

Commented:
What do you mean by this instance would return D1?  Where?
0

Commented:
Oh you changed it to D21....

Try:

=IFERROR(SUMIF(INDIRECT("'"&TEXT(OFFSET(H22,-1,-4),"dd-mmm-yyyy")&"'!\$A\$3:\$A\$14"),H\$3,INDEX(INDIRECT("'"&TEXT(OFFSET(H22,-1,-4),"dd-mmm-yyyy")&"'!\$A\$3:\$Q\$14"),,MATCH(\$D22,INDIRECT("'"&TEXT(\$D\$5,"dd-mmm-yyyy")&"'!\$A\$3:\$Q\$3"),0))),0)

You may have to change the D22 and/or D5 similarly if necessary

also note D21 is 4 columns over....
0

Author Commented:
that's exactly it.
Thanks
0

Author Commented:
great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.