Adding Cell Values up to create an indirect reference to another sheet

arniebun

New Member
Joined
Jun 11, 2020
Messages
31
Office Version
  1. 365
Platform
  1. Windows
Hi was wondering if anyone can help, or if this is possible in excel,

so I have new files every day that will be created daily with this example file name, 20-09-2020-mysheet.csv.

in the sheet i want to reference to I have in A1 the dates formatted 20-09-2020, so what I want to do is create a dynamic reference to all my sheets like this example ='20-09-2020-mysheet.csv'!$B$2
so what i need to do is get 20-09-2020 and add it to -mysheet.csv'!$B$2 to create my reference to my sheets being produced daily. so I can copy this reference to auto reference the next 5 years. I would go in and do it daily, but I have a number of sheets I need to do this with and will defeat the object.

if you could help would be massively appreciated.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,958
Office Version
  1. 365
Platform
  1. Windows
From your example, it sounds like you need

=INDIRECT("'"&TEXT(A1,"dd-mm-yyyy")&"-mysheet.csv'!B2")
 
Solution

arniebun

New Member
Joined
Jun 11, 2020
Messages
31
Office Version
  1. 365
Platform
  1. Windows
thank you so much Jasonb75 that is perfect
 

Watch MrExcel Video

Forum statistics

Threads
1,122,832
Messages
5,598,361
Members
414,233
Latest member
WolverineNurse

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