Can a formula replace the Offset's "Reference"?

[TrinityGal]

I have a question, what happens if you don't want to have a set reference, but you need to have that be taken from a callout table. For example, in a sheet I want to have the month on a cell, days in another column beside it with "ddd", only for Sat & Sun, and beside that column I want the date of such day. I want that information taken from a call out table in a separate sheet, where I have all the months from Jan-Feb, and where the dates and days are modified automatically when I update the year.

In my first sheet I want it to update the days and dates when I manually update the month, finding the month on my call out table with DGET, and selecting the first day and date respectively. That only works well with the first row. But for the other rows for the additional days and dates below the first row I want to use the OFFSET, but in the reference field I want it to find the corresponding month in the call out table based on what I manually enter in my first sheet for the month and based on that I want it to offset the results with the days and dates on the rows below the first one. Does that make sense? However, when I input the DGET formula in the reference section, it gives me an error saying "Argument myst be a range". Do you know what is happening and how I can fix it? Here is my formula:

=OFFSET(DGET('Call Out Table'!A2:E128,"Day",{"Month";B14}),1,0,1,1) I look forward to your reply! Thanks in advance!

 

[jasonb75]

Wouldn't it be easier to have formulas calculating Saturdays and Sundays directly on your quarterly sheets -- instead of referring to the Call Out Table?

After taking another look, I'm in agreement with @Tetra201 100%. I didn't look closely enough at what was being done and looked at a way to do what was asked for rather than what was needed.

B14 contains a proper date of 10/1/2021 (assuming US date format) with a custom format of mmmm applied to it.
C14 and D14 are formatted as ddd ad d respectively.

I haven't allowed for overfill with this example so if you have too many rows for any given month in the calendar then it will show the first dates from the following month at the bottom.

Sample (3).xlsx
	B	C	D
14	October	Sat	2
15		Sun	3
16		Sat	9
17		Sun	10
18		Sat	16
19		Sun	17
20		Sat	23
21		Sun	24
22		Sat	30
4TH QTR

Cell Formulas
Range	Formula
C14:C22	C14	=WORKDAY.INTL(IF(ISNUMBER(B14),B14,C13),1,"1111100")
D14:D22	D14	=C14

 

[TrinityGal]

Johnny, You're awesome!!! Thank you so very much!!!

 

[TrinityGal]

After taking another look, I'm in agreement with @Tetra201 100%. I didn't look closely enough at what was being done and looked at a way to do what was asked for rather than what was needed.

B14 contains a proper date of 10/1/2021 (assuming US date format) with a custom format of mmmm applied to it.
C14 and D14 are formatted as ddd ad d respectively.

I haven't allowed for overfill with this example so if you have too many rows for any given month in the calendar then it will show the first dates from the following month at the bottom.

Sample (3).xlsx
	B	C	D
14	October	Sat	2
15		Sun	3
16		Sat	9
17		Sun	10
18		Sat	16
19		Sun	17
20		Sat	23
21		Sun	24
22		Sat	30
4TH QTR

Cell Formulas
Range	Formula
C14:C22	C14	=WORKDAY.INTL(IF(ISNUMBER(B14),B14,C13),1,"1111100")
D14:D22	D14	=C14

Thank you so much Jason