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!
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!
