Dynamic Formula

M

Mengli

New Member
Joined
May 24, 2024
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I have a set of excel docs, one for each year that are emailed out by leadership. The file name is altered each week with the MMDDYY in the file name showing the week ending date. I am trying to make a lookup file to pull the relevant numbers.

I'd like to create a link that I can drag but I am proving unable to get it to work. I really would like to avoid renaming each file individually or opening each file and creating links for relevant cells individually.

2024 Hours Review.xlsx
BCDEFGHIJKLMNOP
1122724010325011025011725012425013125020725021425022125022825030725031425032125
2_week 01_week 02_week 03_week 04_week 05_week 06_week 07_week 08_week 09_week 10_week 11_week 12_week 13
3
4
5Q1
6JANJANJANJANFEBFEBFEBFEBMARMARMARMARMAR
7BranchCategory12/271/31/101/171/241/312/72/142/212/283/73/143/21
810160ADJ BGT ST HRS'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_122724_week 01.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_010325_week 02.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_011025_week 03.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_011725_week 04.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_012425_week 05.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_013125_week 06.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_020725_week 07.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_021425_week 08.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_022125_week 09.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_022825_week 10.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_030725_week 11.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_031425_week 12.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_032125_week 13.xlsx]Hours Performance LIS Adj'!E$158
910164ADJ BGT ST HRS'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_122724_week 01.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_010325_week 02.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_011025_week 03.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_011725_week 04.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_012425_week 05.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_013125_week 06.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_020725_week 07.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_021425_week 08.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_022125_week 09.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_022825_week 10.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_030725_week 11.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_031425_week 12.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_032125_week 13.xlsx]Hours Performance LIS Adj'!E$164
Adj Hrs
Cell Formulas
RangeFormula
D1:P1D1=TEXT(D7,"mmddyy")
E7:P7E7=D7+7
D8:P8D8="'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_"&D$1&D$2&".xlsx]Hours Performance LIS Adj'!E$158"
D9:P9D9="'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_"&D$1&D$2&".xlsx]Hours Performance LIS Adj'!E$164"
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Try D1=TEXT(D6,"mmddyy"). Next D6 must contain a date value, not the text "JAN". Try 1/1/24 and you should get "010124" as text.
 
Upvote 0
If I change D1 to be as value and not a formula, the formula in D6:D7 still doesn't work. It writes everything out as a string and doesn't process the formula.

2024 Hours Review.xlsx
BCDEFGHIJKLMNOP
2_week 01_week 02_week 03_week 04_week 05_week 06_week 07_week 08_week 09_week 10_week 11_week 12_week 13
3Q1
4JANJANJANJANFEBFEBFEBFEBMARMARMARMARMAR
5BranchCategory12/271/31/101/171/241/312/72/142/212/283/73/143/21
610160ADJ BGT ST HRS'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_122724_week 01.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_010325_week 02.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_011025_week 03.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_011725_week 04.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_012425_week 05.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_013125_week 06.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_020725_week 07.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_021425_week 08.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_022125_week 09.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_022825_week 10.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_030725_week 11.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_031425_week 12.xlsx]Hours Performance LIS Adj'!E$158'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_032125_week 13.xlsx]Hours Performance LIS Adj'!E$158
710164ADJ BGT ST HRS'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_122724_week 01.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_010325_week 02.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_011025_week 03.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_011725_week 04.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_012425_week 05.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_013125_week 06.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_020725_week 07.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_021425_week 08.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_022125_week 09.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_022825_week 10.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_030725_week 11.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_031425_week 12.xlsx]Hours Performance LIS Adj'!E$164'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_032125_week 13.xlsx]Hours Performance LIS Adj'!E$164
Adj Hrs
Cell Formulas
RangeFormula
D6:P6D6="'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_"&D$1&D$2&".xlsx]Hours Performance LIS Adj'!E$158"
D7:P7D7="'https://my.sharepoint.com/personal/William_Wallace_com/Documents/01 - Docs/Annuals/2024/DNE/Budget/01 - NB Hours/[NO_BILL_HRS_COMPARISON_REPORT_DETAIL_2024_thru_"&D$1&D$2&".xlsx]Hours Performance LIS Adj'!E$164"
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Copied sheet code to new file and lost some functionality....not sure how/why
Replies
5
Views
125
MeaclH
M
K
  • Question Question
Looking for assistance using MS Excel VBA to hide rows
Replies
4
Views
520
kcampbell675
K
P
  • Question Question
Problems applying formula to Excel Sharepoint File
Replies
1
Views
643
Fluff
Fluff
M
  • Question Question
Auto adjust row heights based on specific column
Replies
1
Views
419
XLfriend777
XLfriend777
F
  • Question Question
SUMIFS help
Replies
6
Views
523
Alex Blakenburg
A

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

Share this page

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
Back
Top