Question description:
This user has given permission to use the problem statement for this
blog.
Hi, I'm making myself a weekly budget spreadsheet. I've made a sheet with all my monthly outgoings, and I've worked out how to use SUMIF to combine the amounts for each day, but I want to include a reference and don't know how to combine text cells based on a specific date reference... does that make sense?
Solved by E. U. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
16/04/2018 - 09:27
Hello! How are you?
User
16/04/2018 - 09:27
Hey, I'm alright just struggling.
Excelchat Expert
16/04/2018 - 09:28
It's alright, I'm here to help you
Excelchat Expert
16/04/2018 - 09:28
Are you able to provide a sample data that we could work on?
User
16/04/2018 - 09:29
OK, give me a second
Excelchat Expert
16/04/2018 - 09:31
Sure, take your time. Please let me know once you're done
User
16/04/2018 - 09:37
OK so basically, I have certain things that go out on a monthly basis and certain things that go out on a weekly or bi-weekly basis and find it hard to keep track of both. Is there a way I can list monthly and weekly outgoings as I have and then combine the references and amounts on a week by week basis?
Excelchat Expert
16/04/2018 - 09:38
And this will be reflected in your Wk Beg 16 April, tab, correct?
User
16/04/2018 - 09:39
Yeah
User
16/04/2018 - 09:39
And then I could create a new tab for each week and the cells would automatically update.
Excelchat Expert
16/04/2018 - 09:39
also to confirm, what do you intend to put under column C, Outgoing Reference? Is it a multiple or single value?
User
16/04/2018 - 09:40
So it would say "Internet", or "Rent/Water Bill"
Excelchat Expert
16/04/2018 - 09:41
no Cleaner or Gardener (this is with reference on the weekly outgoing)
User
16/04/2018 - 09:41
That too
User
16/04/2018 - 09:42
So it would combine the weekly and monthly amounts and tell me what references they are
Excelchat Expert
16/04/2018 - 09:43
okay, so that is what you meant by combining text based on a date
User
16/04/2018 - 09:44
Yeah
Excelchat Expert
16/04/2018 - 09:45
Are you amenable to changing the Day under Weekly Outgoings and Date under Monthly Outgoings to format m/dd/yy?
Excelchat Expert
16/04/2018 - 09:45
we could always apply formatting to this cell to show only the day number or name afterwards
User
16/04/2018 - 09:48
Well they dont refer to specific dates, I know that on the 17th of every month is my rent and water bill, and every other Monday my cleaner come. Surely if they're m/dd/yy then this will change every week?
Excelchat Expert
16/04/2018 - 09:48
I see, that makes sense
Excelchat Expert
16/04/2018 - 09:49
how about adding a column in Wk Beg 16 April to show only the day number (which you can opt to hide)?
User
16/04/2018 - 09:49
Yeah that could work?
Excelchat Expert
16/04/2018 - 09:49
Yup, for a while
Excelchat Expert
16/04/2018 - 09:50
I'll try to do this on my sheet offline first, is that okay?
User
16/04/2018 - 09:50
OK
Excelchat Expert
16/04/2018 - 09:51
This may take a little while so I'll thank you for your patience in advance :(
Excelchat Expert
16/04/2018 - 09:51
:)
User
16/04/2018 - 09:52
Thank you
Excelchat Expert
16/04/2018 - 10:03
Hi! Please see attached
Excelchat Expert
16/04/2018 - 10:03
[Uploaded an Excel file]
Excelchat Expert
16/04/2018 - 10:04
May I know which excel you are running in?
User
16/04/2018 - 10:07
I'm on a mac so was using numbers
Excelchat Expert
16/04/2018 - 10:07
Could you please confirm if this is working?
User
16/04/2018 - 10:07
Downloading excel now though
User
16/04/2018 - 10:07
give me a min
Excelchat Expert
16/04/2018 - 10:07
This might be applicable to Excel 2016 though
User
16/04/2018 - 10:09
I'm downloading now so let me see
Excelchat Expert
16/04/2018 - 10:09
Okay :)
Excelchat Expert
16/04/2018 - 10:13
Apparently it also works on the Google Sheet
Excelchat Expert
16/04/2018 - 10:13
if you have time, you may also want to check the document preview
User
16/04/2018 - 10:14
I can see
Excelchat Expert
16/04/2018 - 10:14
I hope this solves you problem
User
16/04/2018 - 10:14
Things aren't updating correctly
User
16/04/2018 - 10:14
Gardener shouldn't be there for monday
Excelchat Expert
16/04/2018 - 10:15
thanks for pointing that out
Excelchat Expert
16/04/2018 - 10:15
let me check one sec
User
16/04/2018 - 10:15
Internet should be 19
User
16/04/2018 - 10:15
Gas should be 21
User
16/04/2018 - 10:16
Also, I have week A and B because I need to have cleaner one week on a monday and gardener the next on a sunday
Excelchat Expert
16/04/2018 - 10:17
Please see attached
[Uploaded an Excel file]
Excelchat Expert
16/04/2018 - 10:17
i have also updated the document preview, for your quick reference
User
16/04/2018 - 10:19
Thanks
User
16/04/2018 - 10:20
Howcome it's not updating if I add things?
Excelchat Expert
16/04/2018 - 10:20
You're welcome
User
16/04/2018 - 10:20
And it should only show cleaner or gardener depending on if it's week a or b
User
16/04/2018 - 10:20
See I added gas on monthly
User
16/04/2018 - 10:20
It should be on 21
Excelchat Expert
16/04/2018 - 10:21
Oh, yes... let me check again
Excelchat Expert
16/04/2018 - 10:22
found it, i failed to fix the first row reference so it excluded it on the search when i copy and paste the formula down the rows
Excelchat Expert
16/04/2018 - 10:23
By the way, how do i know if Wk Beg 16 April is week A or B?
User
16/04/2018 - 10:24
It says Week B in cell A1
Excelchat Expert
16/04/2018 - 10:25
Thanks for pointing that out
Excelchat Expert
16/04/2018 - 10:25
is it okay if we add another column to indicate week A or B?
User
16/04/2018 - 10:25
OK
Excelchat Expert
16/04/2018 - 10:25
this again, you can opt to hide like the day number
User
16/04/2018 - 10:26
cool\
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.
