User-inserted external file path used to pull cell values

I have an organization that wants each vendor to fill out and submit a
net present value worksheet as part of the RFP process.  Call it the
NPV workbook.  The organization wants to then pull data from each of
those worksheets into a summary worksheet.

I would like for the organization to be able to input the file path for
each NPV workbook.  Then the summary cells would use the file path to
pull the data.

What I have been trying so far isn't working:
1) Organization creates a hyperlink to each NPV workbook; example: Cell
B8 contains the hyperlinked string: F:\Emergent\Projects\03109 GSA
SIS\SIS Model 2.xls
2) Use CONCATENATE function to create command; example:
=CONCATENATE(B8,"\[SIS Model 2.xls]Offeror Worksheet'!$E$14")

What I get on step 2 is a well-formed string:
='F:\Emergent\Projects\03109 GSA SIS\[SIS Model 2.xls]Offeror
Worksheet'!$E$14

How do I get that string to execute as a command, as if I had typed
that exact string into the cell myself, rather than using the
concatenate formula to create it?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

0
9/18/2003 6:32:10 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
509 Views

Similar Articles

[PageSpeed] 20

The INDIRECT function will do that, but only if the other workbook is open.
Probably not what you want.  You can create a macro that will build the
formula for you and put in the cells.  Post back if a macro interests you.

-- 
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"beerheart" <beerheart.tysoa@excelforum.com> wrote in message
news:beerheart.tysoa@excelforum.com...
> I have an organization that wants each vendor to fill out and submit a
> net present value worksheet as part of the RFP process.  Call it the
> NPV workbook.  The organization wants to then pull data from each of
> those worksheets into a summary worksheet.
>
> I would like for the organization to be able to input the file path for
> each NPV workbook.  Then the summary cells would use the file path to
> pull the data.
>
> What I have been trying so far isn't working:
> 1) Organization creates a hyperlink to each NPV workbook; example: Cell
> B8 contains the hyperlinked string: F:\Emergent\Projects\03109 GSA
> SIS\SIS Model 2.xls
> 2) Use CONCATENATE function to create command; example:
> =CONCATENATE(B8,"\[SIS Model 2.xls]Offeror Worksheet'!$E$14")
>
> What I get on step 2 is a well-formed string:
> ='F:\Emergent\Projects\03109 GSA SIS\[SIS Model 2.xls]Offeror
> Worksheet'!$E$14
>
> How do I get that string to execute as a command, as if I had typed
> that exact string into the cell myself, rather than using the
> concatenate formula to create it?
>
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~ View and post usenet messages directly from http://www.ExcelForum.com/
>


0
dickk8725 (4)
9/18/2003 9:05:02 PM
Reply:

Similar Artilces:

UI Data Validation using XML Schema
Has anybody done UI data validation usinf XML Schemas. Any pointers will help. Thanks! ...

Excel 07 selects 3 cells when I click on 1
Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an empty cell 2 or more below it are selected and the typing goes on the bottom row. I just want to select the cell I click on. Thanks Are the cells merged?? I don't like the 2007 either! If i could i would have the look and feel of 2003 on the engine of 2007... :-( On Thu, 1 Nov 2007 20:21:01 -0700, Brian <Brian@discussions.microsoft.com> wrote: >Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an >empty cell 2 or more below it are selected and the typing goes on the botto...

how to use the type member in System.Web.Services.Description
hi all, I want to parse the wsdl file . It is ease to parse the parts of wsdl except that the part of types. Because I don't know how to use the System.Web.Services.Description member types . I have searched the MSDN to find answer , but , it tell me very less. So I come here for help. For example, there is a types is defined as follows: - <types> - <s:schema elementFormDefault="qualified" targetNamespace="http://www.webservicex.net"> - <s:element name="GetGeoIP"> - <s:complexType> - <s:sequence> <s:element minOccurs="0...

Change paths to linked external data
Hello. I have a database that has 3 external links in it: 1 is to a table in another Access Database. One is to an excel spread sheet. And, the third is to a .csv file. Is there a way in VBA to change the paths to these? Basically, i want to set up a form where the user can specify the new paths in text boxes if any of these files are moved. Anybody help me out? (Access 2007) cyb3rwolf wrote: >Hello. I have a database that has 3 external links in it: 1 is to a table >in another Access Database. One is to an excel spread sheet. And, the third >is to a .csv...

Excel 2003/edit/replace. Under Options/Look In, values is AWOL bu.
Under Look In options Find allows for search by formulae OR values OR comments Replace is restricted to formulaes ONLY WHY? cp Have a look at this google search result. This topic came up a couple days ago and was addressed by Dave and Myrna. http://snipurl.com/cd9j Gord Dibben Excel MVP On Fri, 28 Jan 2005 03:49:02 -0800, "cp" <cp@discussions.microsoft.com> wrote: >Under Look In options >Find allows for search by formulae OR values OR comments >Replace is restricted to formulaes ONLY >WHY? ...

trouble with copying a cell to other cells.
Info adds +1 (so 3oo goes to 301) and in the same function, 2 stays a 2 when copied to another cell. How do I mark what changes and what doesn't? I found the info posted on a different post. by BenjieLop for Copying a formula 9/20/2004 I want to copy a formula from one cell to several others, it is a basic formula =c5*d12 when i copy and paste the formula changes..( =c6*d13 and so on...) I want the second part d12 to change but i want to anchor the first part somehow so the c5 is constant in every formula c5*d13 c5*d14 etc.. The formula =$C$5*D12 will do the trick ...

Can anyone send me the Microsoft.Crm.Platform.Proxy.dll and Microsoft.Crm.Platform.Types.dll files ?
Hi, I'm stucked without those files, and I don't have the instalation disk. Can anyone send me those files ? Thanks in advance, Eddie. You can download the program from Microsoft. http://www.microsoft.com/office/onenote/prodinfo/trial.mspx#EEAA Sorry-wrong group Eddie, email me and i will send them over -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Eddie" <Eddie@discussions.microsoft.com> wrote in message news:epJ2yF3NFHA.3076@tk2msftngp13.phx.gbl... > Hi, > > I'm stucked without those files...

Paste Several Values into one Cell
Hi all. I have data in several cells (I2:GJ2) on one row. I want to copy all of this data into one cell as it appears now (in a particular sequence). Is this possible? Thx in advance. Not sure if this is what you want but to concatenate data from several cells into 1 cell, use formula =Concatenate(I2," ",H2," ",G2) just add more cell references with commas and " " to give a space between each cell value for all the cells you want. "sgarrett" <anonymous@discussions.microsoft.com> wrote in message news:110F5183-D1CE-4C5A-A7F8-90D01E500FBE@micr...

excel file can't be opened #2
hi,I'm cho I have a recent excel file (about 1 month),but since two days ago,the file can't be opened completely or always not responding. What should I do so that I can use this important file. Any help would be appreciated regards, cho ...

How to transpose in excel file
Hi I would like to make a transpose like this? From House Defect How much #1 Broken Sinks 1000 #1 Bad Paint 2000 #1 Bad Lighting 1000 #2 Broken Sinks 0 #2 Bad Paint 2000 #2 Bad Lighting 100 and Transpose into House Broken Sinks Bad Paint Bad Lighting #1 1000 2000 1000 #2 0 2000 100 A Pivot Table will do that and more. Set House as row field, Defect as column field and How much as data. HTH. Best wishes Harald "vilfood" <vilfood@d...

MBF file won't open...am I hosed???
In my previous computer I had a flaky hard drive. While it still worked I backed up all my important files to CD's and DVD's. Since I dutifully backed up my MNY file (to two locations) each time I used my Money 2001 I didn't think to copy my MNY file I merely copied the MBF files from those locations. Once, I had to use that an MBF file as my MNY file was corrupt and it worked flawlessly. I saw no reason why it shouldn't do so again. Plus I was in panic mode to get all the data possible from the dying HD. Now that I have a new computer I installed my copy of MM 2001 and wh...

Outlook 2003 attach File Browse Window Stops Responding
In outlook 2003 when I try to attach a file and the browse window pops open to browse for the file I want to attach the window will stop responding and I have to force quit the outlook. Any Ideas? do you have mapped network drives? how long do you wait before quitting outlook? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipsti...

Can't restor backup file, "Please insert the next backup disk.....
I have money 2005 backup file (.mbf) and my original file has gotten corrupted somehow and can't repair it (computer hung and had to be restarted). Whenever I try to restor the backup file, I get the following message, Window Title: Money Multiple Disk Backup Message: Please insert the next backup disk. (The disk containing file "".) I was backing up on a different connected single harddrive and there is only one file. The file is not in multiple pieces and on different drives. When I click OK, it doesn't do anything and cancel just stops the process. I even down...

Bulk Import Wizard for Leads Issue
Hi, I am trying to import Lead records using a CSV file containing around 3,000 records. I have tested with around 20 records (taken from the larger file) and it imports fine but when I select the the 3,000 record file for import the wizard gives an error message somthing like (sorry don't have exact wording) 'Source Import file is corrupt'. Are there any known issues with CSV files such as the following : 1) Special characters such as an asterix not handled 2) Email and Website type fields must all be correctly formatted? 3) File is too large Thanks for your help in adva...

Outlook user profiles
I set-up two different user profiles in outlook. I am not able to change the settings in one user profile under Tools/options/mailset-up without it changing the settings on the other user profile. I need to apply individual settings for each. Anyone know how to remedy this? I have tried everything! Which settings are youi trying to change? -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "gia" <anonymous@discussions.microsoft.com> wrot...

portfolio value over time
I run Money 2004 and i can't do some things i think are really basic - main two (related issues) that bother me are: 1) is there any way to view the value of my entire investment portfolio over time (e.g. as a chart showing basically just price * volume of shares i own for each investment, combined to show all investments together)? 2) is there any way to view price history for all my holdings as an indexed value (0% at the start growing up or down based on % change in price) as opposed to absolute value? Some shares are $20 each some are $200 each - doesn't make sense to sho...

Report Error
Hi all, any ideas on this.... We have created a number of Quotation Reports that work fine for all users except one ! When she runs the report she gets an error saying the sub report can be found. I have checked that she is in the same CRM groups as other users and checked that she is in the Reporting group in Windows/SQL - all looks fine. Any help would be appreciated Thanks Ian Hi Ian, Does she have problem with this report only or this happens to her other reports as well? Darren Liu, Microsoft CRM MVP http://www.crowecrm.com On Nov 7, 5:29 am, "Ian Kelsall" <...

determining user-defined chart definition
How can I look at the code/description of a user-defined chart? ...

incremental counter for dup number values
my wife, accountant, is very often bringing home work where she wants to match offsetting records from two record sets by value only. However, where one set may have 10 instances of $25, the other may only have 2 instances of $25. How can I effectively limit the pick of only two records from the set with 10 dups? ...

sum values from range of cells if cells correcponding have the sam
hiya, i have a small project and i've spent too much time on this already - can't figure it out myself. What i need to do is to create a formula which will find the same values in column A and will sum the corresponding to them values from columns B:E. this is a sort of result i want to get: A B C D E F RESULT: 1 A1 1 5 2 3 A1 31 2 B5 2 6 3 3 A2 19 3 C1 3 7 4 3 B1 22 4 A1 4 8 5 3 B5 37 5 B5 5 9 6 3 C1 33 6 C2 6 1 6 3 C2 16 7 A2 7 2 7 3 8 B1 8 3 8 3 9 C1 9 4 0 3 I would appreciate any ideas how to do this. Thanks and regards, Assume in G1 down are the unique item...

How do you check out a file from a link
I have received a link to an excel file stored in a SharePoint library. When I open the file the option to 'check out the file' does not appear at the top of my screen. This function does work for a word document in the same library. Please can you advise. Thanks With your browser, go to the Library where the document is located and check out from there. --rms www.rmschneider.com On 16/04/10 15:44, Tracey T wrote: > I have received a link to an excel file stored in a SharePoint library. When > I open the file the option to 'check out the fi...

Spreadsheet name in a Cell
I am looking for a cell formula that would recover the spreadsheet name and display it in a cell - more or less as "=CELL("row";A1) function is able to return the line number of cell A1 or as "=CELL("filename";C18)" will return the full path of the Excel workbook housing cell C18... Thanks a lot - Alain79 See http://www.xldynamic.com/source/xld.xlFAQ0002.html -- HTH Bob Phillips "Alain79" <desaivresanospam@free.fr> wrote in message news:ddsce3$94f$1@s1.news.oleane.net... > I am looking for a cell formula that would recover the spre...

how do I insert rows without changing formula
In a cashflow worksheet how can I add rows without having to re-do all the proceeding formulas. use absolute references e.g. =$A$5 -- HTH RP (remove nothere from the email address if mailing direct) "tinka" <tinka@discussions.microsoft.com> wrote in message news:4ED4569E-0FD6-469B-82B1-F30139B1A280@microsoft.com... > In a cashflow worksheet how can I add rows without having to re-do all the > proceeding formulas. ...

Proper use of categories in invoicing?
I am a freelance web developer. I use Money to print and track my invoicing. Being a freelancer, I often have a wide variety of clients: there is a continual influx of new clients, as well as a stable of regular clients. The regular clients often bring their own clients with them, and in this case, my role is only to develop the site. As a result, I will often send out 10 or 20 invoices to one of these regular clients over the course of a year, covering work that I may have done on 20 or 30 sites. As it is now, I can sort for invoices to the REGULAR CLIENTS, but I cannot search/sort for i...

How can a value be converted to words
Is there a fromula if I have a total i.e. 123 to be converted in words i.e. One hundrd and twenty three automatically. http://support.microsoft.com/default.aspx?scid=kb;en-us;213360 or http://www.xldynamic.com/source/xld.xlFAQ0004.html tj "Azar" wrote: > Is there a fromula if I have a total i.e. 123 to be converted in words i.e. > One hundrd and twenty three automatically. ...