Question description:
This user has given permission to use the problem statement for this
blog.
Hi There,
I am using google sheet and I want help regarding formula. Here's the scenario
Have 12 Period and each period have 4 weeks in it. Now on another sheet I want to select for e.g. If I do drop down and select period 1 and week 1 then result should be 1st date of week 1. same way if I drop down and select period 9 and week 3 then result should be 1st date of week 3.
Please help me with the formula
Thank You
Solved by C. W. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
15/09/2018 - 09:34
Welcome to Excelchat, I see that your question is about Formulas.
Excelchat Expert
15/09/2018 - 09:34
I can help you with that problem, I'll be able to give you a solution and an explanation.
Excelchat Expert
15/09/2018 - 09:34
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
15/09/2018 - 09:35
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert
15/09/2018 - 09:35
May you please share the data you have so I can help you with your concern? You can also paste a sample data on the document preview pane.
User
15/09/2018 - 09:35
ok sure
Excelchat Expert
15/09/2018 - 09:38
You can also share the link to the file so I can it without the errors
Excelchat Expert
15/09/2018 - 09:38
*I can see it without the errors
User
15/09/2018 - 09:38
it says N/A
User
15/09/2018 - 09:39
when I select any week from period 1 then it works but when I change period then it start giving error
Excelchat Expert
15/09/2018 - 09:40
Based on what you've shared, you need the dates to be populated on column A Sheet 1 based on the Period and Week selected on row 1. The dates will be selected from the table provided in Period and Week Tab. Do you believe that will address your problem?
User
15/09/2018 - 09:41
yes
User
15/09/2018 - 09:42
so basically I want only 1st date of each week from each period to be populated
Excelchat Expert
15/09/2018 - 09:43
Ok I'm going to create the formula needed to get the output you want. This should take me less than 20 minutes. If we need more than 20 minutes, you'll be able to extend the session up to 60 total minutes.
User
15/09/2018 - 09:43
My session says only 10 minutes remaining
Excelchat Expert
15/09/2018 - 09:44
You will have an option to extend it if I am not yet done in 10 minutes
Excelchat Expert
15/09/2018 - 09:45
I'll be working in the shared spreadsheet on the document preview. When our session is over you'll be able to access the solution in your session history by downloading the Excel File
User
15/09/2018 - 09:45
Ok sure
Excelchat Expert
15/09/2018 - 09:48
I’m working on your solution. But don’t worry, I’ll be updating you throughout!
User
15/09/2018 - 09:48
ok
Excelchat Expert
15/09/2018 - 09:57
Sorry this may take some time, I may need to transpose your data in a way that the formula can work
User
15/09/2018 - 09:58
ok
Excelchat Expert
15/09/2018 - 10:11
Almost done, sorry for the delay
User
15/09/2018 - 10:11
ok
Excelchat Expert
15/09/2018 - 10:15
Here's the formula you need: =VLOOKUP(CONCATENATE(B1,D1),'Period and Week'!T:U,2,0)
Excelchat Expert
15/09/2018 - 10:15
Before that, please make sure to paste the formula from Period and Week tab columns R to U, which transformed your table to a readable one for the formula
Excelchat Expert
15/09/2018 - 10:16
Oh wait the formula did not copy correctly
User
15/09/2018 - 10:17
ok
Excelchat Expert
15/09/2018 - 10:23
Ok I have updated the formulas in columns R to U of Period and week tab
Excelchat Expert
15/09/2018 - 10:23
Please copy and paste it on your original file
Excelchat Expert
15/09/2018 - 10:23
Do you have any other questions about this problem and the solution?
User
15/09/2018 - 10:24
No thats every thing thanks
Excelchat Expert
15/09/2018 - 10:24
Thanks for coming to Excelchat. Feel free to leave any comments or feedback. Have a nice day!
'
Excelchat Expert
15/09/2018 - 10:27
Just a reminder, we are unable to end this session on our end until you do so please do not close this window without ending the session properly (Red X or End Session). Feel free to end this session as soon as you are ready for us to continue helping other customers. Thank you.
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.
