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

TrinityGal

New Member
Joined
Jun 16, 2021
Messages
6
Office Version
  1. 2011
Platform
  1. MacOS
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

Well-known Member
Joined
Dec 30, 2008
Messages
13,790
Office Version
  1. 365
Platform
  1. Windows
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
BCD
14OctoberSat2
15Sun3
16Sat9
17Sun10
18Sat16
19Sun17
20Sat23
21Sun24
22Sat30
4TH QTR
Cell Formulas
RangeFormula
C14:C22C14=WORKDAY.INTL(IF(ISNUMBER(B14),B14,C13),1,"1111100")
D14:D22D14=C14
 
Solution

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

TrinityGal

New Member
Joined
Jun 16, 2021
Messages
6
Office Version
  1. 2011
Platform
  1. MacOS
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
BCD
14OctoberSat2
15Sun3
16Sat9
17Sun10
18Sat16
19Sun17
20Sat23
21Sun24
22Sat30
4TH QTR
Cell Formulas
RangeFormula
C14:C22C14=WORKDAY.INTL(IF(ISNUMBER(B14),B14,C13),1,"1111100")
D14:D22D14=C14
Thank you so much Jason
 

Forum statistics

Threads
1,143,687
Messages
5,720,298
Members
422,275
Latest member
Maria95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top