[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Offset current cell

Posted on 2013-06-26
Medium Priority
500 Views
Morning experts.  Does anyone know how to reference the current cell minus 3 columns minus 1 row?
Thanks
0
Question by:Jenedge73
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 4

LVL 23

Expert Comment

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

Author Comment

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

LVL 23

Expert Comment

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

Author Comment

ID: 39278628
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 Comment

ID: 39278659
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

LVL 23

Expert Comment

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

LVL 23

Accepted Solution

NBVC earned 2000 total points
ID: 39278682
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 Comment

ID: 39278706
that's exactly it.
Thanks
0

Author Closing Comment

ID: 39278709
great
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
###### Suggested Courses
Course of the Month13 days, 21 hours left to enroll