Using a cell reference within a formula's file path?

E

eat_cress

New Member
Joined
Dec 5, 2010
Messages
37
Hi Guys,

I wonder if you lot will be able to help me with a problem I have.

I have a spreadsheet uses a formula in each cell which has a directory in it, as it does count from another spreadsheet and matches with the count criteria on the main spreadsheet. Basically it looks something like this:

=SUMPRODUCT(--(directory name\[25-01-2011.xls]Sheet1'!$H$1:$H$6000="whatever search criteria it is"))

I have to create a new spreadsheet of data each day, and name it accordingly (I name it the date for the day it is refering to). I then make a copy of the previous days tab on the main spreadsheet, and do a find and replace on the filename and change it to the new filename in all the formulas.

Basically, I want to know if there is a way I could have a cell on the main spreadsheet where I can just write in the new filename (always named the date.xls), and all the formulas in the spreadsheet reference that call (as the directory is always the same, so it will only be the filename part that changes) in the formulas, and so will automatically update the stats.

Thanks in advance.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
eat_cress said:
I want to know if there is a way I could have a cell on the main spreadsheet where I can just write in the new filename (always named the date.xls), and all the formulas in the spreadsheet reference that cell
Click to expand...

If as implied the target is closed (hence use of SUMPRODUCT) then no. Your Find & Replace method is the most logical.
(you could google INDIRECT.EXT but honestly I'd suggest you persist with your present approach)
 
Upvote 0
eat_cress said:
Hi Guys,

I wonder if you lot will be able to help me with a problem I have.

I have a spreadsheet uses a formula in each cell which has a directory in it, as it does count from another spreadsheet and matches with the count criteria on the main spreadsheet. Basically it looks something like this:

=SUMPRODUCT(--(directory name\[25-01-2011.xls]Sheet1'!$H$1:$H$6000="whatever search criteria it is"))

I have to create a new spreadsheet of data each day, and name it accordingly (I name it the date for the day it is refering to). I then make a copy of the previous days tab on the main spreadsheet, and do a find and replace on the filename and change it to the new filename in all the formulas.

Basically, I want to know if there is a way I could have a cell on the main spreadsheet where I can just write in the new filename (always named the date.xls), and all the formulas in the spreadsheet reference that call (as the directory is always the same, so it will only be the filename part that changes) in the formulas, and so will automatically update the stats.

Thanks in advance.
Click to expand...

If I'm understanding you correctly.
Try something like this,
=sumproduct(indirect("directory name...["&text(today(),"dd-mm-yyyy")&".xls]Sheet1'!$H$1:$H$6000="whatever search criteria it is"))
 
Upvote 0
I am still having problems with this. Thankyou for your responses none the less. I am sure it is a mixture of my bad communication skills, and general noobishness.

Basically, I just want there to be a cell on the spreadsheet where I can wriite the file name, and all formulas on the sheet will reference that filename. The forumlas themselves will have the beginning part of the file path in them. It is just the filename that will vary.

In a perfect world, it would look something like this:

=SUMPRODUCT(--('c:\data\["C$5$".xls]Sheet1'!$H$1:$H$6000="14"))

Obviously the above formula would not work, but I would like to know how I would write that so that it would work (if possible).

I am thinking it may involve the INDIRECT function, but I am having problems getting it to work how I want. Just always getting the#REF error.

I hope this clears things up a bit.

thanks in advance, and sorry for my dumbness on this.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
VBA Challenge
Replies
2
Views
262
SnowyBry
S
M
  • Solved
Ignore Row with Text Using VLOOKUP
Replies
7
Views
407
Alex Blakenburg
A
M
  • Solved
Alternative to COUNTIFS for a constantly growing database
Replies
2
Views
307
multifidus
M
O
  • Question
Count only one occurrence of all criteria being met
Replies
8
Views
488
KRice
K
V
  • Question
Copy data from one spreadsheet to another with certain criteria it meets using VBS
Replies
0
Views
113
VashTstmpe
V

Forum statistics

Threads
1,215,372
Messages
6,124,532
Members
449,169
Latest member
mm424

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