building an external link based on a cell value

I would like to build an external link in a spreadsheet 
that automatically changes based on a cell value.

For example, I have the following link:

=SUMIF('[Location Income Statements 2004.xls]Mar04'!
$14:$14,P5,'[Location Income Statements 2004.xls]Mar04'!
$18:$18)

Instead of Mar04 in the formula above, I would like to 
use a cell's contents.... cell A8 for example.

Is this possible?

Thanks,

Jason
0
anonymous (74722)
4/27/2004 3:00:13 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
410 Views

Similar Articles

[PageSpeed] 50

Jason

You can use the INDIRECT function.

=SUMIF(INDIRECT("'[Location Income Statements 2004.xls]" & A8 &
"'!$14:$14"),P5...

Indirect won't work on closed workbooks, so it's not suitable for every
situation.  If you need to work on closed workbooks, you'll either need to
hardcode the link or use a macro to change the formula whenever A8 changes.

-- 
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Jason" <anonymous@discussions.microsoft.com> wrote in message
news:4f9101c42c68$580ffc00$a101280a@phx.gbl...
> I would like to build an external link in a spreadsheet
> that automatically changes based on a cell value.
>
> For example, I have the following link:
>
> =SUMIF('[Location Income Statements 2004.xls]Mar04'!
> $14:$14,P5,'[Location Income Statements 2004.xls]Mar04'!
> $18:$18)
>
> Instead of Mar04 in the formula above, I would like to
> use a cell's contents.... cell A8 for example.
>
> Is this possible?
>
> Thanks,
>
> Jason


0
dickk (89)
4/27/2004 8:49:07 PM
"Dick Kusleika" wrote...
>You can use the INDIRECT function.
>
>=SUMIF(INDIRECT("'[Location Income Statements 2004.xls]" & A8 &
>"'!$14:$14"),P5...
>
>Indirect won't work on closed workbooks, so it's not suitable for every
>situation.  If you need to work on closed workbooks, you'll either need to
>hardcode the link or use a macro to change the formula whenever A8 changes.
...

Nope. There's a udf approach. See #4 in

http://www.google.com/groups?selm=hkQVb.2432%24_4.259%40www.newsranger.com

--
To top-post is human, to bottom-post and snip is sublime.
0
hrlngrv (1990)
4/27/2004 8:55:08 PM
Reply:

Similar Artilces:

Inserting extra info into a cell
I currently have three columns in a spreadsheet. This spreadsheet is used as a bilingual glossary of terms. The first column simply contains the following: ** Now the second column contains my Spanish words and the third column my English words. So, it looks something like this:- ** Hola Hello Now, I wish to import this data into some new software I have however, it requires me to put the word <Spanish> before every words in the second column and <English> before every word in the third column. So, in essence, it will be ** <Spanish>...

Add Value to the Chart
I have a chart done from the table shown below. However, I want to put the value (date) in addition of the values (X) and (Y) axis. So far, I have been doing it manually. I wonder if there is any way to it automatically. Thanks in advance. Maperalia. Date "X" "Y" 6/21/2005 0 0 7/6/2005 147 0.008 7/18/2005 197 0.012 8/5/2005 255 0.016 8/18/2005 289 0.016 9/8/2005 337 0.006 9/19/2005 360 0.017 How would you like the date to appear? As a data label on each point? then use one of these handy data labeling add-ins: Rob Bovey's Chart ...

Need to block email going to external email address from inside co
I have users sending info to unauthorized email address out side the company but to the best of my knowledge I have no way of blocking this. I know I can block emails going to an internal user but how do I block internal emails from going to external address? External being SMTP Enviroment is Win2003 Exchange 2003 OutLook 2000/2003 I don't have access to my exchange server at the moment so I can't check this but I think that you can filter the email domain within exchange server so that if any mail going to the external address is sent, it gets blocked before it leaves the ex...

How to select a sheet and input data into certain cells
I have 36 sheets, sheet1 is my menu, also on sheet1 I created in colum AB1=1stQTR AC1=2ndQtr AD1=3rdQTR AE1=4thQT AA2=2004 Sheet2 Sheet3 etc . . . AA3=2005 Sheet6 Sheet AA4=2006 Sheet8 Sheet AA5=2007 Sheet4 Sheet I would like to be able to select a year and a quarter and it goes to the sheet, i.e 2005 2ndQtr goes to sheet5 and in a couple of cells input 2005 and 1st Quarter I would also like to be able to print sheet5 and ask me if I want to print it agai or close and upon closing go back to sheet Is there an...

"implant" ActiveX ComboBox value into if eq'n
Morning all. Ok, I've got my combox active to where I can read the names in the list. I'd now like to have an if equation that will pull the value I select in the combobox. Do I have to write a secondary macro/function for that, or can I do it directly on the worksheet? Private Sub ComboBox1_Change() Select Case ComboBox1.Text Case "Item1" MsgBox ("Item1") Case "Item2" MsgBox ("Item2") Case "Item3" MsgBox ("Item3") Case "Item4" MsgBox ("It...

Hover values in chart
I have a line chart with a secondary vertical axis with values displayed on the right of the chart. When I hover over this secondary data line I get a message box that gives me the "series", "point" and "value". The "point" however is a number depending on how far you are from the left side of the chart. If I hover over the primary data line the "point" displays the value on the x-axis. There doesn't seem to be a way to specify the data sequence for the secondary x-axis. How do I get the secondary data line to show the "point" va...

Linking Price Lists to Products
I am having a problem linking products to a price list. I need to be able to import price lists from time to time, but it seems that I need to attach each product to a price list manually, one at a time. Is there a better way to do this? ...

constant values
I have a very simple question, which I am not able to resolve though. I have created a query, which combines records from two tables. Works nicely. Now I would need to add several columns with either a) fix values, which will never change b) values which apply for all records, but which I would like to enter within a form every time I would like to run the query. The background is that I will use access as a mapping tool, so an original file will be uploaded and result in a table with the same name always. The query now takes the mappings for another table and combines the ...

Null Value
When I try to enter a date on a form I keep getting the following error message; "You tried to assign the Null value to a variable that is not a Variant data type (Error 3162). The help for this error recommends declaring the variable. The only problem is I do not have any modules created for the form. What can I do to stop this error message from happening? The form has a code module. Perhaps you mean you have not created any code in the form's code module? If you used a wizard to create, say, a command button there will probably be code in the form's module, so ...

IF formula to round up values depending upon their outcome
I am using an IF formula to calculate between two cells, one is J (width) the other is K (length). Currently these formulas give an answer that then has to be rounded up based on the decimal place. I need the formula to also round up the amount to quarter increments. For example if the answer is 1.17 then the formula needs to make it 1.25, if it is 1.33 then the formula needs to make it 1.5, and finally if it is 1.63 then the formula needs to make it 1.75. So how do I add or make the formula round up to quarter increments? The formula that I am using is: =IF(J41<=3,K41/4,IF(J4...

Locking certain cells
I have an expense reporting template which I have locked certain cells that I do not want changed while allowing input into other cells. This is under the tools functions but its not coming to me right away how I did this. The users do not need to use any passwords --its just certain cells are read-only and cannot be changed unless unlocked. How'd I do this I need to update? Disregard- figured it out again "Scott" wrote: > I have an expense reporting template which I have locked certain cells that I > do not want changed while allowing input into other cells. Th...

IF number is grather than 100 color the cell red
I did this before but I can't remember how I did it. I want to have a column of numbers and if they are greater than a certain value ie: 120 , I want the sell to have the color red. Any ideas how to do this in Excel 2007? THanks Take a look at format|conditional formatting. Ron Boetger wrote: > > I did this before but I can't remember how I did it. I want to have a > column of numbers and if they are greater than a certain value ie: 120 > , I want the sell to have the color red. > > Any ideas how to do this in Excel 2007? > > THanks -- Dave Peterson ...

Hyperlink that does not link
I created a field called FileName in Access the type is hyperlink then I imported from Excel the list of thousand of file names such as “\\MAIN-SERVER\DOWN-SERVER\My_Documents.doc“ after the import succeeded then I clicked to open the file, however it does not work, it won't open the file. I tried an experiment and paste that file name in Word, Ctrl-Click does not open it. So, next I put the cursor to the end of File Name hit the Backspace button which invalidates the linkage, then I hit the Enter button after which Word made the File Name a linkage again. I do the Ctrl...

Writing to web-based database
I am trying to build VBA code that will write to a database locatedona web server. Database is MS Access now, but could use SQL Server orsomething else SQL based. How can I automate the process of opening,appending, and closing a database when the Office application is onthe local computer but the database is on a web server?...

Specifying next cell to "jump" to
When a user completes as much of a specified cell range as necessary an wishes to move on to the next range, I would like to give them a option to go "automatically" to the first cell of the next range a opposed to having to tab through the remainder of the range or click i the first cell of the range they wish to go to. For example, the firs range is A10 through D25. After inputting data in D15, the user want to go to the next range, the first cell of which is A30. How can th user most quickly and easily go from D15 to A30? I appreciate your help -- Message posted from http://...

"De-name" cell references in formulas
Hello, I'm working in Excel 2003 SP2, and I'm trying to replace all references to named ranges used within my formulas within a workbook with the underlying absolute cell references. I found a solution in the archives of this newsgroup ( http://groups.google.com/group/microsoft.public.excel/browse_frm/thread/4ebb9d1ee0076045/a0d6406155d03ccc#a0d6406155d03ccc ) , but I can't get it to work (neither the manual version nor the macro version). My only guess is that the behavior has of TransitionFormEntry has changed in Excel 2003. If so, is anyone aware of any other solutions. I found...

How to get one field to update based on a selection from a drop do
New to Access. I want to create a form that coworkers can use to lookup information in a database for inputting data into a PO as well as submit new records into the database. I want to have a control that is a combo drop down box where the can select a row from a list of items in a specified field and based on that selection it will update the below text box control with the corresponding data in the field next to it on the database. Make sense? Basically need to figure out how to update one control based on data selected from the drop down box control above it??? Private Sub...

Relative links in Publisher?
I'm trying the publish a web page in Publisher and it keeps adding file:\\\c: to all my relative links making them absolute and unusable. How do I turn this off or make Publisher have relative links? ...

Links do not work in junk mail folder
Dear readers: I've noticed that the hyperlinks do not work on the messages sent to my junk folder and also, the images are not available. If I un junk a message or drag it to my inbox, both of the former behavior is reversed. Why does this happen and how can I undo this. That's by design, as a security feture.=20 You've already found the solution -- moving the item to another folder.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 ...

Copying number to clipboard, subtracting 398 then pasting the value to overwrite the original
Hi. I am very new to this. I'd be really grateful if someone could help/guide me. I want to create a macro in Microsoft Word but I don't know visual basic. I want to be able to highlight a number then: - copy it to the clipboard - subtract 298 - paste the value to the Word document, overwriting the original text Hi John, There is no need to involve the clipboard if you are only changing the selected number. The following macro subtracts 298 from the selected number. Sub Subtract298() If IsNumeric(Selection.Text) Then Selection.Text = Val(Selecti...

Calculating Between Values
Hope you can help me! I have a column on one spreadsheet called "Platts Ports" Now, I want to analize column E. I want to be able to pick out all the values between 0 & 0 (>0 &<2). I then want to say the following: Where value = between 0-2 (COUNT) then add up column F where all these fields apply. Then divide COUNT(E) by the total of F( the sum we just calculated) To top this off, I need to do this from another sheet (within the same Spreadsheet) Hope you understand. Email me at RBotley@Gmail.com if you need me to priovide more info! Thanks in advance -- ...

Rounding up Time Values
Hi I am using the formula below to round time values up,in this case t the next five minute increment =(TIME(HOUR(BB14),CEILING(MINUTE(BB14),BB13),0)) BB13 = 5 BB14 = 16:00:02 This formula only rounds up to 16:05:02 if the time is 16:01:00 o greater,i would prefer it if the formula could make the time round u as soon as one second has passed, 16:00:01 and not when one minute ha passed. Can this be done Ad -- Message posted from http://www.ExcelForum.com What is in BB14? You can just use =CEILING(BB14,5/1440) will do what you want Or if there are dates as well you can just format...

How do I get my text to wrap in a single cell in exel?
I can't get all my text in a single cell, it starts across in to the cell next to it instead of starting a new text row below? Thanks Hi DJz, Select your cell and go to Format-->Cell. On the Alignment tab, check the checkbox labelled Wrap Text at the bottom of that dialogue window. HTH, Katherine "DJz" <DJz@discussions.microsoft.com> wrote in message news:97B925D0-FF0C-4CC9-AB23-9CB2D9AC2597@microsoft.com... > I can't get all my text in a single cell, it starts across in to the cell > next to it instead of starting a new text row below? Thanks Hi Kathe...

Lookup / Array Range / find the value
I have seen similiar examples but not that were not using exact matches to retrieve data. This seems simple and maybe I am over thinking but I have been unable to crack this nut. I have a table where the first tier sales runs between 0 and 20k and awards 1K commission. I want to be able to enter the salse result for various team members and obtain the correct commission amount based on their sales for the period. How should I complete the commission formula for this process? Sales Value Start Sales Value Stop Commission $0 $20,000 $1,000.00 $...

Finding cell text corresponding to the MAXimum figure in a spreadsheet?
Please can anyone help me with this... :) I've made a spreadsheet with the following attributes: Cells A1 to A100 contain a figure from 1-100 (a 'desirability' rating), sorted so that the highest figures are at the top of the spreadsheet and descend to the lowest figure in cell A100. Cells B1 to B100 contain the title of a CD album. Cells C1 to C100 contain the price of a CD album. I would like to make a formula that searches for the row that produces the highest result given from multiplying the figure in column A with the corresponding figure in column C and returns the co...