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!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,790
Office Version
  1. 365
Platform
  1. Windows
it gives me an error saying "Argument myst be a range". Do you know what is happening and how I can fix it?
You can fix it by using a range. The offset reference must refer to a cell directly, not what is in the cell which is what the DGET function will be looking at.

In theory, you probably just need to refer to the top left cell of the table then use match for the row / column offsets. It would be easier to follow what you have with a visual representation of your sheet rather than just a description.
 

TrinityGal

New Member
Joined
Jun 16, 2021
Messages
6
Office Version
  1. 2011
Platform
  1. MacOS
Hi Jason.
Thanks for your reply!

I did do it by selecting one single cell on my call out table, more specifically the title of the column where I want the data from, but this does not work because when I make a new sheet for a new quarter of the year, and I update the months, the results are still selected from that top cell, giving me the results of the first three months.

Here is a link tot he document, which I published in Google sheets: https://docs.google.com/spreadsheet...JJATbCWGO543qV2q8ZufNLPVensDw/pub?output=xlsx

Does this help?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,790
Office Version
  1. 365
Platform
  1. Windows
the results are still selected from that top cell,
That is not because offset is referring to a specific cell but because the row offset is fixed at 1. In principle you would use something on the format of
=OFFSET('Call Out Table'!$A$2,MATCH(something, somewhere, FALSE),0,1,1)
Where something and somewhere refer to a date to look for and a place to look for it, for example a specific date in a column of dates.
Given that you are only returning a single cell as the result, INDEX would by far be the preferable function to use.

I can't open your workbook due to security limitations, however there is a tool called XL2BB (<< link) that you can use to post your sample directly to the forum (only needs to be about 10 rows, not the whole thing).
 

TrinityGal

New Member
Joined
Jun 16, 2021
Messages
6
Office Version
  1. 2011
Platform
  1. MacOS

ADVERTISEMENT

I've made a copy of the workbook with only two sheets, because I think you need to see the call-out table, or else the formulas would be broken, as they use data from that sheet.
Best I can do it upload images from that published version.

Does this help? It shows the formulas I am using and what I am trying to get, and where...
 

Attachments

  • 1st_dayname_with_TEXT-IF-DGET.jpg
    1st_dayname_with_TEXT-IF-DGET.jpg
    245 KB · Views: 4
  • 1st_days_date-with-DGET.jpg
    1st_days_date-with-DGET.jpg
    245.6 KB · Views: 5
  • 2nd_daysname_try2offset-from-date-2-columns.jpg
    2nd_daysname_try2offset-from-date-2-columns.jpg
    184.5 KB · Views: 4
  • 2nd_days_date_ERROR-in-Formula.jpg
    2nd_days_date_ERROR-in-Formula.jpg
    196.3 KB · Views: 5
  • error_message.jpg
    error_message.jpg
    84 KB · Views: 6
  • call_out_table.jpg
    call_out_table.jpg
    123.4 KB · Views: 5

TrinityGal

New Member
Joined
Jun 16, 2021
Messages
6
Office Version
  1. 2011
Platform
  1. MacOS
Here are some notes about what I am after...
 

Attachments

  • 1st_dayname_with_TEXT-IF-DGET_Notes.jpg
    1st_dayname_with_TEXT-IF-DGET_Notes.jpg
    218.4 KB · Views: 2

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,790
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It's a bit of a formula unfriendly format but it should be possible. I don't think that I will be able to look at it today, hopefully another member will see your thread and provide a solution before I return.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,790
Office Version
  1. 365
Platform
  1. Windows
See if this works, I've only done a very quick test so could have missed some errors.
Sample (3).xlsx
BCD
14OCTOBERSat2
15Sun3
16Sat9
17Sun10
18Sat16
19Sun17
20Sat23
21Sun24
22Sat30
4TH QTR
Cell Formulas
RangeFormula
C14:C22C14=INDEX('Call Out Table'!$E:$E,MATCH(LOOKUP("zzz",$B$14:$B14),'Call Out Table'!$B:$B,0)+ROWS(C$14:C14)-MATCH("zzz",$B$14:$B14))
D14:D22D14=INDEX('Call Out Table'!$C:$C,MATCH(LOOKUP("zzz",$B$14:$B14),'Call Out Table'!$B:$B,0)+ROWS(C$14:C14)-MATCH("zzz",$B$14:$B14))
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,688
@TrinityGal: 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?
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,431
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
I can't open your workbook due to security limitations, however there is a tool called XL2BB (<< link) that you can use to post your sample directly to the forum (only needs to be about 10 rows, not the whole thing).

Here is the XL2BB Data ...

4TH QTR worksheet

Sample.xlsx
ABCDEFGHIJKLMN
1
2OLA 2021 VOLUNTEER CALENDAR
3DATESERVERSHOSTESSBUSSERSCOOKSPRESENTER
4NOTES: Weekend datesSERVERS: Description of dutiesHOSTESS: Description of dutiesBUSSERS: Description of dutiesCOOKS: Description of dutiesPRESENTER: Description of duties
13Mes
14OCTOBERSat2Volunteer 1Volunteer 6Volunteer 3Volunteer 8Presenter 1
15Sun#N/A
16Sat9
17Sun10
18Sat16
19Sun17
20Sat23
21Sun24
22Sat30
23
24NOVEMBERSat6
25Sun17
26Su23
27Sa24
28Su21
29Sa27
30Su28
31Sa34
32Su35
33
34DECEMBERSa4
35Su5
36Sa11
37Su12
38Sa18
39Su19
40Sa25
41Su26
4TH QTR



Call Out Table worksheet

Sample.xlsx
ABCDEF
12021
2Month#MesDiaWeekday#Day# of Additional 7
31January27Sat
4131Sun
5197Sat1
61101Sun
71167Sat2
81171Sun
91237Sat3
101241Sun
111307Sat4
121311Sun
13
14  
152February67Sat
16271Sun
172137Sat1
182141Sun
192207Sat2
202211Sun
212277Sat3
222281Sun
23  
24
253March67Sat
26371Sun
273137Sat1
283141Sun
293207Sat2
303211Sun
313277Sat3
323281Sun
33  
34
354April37Sat
36441Sun
374107Sat1
384111Sun
394177Sat2
404181Sun
414247Sat3
424251Sun
43  
44
455May17Sat
46521Sun
47587Sat1
48591Sun
495157Sat2
505161Sun
515227Sat3
525231Sun
535297Sat4
545301Sun
55
56  
576June57Sat
58661Sun
596127Sat1
606131Sun
616197Sat2
626201Sun
636267Sat3
646271Sun
65  
66
677July37Sat
68741Sun
697107Sat1
707111Sun
717177Sat2
727181Sun
737247Sat3
747251Sun
757317Sat4
76  
77
788August11Sun
79877Sat1
80881Sun
818147Sat2
828151Sun
838217Sat3
848221Sun
858287Sat4
868291Sun
87  
88
899September47Sat
90951Sun
919117Sat1
929121Sun
939187Sat2
949191Sun
959257Sat3
969261Sun
97  
98  
9910October27Sat
1001031Sun
1011097Sat1
10210101Sun
10310167Sat2
10410171Sun
10510237Sat3
10610241Sun
10710307Sat4
10810311Sun
109
110
11111November67Sat
1121171Sun
11311137Sat1
11411141Sun
11511207Sat2
11611211Sun
11711277Sat3
11811281Sun
119  
120
12112December47Sat
1221251Sun
12312117Sat1
12412121Sun
12512187Sat2
12612191Sun
12712257Sat3
12812261Sun
Call Out Table
Cell Formulas
RangeFormula
B3,B121,B111,B99,B89,B78,B67,B57,B45,B35,B25,B15B3=TEXT(DATE($A$1,A3,1),"MMMM")
C3,C121,C111,C99,C89,C78,C67,C57,C45,C35,C25,C15C3=IF(WEEKDAY(DATE($A$1,A3,1))=1,1,8-WEEKDAY(DATE($A$1,A3,1)))
D3:D10D3=WEEKDAY(DATE($A$1,A$3,C3))
C122:C128,C112:C118,C100:C106,C90:C96,C79:C85,C68:C74,C58:C64,C46:C52,C36:C42,C26:C32,C16:C22,C4:C10C4=IF(D3=7,C3+1,C3+6)
C11,C65C11=IF(IF(D10=7,C10+1,C10+6)<=DAY(EOMONTH(DATE($A$1,A$3,1),0)),IF(D10=7,C10+1,C10+6),"")
D76,D43,D11:D12D11=IF(C11="","",WEEKDAY(DATE($A$1,A$3,C11)))
C12C12=IF(C11="","",IF(IF(D11=7,C11+1,C11+6)<=DAY(EOMONTH(DATE($A$1,A$3,1),0)),IF(D11=7,C11+1,C11+6),""))
F5,F69,F59,F47,F37,F27,F17F5=COUNTIF(D4:D5,D3)
F7,F61,F49,F39,F29,F19F7=COUNTIF(D4:D7,D3)
F9,F63,F51,F41,F31,F21F9=COUNTIF(D4:D9,D3)
F11,F53F11=COUNTIF(D4:D11,D3)
C14C14=IF(C23="","",IF(IF(D23=7,C23+1,C23+6)<=DAY(EOMONTH(DATE($A$1,A$15,1),0)),IF(D23=7,C23+1,C23+6),""))
D14,D23D14=IF(C14="","",WEEKDAY(DATE($A$1,A$15,C14)))
D15:D22D15=WEEKDAY(DATE($A$1,A$15,C15))
C23C23=IF(IF(D22=7,C22+1,C22+6)<=DAY(EOMONTH(DATE($A$1,A$15,1),0)),IF(D22=7,C22+1,C22+6),"")
A111:A118,A108,A57:A64,A54,A15:A22A15=A3+1
D25:D32D25=WEEKDAY(DATE($A$1,A$25,C25))
C33C33=IF(IF(D32=7,C32+1,C32+6)<=DAY(EOMONTH(DATE($A$1,A$25,1),0)),IF(D32=7,C32+1,C32+6),"")
D33D33=IF(C33="","",WEEKDAY(DATE($A$1,A$25,C33)))
A121:A128,A99:A106,A67:A74,A45:A52,A35:A42,A25:A32A25=A15+1
D35:D42D35=WEEKDAY(DATE($A$1,A$35,C35))
C43C43=IF(IF(D42=7,C42+1,C42+6)<=DAY(EOMONTH(DATE($A$1,A$35,1),0)),IF(D42=7,C42+1,C42+6),"")
D45:D52D45=WEEKDAY(DATE($A$1,A$45,C45))
C53C53=IF(IF(D52=7,C52+1,C52+6)<=DAY(EOMONTH(DATE($A$1,A$45,1),0)),IF(D52=7,C52+1,C52+6),"")
D53:D54D53=IF(C53="","",WEEKDAY(DATE($A$1,A$45,C53)))
C54C54=IF(C53="","",IF(IF(D53=7,C53+1,C53+6)<=DAY(EOMONTH(DATE($A$1,A$45,1),0)),IF(D53=7,C53+1,C53+6),""))
A53,A107,A89:A96,A78:A86,A75A53=A42+1
C56C56=IF(C65="","",IF(IF(D65=7,C65+1,C65+6)<=DAY(EOMONTH(DATE($A$1,A$3,1),0)),IF(D65=7,C65+1,C65+6),""))
D56,D65D56=IF(C56="","",WEEKDAY(DATE($A$1,A$57,C56)))
D57:D64D57=WEEKDAY(DATE($A$1,A$57,C57))
D67:D75D67=WEEKDAY(DATE($A$1,A$67,C67))
C75C75=IF(IF(D74=7,C74+1,C74+6)<=DAY(EOMONTH(DATE($A$1,A$67,1),0)),IF(D74=7,C74+1,C74+6),"")
C76C76=IF(C75="","",IF(IF(D75=7,C75+1,C75+6)<=DAY(EOMONTH(DATE($A$1,A$67,1),0)),IF(D75=7,C75+1,C75+6),""))
F71F71=COUNTIF(D69:D71,D67)
F73F73=COUNTIF(D69:D73,D67)
F75F75=COUNTIF(D69:D75,D67)
D78:D85D78=WEEKDAY(DATE($A$1,A$78,C78))
C86C86=IF(IF(D85=7,C85+1,C85+6)<=DAY(EOMONTH(DATE($A$1,A$78,1),0)),IF(D85=7,C85+1,C85+6),"")
D86:D87D86=IF(C86="","",WEEKDAY(DATE($A$1,A$78,C86)))
C87C87=IF(C86="","",IF(IF(D86=7,C86+1,C86+6)<=DAY(EOMONTH(DATE($A$1,A$78,1),0)),IF(D86=7,C86+1,C86+6),""))
F79F79=COUNTIF(D78:D79,D79)
F81F81=COUNTIF(D79:D81,D79)
F83F83=COUNTIF(D78:D83,D79)
F85F85=COUNTIF(D78:D85,D79)
D89:D96D89=WEEKDAY(DATE($A$1,A$89,C89))
C97C97=IF(IF(D96=7,C96+1,C96+6)<=DAY(EOMONTH(DATE($A$1,A$89,1),0)),IF(D96=7,C96+1,C96+6),"")
D97:D98D97=IF(C97="","",WEEKDAY(DATE($A$1,A$89,C97)))
C98C98=IF(C97="","",IF(IF(D97=7,C97+1,C97+6)<=DAY(EOMONTH(DATE($A$1,A$89,1),0)),IF(D97=7,C97+1,C97+6),""))
D99:D106D99=WEEKDAY(DATE($A$1,A$99,C99))
C107C107=IF(IF(D106=7,C106+1,C106+6)<=DAY(EOMONTH(DATE($A$1,A$99,1),0)),IF(D106=7,C106+1,C106+6),"")
D107:D108D107=IF(C107="","",WEEKDAY(DATE($A$1,A$99,C107)))
C108C108=IF(C107="","",IF(IF(D107=7,C107+1,C107+6)<=DAY(EOMONTH(DATE($A$1,A$99,1),0)),IF(D107=7,C107+1,C107+6),""))
F91,F123,F113,F101F91=COUNTIF(D89,D89)
F93,F125,F115,F103F93=COUNTIF(D89:D91,D89)
F95,F127,F117,F105F95=COUNTIF(D89:D93,D89)
F107F107=COUNTIF(D99:D105,D99)
D111:D118D111=WEEKDAY(DATE($A$1,A$111,C111))
C119C119=IF(IF(D118=7,C118+1,C118+6)<=DAY(EOMONTH(DATE($A$1,A$111,1),0)),IF(D118=7,C118+1,C118+6),"")
D119D119=IF(C119="","",WEEKDAY(DATE($A$1,A$111,C119)))
D121:D128D121=WEEKDAY(DATE($A$1,A$121,C121))
 

Forum statistics

Threads
1,143,690
Messages
5,720,311
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