Dynamic filename referencing for small calculations

patmcroo

New Member
Joined
Nov 6, 2017
Messages
1
Hello, first post here-

I am trying to streamline the set up for a worksheet which I reference every day.
Background info- The goal of the spreadsheet (Main) is to reference other worksheets (Project), which are stored on our local server, and pull data from those project worksheets to display on the main worksheet. This information would include information such as date of last site visit (using the MAX() function from an array of dates) and basic cell references to pull single values from the project worksheet to the main worksheet. In some cases I need to perform simple equations from data in the project worksheets, such as subtracting one cell value from another and displaying the result in the main.


The main worksheet is simple enough and I have it set up and working however I am trying to streamline the process of adding new projects. Each project in my main worksheet is a listed on a different row, with several columns with various information pulled from the project worksheet. I am trying to streamline the process of adding a new project by adding a new column to list the project worksheet filename which is standardized across our server so referencing it would be easy. Once I have the filename cell for each project, I should be able to use the same formulas for each project and reference the file name cell in the equation. Currently I am writing each formula individually because I have not been able to reference a filename within a cell equation. See my example below:


Say within my main worksheet I have set up a new project and I am trying to find the sum of some values within the project worksheet. The project worksheets are all set up the same so I should be able to use the same formulas with different filenames for each project worksheet.

If row 4 is set up for 'project y' I would like to place the file name for project y in say cell K4.
so K4 would read something like '\\Server\_Projects\2016\project y\[project y.xlsm]

now in cell D4 I am trying to pull and manipulate data from project y.xlsm

currently I would have to hand write (copy and edit from another project) the equation that looks something like this:

=SUM('\\Server\_Projects\2016\project y\[project y.xlsm]Inspection Summary'!$O$5,'\\Server\_Projects\2016\project y\[project y.xlsm]Inspection Summary'!$V$5,'\\Server\_Projects\2016\project y\[project y.xlsm]Inspection Summary'!$AC$5)

which isn't the end of the world but there are several columns so adding projects can be time consuming.
I think I could make it easier by creating a standardized formula and instead of using the file location, I could reference a cell on the same row which has the file location in it. The new formula would read something like:

=SUM(K4\Inspection Summary'!$O$5,K4\Inspection Summary'!$V$5,K4\Inspection Summary'!$AC$5)

Im thinking Id have to parse the K4 cell reference with something like TEXT[K4] each time or something like that but I have tried that and cannot get it to work.

l am currently looking into the TRIM(getValue()) function and how it could work in this application however I cannot seem to get that to work either.

Any input would be much appreciated.

Thanks!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
To reference the contents another cell for use in a formula like that, you would use INDIRECT().

However INDIRECT only works on open workbooks (the source must be open). Another possible option would b to use the Morefunc add-in, which allows INDIRECT to use closed workbooks. Find it here
http://www.ashishmathur.com/tag/morefunc/
 

Watch MrExcel Video

Forum statistics

Threads
1,129,696
Messages
5,637,862
Members
416,986
Latest member
zmartee

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