Question description:
This user has given permission to use the problem statement for this
blog.
I'm looking to offset from =today(), for example:
If cell A13=today(), display cell A20.
If cell B13=today(), display cell B20.
If cell C13=today(), display cell C20.
I want to do this for 365 rows. Is there a simpler way of doing this than a huge nested IF statement?
Solved by K. H. in 34 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
13/09/2018 - 10:29
Hello! How are you?
User
13/09/2018 - 10:29
Hi DT. I'm really well thanks. How are you?
Excelchat Expert
13/09/2018 - 10:29
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
Excelchat Expert
13/09/2018 - 10:30
That's good to hear. I am great. Thanks for asking. :)
User
13/09/2018 - 10:30
Okay!
Excelchat Expert
13/09/2018 - 10:30
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert
13/09/2018 - 10:31
Firstly, are you able to share a sample data on the document preview to the right, so I could better visualize your worksheet?
User
13/09/2018 - 10:33
I'm looking to have a 'home page', where I can easily see how much interest I have made today. So, if today was 25th August, I'd like the cell to show E9. If 26th, E10, etc.
User
13/09/2018 - 10:33
I started a nested IF statement
Excelchat Expert
13/09/2018 - 10:34
From your problem statement, you wanted to be able to utilize the OFFSET function on this worksheet.
User
13/09/2018 - 10:34
Ok, that sounds right
Excelchat Expert
13/09/2018 - 10:35
Are you also open to use of other formula as long as it is not a nested IF statement?
User
13/09/2018 - 10:35
Not really, no. I started to use a Nested IF statement generator, but realised there's got to be an easier way
Excelchat Expert
13/09/2018 - 10:36
You are right. Let me show you 2 sets of formula which can do the job for you. :)
User
13/09/2018 - 10:36
Thank you :D
Excelchat Expert
13/09/2018 - 10:41
Hello! Are you able to see the document preview?
User
13/09/2018 - 10:41
I can see it, yes.
Excelchat Expert
13/09/2018 - 10:41
So the formula are in cells H4 and H5, both returning the same results
User
13/09/2018 - 10:42
ok
Excelchat Expert
13/09/2018 - 10:42
Based on the date specified in H3
Excelchat Expert
13/09/2018 - 10:44
First let me tell you how the INDEX and MATCH combo works.
Excelchat Expert
13/09/2018 - 10:44
The syntax for INDEX is INDEX(reference, [row], [column])
User
13/09/2018 - 10:44
Oh no I typed in the spreadsheet instead of here!
User
13/09/2018 - 10:44
Ok sorted
Excelchat Expert
13/09/2018 - 10:45
Cool
Excelchat Expert
13/09/2018 - 10:48
So for the index formula we can say that it returns a value of a range based on the position indicated. Our reference range is the whole of column E (or E:E). The position is determined by our MATCH formula.
Excelchat Expert
13/09/2018 - 10:49
In this example, in our formula ...MATCH(H3,A:A,0)... This will find the position of H3 (or Aug 22) in column A.
Excelchat Expert
13/09/2018 - 10:49
So MATCH(H3,A:A,0) = 6
Excelchat Expert
13/09/2018 - 10:50
And then this feeds to your INDEX formula INDEX(E:E,6)
Excelchat Expert
13/09/2018 - 10:50
It will now return the 6th row in your column E = 0.23973
Excelchat Expert
13/09/2018 - 10:52
OFFSET works similarly such that you have to first identify an initial reference point and tell excel to move by N rows and N columns after that point.
Excelchat Expert
13/09/2018 - 10:53
so in our formula =OFFSET($E$2,MATCH(H3,$A$2:$A$13,0)-1,0), where MATCH(H3,$A$2:$A$13,0)-1 = 5-1 = 4
Excelchat Expert
13/09/2018 - 10:54
We get > OFFSET($E$2,4,0)... So you could see this tells excel to move 4 rows down and 0 columns to the right. That's basically cell E6 = 0.23973
Excelchat Expert
13/09/2018 - 10:55
Clear so far?
User
13/09/2018 - 10:55
Yup!
Excelchat Expert
13/09/2018 - 10:56
Cool. Could you please confirm if this solution solve your problem?
Excelchat Expert
13/09/2018 - 10:58
If you don't have any more clarifications, you may end the session any time now by clicking the END SESSION button on the upper right corner of the workspace (that's beside the countdown timer). :)
Excelchat Expert
13/09/2018 - 10:58
You may also keep a copy of this worksheet by clicking File>Download as>Microsoft Excel
User
13/09/2018 - 10:59
This does solve my problem. Thank you so much!
User
13/09/2018 - 10:59
Is this service available 24 hours a day?
Excelchat Expert
13/09/2018 - 10:59
Thanks for coming to Excelchat. Feel free to leave any comments or feedback. Have a nice day!
Excelchat Expert
13/09/2018 - 10:59
Yes. We have a pool of experts on standby to help 24 hours a day :)
User
13/09/2018 - 11:00
That's amazing. Thank you so much. Sometimes there's an issue I just can't work out so I'm so glad you're here.
User
13/09/2018 - 11:00
What is the charge noramlly?
Excelchat Expert
13/09/2018 - 11:01
Happy to hear that.
Excelchat Expert
13/09/2018 - 11:01
You may check this link for the rates
Excelchat Expert
13/09/2018 - 11:01
https://www.got-it.ai/solutions/excel-chat/pricing/personal
User
13/09/2018 - 11:01
Is there a student rate?
Excelchat Expert
13/09/2018 - 11:02
I am not so sure about that. You may e-mail sheets.support@got-it.ai for further inquiries on the rates. :)
User
13/09/2018 - 11:02
Thank you so much.
User
13/09/2018 - 11:02
Speak soon, DT.
Excelchat Expert
13/09/2018 - 11:02
You're welcome. :)
Excelchat Expert
13/09/2018 - 11:03
You may end the session any time now by clicking the END SESSION button on the upper right corner of the workspace (that's beside the countdown timer). :)
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
