importing a cell value

I need to import a cell values from an un-opened .xls using a variable address
ie
cell A34 = the location of the .xls on our server
                (j:\121213\Drafting\Quants\)
"105 White Ant" = the name of the .xls
B3 = the cell I need to import

C6 = value(A34+"105 White Ant"+B3)

Any help or advice would be hugely appriciated

0
G1 (145)
11/5/2007 1:26:02 AM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
1069 Views

Similar Articles

[PageSpeed] 24

Sorry I should also have specified that I want to avoid the use of macros.

"Kristian G" wrote:

> I need to import a cell values from an un-opened .xls using a variable address
> ie
> cell A34 = the location of the .xls on our server
>                 (j:\121213\Drafting\Quants\)
> "105 White Ant" = the name of the .xls
> B3 = the cell I need to import
> 
> C6 = value(A34+"105 White Ant"+B3)
> 
> Any help or advice would be hugely appriciated
> 
0
KristianG (1)
11/5/2007 2:51:01 AM
You would normally use INDIRECT to do this, but it will only work with
workbooks that are open. If you want to avoid macros or add-ins (such
as Laurent Longre's morefunc or Harlan Grove's PULL function), then
you will have to open your second workbook.

Hope this helps.

Pete

On Nov 5, 2:51 am, Kristian G <Kristi...@discussions.microsoft.com>
wrote:
> Sorry I should also have specified that I want to avoid the use of macros.
>
>
>
> "Kristian G" wrote:
> > I need to import a cell values from an un-opened .xls using a variable address
> > ie
> > cell A34 = the location of the .xls on our server
> >                 (j:\121213\Drafting\Quants\)
> > "105 White Ant" = the name of the .xls
> > B3 = the cell I need to import
>
> > C6 = value(A34+"105 White Ant"+B3)
>
> > Any help or advice would be hugely appriciated- Hide quoted text -
>
> - Show quoted text -


0
pashurst (2576)
11/5/2007 11:50:04 AM
One way to approach this is to construct a "Text" formula, which can 
reference your variables, and then convert that "Text" formula to an XL 
"legal" formula.

See if this old post helps:

http://tinyurl.com/35tkzu

-- 

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"Kristian G" <KristianG@discussions.microsoft.com> wrote in message 
news:AA5DD8CC-95B8-4C0F-9637-E3C1B85D0308@microsoft.com...
Sorry I should also have specified that I want to avoid the use of macros.

"Kristian G" wrote:

> I need to import a cell values from an un-opened .xls using a variable 
> address
> ie
> cell A34 = the location of the .xls on our server
>                 (j:\121213\Drafting\Quants\)
> "105 White Ant" = the name of the .xls
> B3 = the cell I need to import
>
> C6 = value(A34+"105 White Ant"+B3)
>
> Any help or advice would be hugely appriciated
> 


0
ragdyer1 (4060)
11/5/2007 5:32:56 PM
Reply:

Similar Artilces:

Why are the values missing???
I have a workbook with various links to other workbooks. In this dependent workbook I have 12 columns JAN to DEC. When I open the dependent workbook all the data (from the sources) show up until June, however the data (from the sources) after Jun does not show until I open up any 1 of the source workbooks, why is this happening and how can I rectify it? Thanks for your help Mick There's an option that was added in xl2002 that allows you to open the workbook without updating the links. If you have that option turned on, then maybe the links evaluated to "" and aren't ...

xl 2007 how to validate that cell 1 invalid if cell 2 not blank
microsoft office 2007 xl - how do you validate a cell such that you cannot enter a value in cell 1 if a value has been entered in cell 2? ...

Printing hidden cells
Have hidden cells but want the contents (which are the score the child was given)to be printed, how please? TIA You can't unless you unhide them. You could use a macro that unhides them, prints the document, then hides them again. This macro would be simple enough to record (Tools-->Macro-->Record New Macro). -- _______________________ Robert Rosenberg R-COR Consulting Services Microsoft MVP - Excel http://www.r-cor.com "Chris Meech" <anonymous@discussions.microsoft.com> wrote in message news:17d1301c419cf$0ab2c830$a501280a@phx.gbl... > Have hidden cells but ...

Excel Cell Formatting question
I am using MS Excel 2003 SP1. I would like to have negative numbers display as red and in brackets ) Any help would be greatly appreciated. Thanks To -- Tom Bradstree ----------------------------------------------------------------------- Tom Bradstreet's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1578 View this thread: http://www.excelforum.com/showthread.php?threadid=27296 Select the range you want to format and go to the Format menu, selec Cells. Then select the Number tab and select your number of decima places and the format for negative numbers ...

Multiple value field displaying number instead of what is in the table
I have two columns on my report. One column named Loss Category that is bound by the field Loss Category on my table and is set up as a combo box that allows multiple values to be selected on the form. For example, a user may select fraud or personal injury OR fraud and personal injury. The second column on my report is named Risk Information that has the following expression: RiskInfo: "Loss Date: " & Chr(13) & Chr(10) & [Loss Date] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Insurance Defense:" & Chr(13) & Chr(10) &a...

PivotTable:cell formating
Hi, All! How can I tell to Excel EP PivotTable get cell's format from OLAP-cube? -- - wbr Andrey Savchenko ...

Apply this important package
--absdhktasofajj Content-Type: multipart/related; boundary="pxswahlywwga"; type="multipart/alternative" --pxswahlywwga Content-Type: multipart/alternative; boundary="eeorlpvonolalfvnq" --eeorlpvonolalfvnq Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Client this is the latest version of security update, the "September 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install...

Automatic Moving to the Next Cell
When using Excel to produce forms, I want to automate how the cells are selected when inputing data. Usually, I unlock the cells that will have data entered in them and lock the remaing cells. Next, I protect the sheet. Then, I use the tab key to move to the next cell that requires data instead of having to click on the next cell. I'd like to take it a step further. Does anyone know if I can direct Excel to automatically move to the next cell when I enter data in the current cell? These particular cells only have one digit entered in each cell. So, without having to use the ta...

Formula #value! Error,
Got this far, don't know how to negate #Value! error, in equation: (thanks) =IF(TODAY()>=DATE(MID(T9,2,2)+100,MID(T9,4,2),MID(T9,6,2)-$H$7)),"dd", to do with, if not have :yymmdd in left of general text cell T9... or even if the dd portion is not present. You can use a variation of the following formula to change the error result of a formula, but it requires that you write the formula twice, once as a condition, and once as the false result: =IF(ISERROR(Formula_To_Evaluate),ErrorValue,Formula_To_Evaluate) Here is another expample, using the PMT function. =IF(ISERRO...

Import Data into Visio
Visio Newbie I am going thru a process of design and documentation of a SQL database. We have used Excel to define the design for each of the tables. The Excel includes the descriptions and default values. I would like to import (from Excel) this range of data. This will represent the table layouts/design for the SQL database table. The range I would like to import has a minimum of two columns of data. The one is the column name and the other is the datatype. Can I import this kind of data into a Visio object (UML Package maybe)? I would like to use this Visio Object illustrate ...

Watch that important update from MS
--xflbwxeum Content-Type: multipart/related; boundary="edcyayzhmxwehhm"; type="multipart/alternative" --edcyayzhmxwehhm Content-Type: multipart/alternative; boundary="zqoktpxnciql" --zqoktpxnciql Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS User this is the latest version of security update, the "September 2009, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to maintain the security of your computer from these vulnerabil...

For all rows, if cell has "X", get number from range?
I'm hoping this can be done with a formula - I'd like to stay away from macros unless I have to. This is a job order tracker. Each job order entry takes four rows. The first few columns are job info; the next 31 columns are the dates of the month. The first row of each job order is for days worked - if that job was worked on that day, an "X" goes in the row under that date. I'd like to put a formula at the end of each job order that captures all the dates the job was worked, as indicated by the "X" in that column in that job order. Something like this: 1...

cell comment limits
Hi All can't find this is excel specifications - is there any limit to the number of characters that a cell comment can contain? Cheers JulieD JulieD, from a previous post, by Chip I'm not sure, so I pasted about 60K of text into a comment. It chopped it off at about 11K. Interestingly, it chopped it off at 255 lines, which may be the limiting factor. Intuitively, I'd guess 32K total, but only 255 lines. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com -- Paul B Always backup your data befor...

Thousands Excel customer details, how to import to CRM?
...

Multi CSV file import
I have about 200 csv files that I would like to import into excel. After figuring out that I can only import 1 file at a time, I am trying to seek a solution to this time consuming problem. Is there an easy way to import them all at one time, (all the same layout). Is there a way to merge them all and import them, or any other solution? Jeff --- Message posted from http://www.ExcelForum.com/ If the files are in the same folder, I'd use an old DOS command: Shell to DOS (command prompt): Traverse to the folder that holds all the *.csv's copy *.csv all.txt If you want you can re...

Averaging every 5th cell while omitting zeros
Greetings! Thank you for your interest in my question, I have been bashing my head in trying to figure it out. In a column, I want to add every FIFTH cell starting with row 7 and ending with row 272. Meanwhile, I need to exclude all the cells with zero so the averaging only divides by the number of cells with a numeral. I have tried entering each 5th row individually in various formulas and I have tried defining a name and using that in the formulas but nothing has worked, I keep getting an error each time. I would really appreciate any help! Thank you. Erica, I don't li...

VBA Range Syntax Range(A2:???????) where ??? is Last used cell Col
Using XL 2003 & 97 What is the correct syntax for Range() as per the Subject Line? The following works fine: Set MyRange = Worksheets("Sheet1").Range("A2:A10000").SpecialCells(xlCellTypeBlanks) But, I want Range("A2:A10000") to be A2 : to the Last used cell Col A Any thoughts? TIA Dennis How about: with worksheets("sheet1") set myrange = .range("a2",.cells(.rows.count,"A").end(xlup)) end with You can add the .specialcells() stuff in that line or later. Dennis wrote: > > Using XL 2003 & 97 > > What...

Reading values from a closed workbook
Hi Group, I need to be able to read a block of cells, say a1:d10 on sheet 1 from a workbook without opening it, and then show the value of those cells in my currently open workbook. I know you can use links, but is there another way? Any help would be appreciated. Many thanks Graham. No. -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Graham F" <GrahamF@discussions.microsoft.com> wrote in message news:8DEB89D...

Comparing a formated result with a harcoded value
Hello. I have a cell containing the numeric value. I use cell formatting to present the numeric value as which month it is. The formats value is "april" In another cell I have the hardcoded text value "april". How do I compare these two cells and find out if they are the same? /Anders -- a94andwi ------------------------------------------------------------------------ a94andwi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21077 View this thread: http://www.excelforum.com/showthread.php?threadid=532567 One way: Assume A1 is the cell...

Adding to number to get the cell
Hi Guys, I want to know how can I do this: I have column A and B and and with this two number, add then and see the data in the cell Column A Column B Column C 12 2 I wanto see the values in A12 +2 (A14) 15 10 I wanto see the values in A15 +10 (A25) 35 20 I wanto see the values in A35 +20 (A55) : : Thanks Paste this formula in C14 cell =IF("A"&SUM(A14:B14)="A0","","A"&SUM(A14:B14)) Now copy the C14 cell and pas...

vlookup looking for the next higher value?
I would like to look-up a value in a table. and in case of not an exact match select the next higher value. Vlookup rounds of downwards. I'm sure someone has a better method, but if you insert a column within your lookup table (or on the right of it) you can use that as an offset to pick up the next higher value. See attached, the working line is =IF(VLOOKUP(A21,$A$1:$C$9,1,2)=A21,VLOOKUP(A21,$A$1:$C$9,1,2),OFFSET($C$1,VLOOKUP(A21,$A$1:$C$9,2,2),0)) where column B is the increment column. Hope this helps. deugniet418 Wrote: > I would like to look-up a value in a table. and ...

Importing mac Quicken to Money that has exsisting information in it
Hey I have used a mac for years and quicken for the mac to keep my finances in check. I switched to the pc about a year ago and started using quicken for the pc. I started new using quicken for the pc because i couldnt for the life of me figure out how to import the data from my mac........ive recently found out how to do that NOW but I have a years worth of data in quicken for the pc. NOW I would like to switch to Money for the pc that came with my computer. I got the files converted from my quicken for the pc to money ok but can i now go back and import all that old information from...

SUMIF non-blank cells?
We have been able to conditionally sum a column of cells (sum_range) based on the blank cells in another column (range) using SUMIF(A$8:A20,"",B$8:B20). Nevertheless, we actually need to sum the NON-BLANK cells in the sum_range column. How do we create a formula that will sum the non-blank cells? Please note that we have tried several iterations of the formula 'criteria' argument including NOT(""), NOT(""""), "NOT("")", "NOT("""")", ISBLANK(), "ISBLANK()", and so forth. ...

Conditional Formatting of Cells based on discontinuous range of numbers
Hello, I have a column of numbers that I want to match to another column. In column C, I have numbers that have discontinuous values from 0 to 10,000. In Column N, I have a similar but smaller subsample of numbers (again ranging from 0 to 10,000). I would like to make all of the cells in column C that have a matching number anywhere in column N, yellow. Is this possible? Conditional formatting does not seem to work on ranges. Thanks in advance. Mike try this select the range for col C , go to format | conditional format | condition 1 : formula is =3DNOT(ISNA(MATCH(C1,$N$1:$N$11,0))) ...

this should be easy...highest value in a row
How do you display the highest numeric value in a row? Example: Row 1: A 100 B 200 C 300 D displays "300", highest number on row 1. lookup value or something???????? thanks!!! jfarino@mindspring.com Hi Jacob =max(1:1) -- XL2002 Regards William willwest22@yahoo.com "jacob farino" <jfarino@mindspring.com> wrote in message news:PQEEd.3756$C52.120@newsread2.news.atl.earthlink.net... | How do you display the highest numeric value in a row? | | Example: | | Row 1: | A 100 B 200 C 300 D displays "300", highest number on row 1. | | lookup val...