?
Solved

Offset current cell

Posted on 2013-06-26
9
Medium Priority
?
471 Views
Last Modified: 2013-06-26
Morning experts.  Does anyone know how to reference the current cell minus 3 columns minus 1 row?
Thanks
0
Comment
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
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 23

Expert Comment

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

Author Comment

by:Jenedge73
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

by:NBVC
ID: 39278568
not sure what you mean... can you give example of "current formula" and what you need?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Jenedge73
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

by:Jenedge73
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

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

Accepted Solution

by:
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

by:Jenedge73
ID: 39278706
that's exactly it.
Thanks
0
 

Author Closing Comment

by:Jenedge73
ID: 39278709
great
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question