Excel Excel Challenge (Use Cell in a seprate formula)

[loftis01]

Having some issues with incorporating a cell value into an separate cell formula. The advantage is to change the value in order to access different data. For example

=SUM('[CM 050812P2P.xls]Inputs'!$B$3:$B$6)

The CM 050812P2P.xls is the file name. The worksheet accesses different files with the same name convention however the differnece is the date which is the value: 050812.

The next day would be 050912 or CM 050812P2P. Therefore as the cell changes value so would the formula resulting in accessing a different file.

Any suggestions would be appreciated.

 

[shaiyac]

Hi
I have aproblem with pivot chart:when i summarizing a column, i get "count" and it is OK,

Pivot Probs

but when i change it to "Sum" it give me "0"

 

[alow]

If you had the date in that format as the column header you could use the following:
=SUM(INDIRECT("'[CM "&A1&"P2P.xls]Inputs'!$B$3:$B$6"))

Where A1 is, you would reference the column heading with the date in the 6 digit format.

 

[alow]

For shaiyac, you should check the source data and make sure it is numeric. If you have numbers stored as text, excel won't sum them up in a pivot table, but will still be able to count them. To make sure, you can use the text to column wizard on the column of source data, then refresh the pivot table. If you need more help, I suggest creating a new thread.

 

[loftis01]

If you had the date in that format as the column header you could use the following:
=SUM(INDIRECT("'[CM "&A1&"P2P.xls]Inputs'!$B$3:$B$6"))

Where A1 is, you would reference the column heading with the date in the 6 digit format.

Thank you for looking at this issue. However it provides a #REF! once executed due to the file benig closed. The indirect formula is great as long as you have the other file open. I need the information access from a closed file.

 

[alow]

Once you have that formula in place, you can try a couple things. First, I would change the drive letter to use the UNC path (\\servername\folderpath). Next, if you're still getting a #REF error, update links in the file (Edit > Links, Update; or Data > Edit Links, Update if you're in a newer version) and see if that helps. As far as having a formula that can dynamically access a closed file, that doesn't really happen in excel. It basically takes snapshots of the file when you update the links. By default, these should update when you save or open the file, but if it's on a network drive, things get iffy. Also, if the files you're trying to access are in the same folder as the file with the formulas, you can try omitting the folders altogether and just using the file names. Let me know if this helps.