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
756 Views

Similar Articles

[PageSpeed] 3

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:

Importing data
When I import sales data through 3CDaemon, I get cells that I of data that I cannot use in formulas. It is like they are corrupted. But can copy the info from cell to cell, just cannot use in formula (ex. =if(dd04="N",1,0. Formula does not error out, but it makes no difference if N, U, or anything else is in dd04. It answers back 0. How can I fix this ? Just a couple of guesses (I have no idea what 3CDaemon is). First, Get a copy of Chip Pearson's CellView Addin so that you can really determine what's in DD04): http://www.cpearson.com/excel/CellView.htm Second, ...

concatenate a 100 cells
concatenate maybe a hundred cells--A1&A2&A3&A4....A100-- WHAT FORMULA CAN I PLUG IN TO concatenate A1 THRU A100?? A1&:A100 THIS DOESN'T WOR -- ROL ----------------------------------------------------------------------- ROLG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1606 View this thread: http://www.excelforum.com/showthread.php?threadid=27536 On Thu, 4 Nov 2004 12:44:45 -0600, ROLG <ROLG.1f7mpc@excelforum-nospam.com> wrote: > >concatenate maybe a hundred cells--A1&A2&A3&A4....A100-- > >WHAT FORMULA ...

Cell reference from a looked up value
Hi, I'm trying to get the cell reference of a value looked up in a table. For example: A table of values are found in A4 to J13 (10x10 table). A4=1, B4=2,A5=11, B5=12 and so on (ie 1 to 10 is in the first row, 11 to 20 is in the 2nd row, Last row is 91 to 100 (found in A13:J13). A1 contains the value I want found B1 contains some formula that gives me the location in the table (absolute). i.e., if 1 is in cell A1, I want B1 to have "A4" if 100 is in cell A1, I want B1 to Have "J13" Is there a function or combination of functions that would allow me to do this? T...

Import and export
Trying to salvage my data and it was suggestd I import and export files, do I need to import and export closed accounts as well? Only if you want them in the new file. In order to export a closed account, you'll need to first specify that it is open in Money. "Mike" <anonymous@discussions.microsoft.com> wrote in message news:269701c3fc78$9f29fcf0$a301280a@phx.gbl... > Trying to salvage my data and it was suggestd I import and > export files, do I need to import and export closed > accounts as well? ...

Importing messages #6
I have a backup disk that I would like to impport from, but obviously I do not know how. When I double-click a message, it does not recognize which program to use to open it and takes me to an MS site. All of these have a ..dbx extension. Thank you. "Jennifer" <Jennifer@discussions.microsoft.com> wrote in message news:3398C3D3-FDD8-41CB-8F73-C445B56F6CF7@microsoft.com... >I have a backup disk that I would like to impport from, but obviously I do > not know how. When I double-click a message, it does not recognize which > program to use to open it and takes me...

How do i change the height and width of a single cell in Excel?
You change the height of the cell by changing the height of the entire row, and the width of the cell by changing the width of the entire column. The cell cannot be independently resized, because then all the other cells wouldn't fit. You can merge contiguous rectangular ranges together to create super-cells. Depending on your objective, merging cells may have undesired side-effects. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ lotus1107 wrote: ...

Import TransferIN into TransferOUT... its possible?
Hi, I have RMS 1.3, its possible to import a transfer in of one store into transfer out on my store? Thanks in advance. Aldo, If you are running HQ, it does this for you, on the next connect to HQ. -- * Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment. If you are not using a reader, follow the link below to setup Outlook Express. Click on "Open with newsreader" under the MS Retail Management System on the right. http://tinyurl.com/75bgz ********** "Aldo" <...

excel 97 xml import
I have Excel 97 and need to import a XML file. I don't know vba. Slinger XL97 was written way before XML therefore it will not handle it. Only XL2003 (Individual product or from Office Pro) will recognise and handle XML import/export -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Slinger" <Slinger@discussions.microsoft.com> wrote in message news:D1D9952E-F741-4B11-8040-B27B34F382BE@microsoft.com... >I have Excel 97 and need to import a XML file. I don't know vba. ...

Importing Dynamic data
Hi I have some tables in various xl files, call the files a,b and c. I want to import all these into a single xl file called e. If changes are made to a,b or c I want it to automatically update e. I have used the import function in d but if I change a,b or c to see the change I have to select the data in d and press refresh. Furthermore when I refresh d i have to have files a,b and c open otherwise it says connection lossed and is unable to refresh the data. Can someone explain to me in simple terms what I have to do in order to get the data automatically updated without having to alway...

writeprotect a cell dependet to the value in the cell before
Me again... is it possible to allow/disallow writing to a cell dependent what the value in the cell before is? Sample: A5 = Validation List B5 = VLockup value (dependet to value choosen in list before - just learned that here yesterday) :) C6 = Depending tha value choosen in the validation list in A5 List coming from sheet "Data") C6 must be write enabled or protectet Smaple with values Lists "Voucher" --> "1 Voucher" "Invoice" --> "USD 100" "Hourly" --> "5 hours" A5 = validation list B5 = dependet value go...

protect borders on a data entry cell
I have an Excell 2007 spreadsheet with borders around selected cells, the spreadsheet is protected so my formulas cant get corrupted, but with some cells that are unlocked for user data entry. But when you copy and paste in these user entry cells the borders are getting copied and pasted also destroying the look of the spreadsheet. so how do you protect the borders from being changed and still allow copy and paste in the user entry cells? Thanks in advance Terry You could politely ask users to copy>paste VALUES -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin....

How I import data without truncating zero in the beginning
I am importing data from text file to access database but it is truncating all the zeros if they come in the starting.... please help me to resolve this problem as there is a field name telephone number and I need zero in the starting to distinguish between local and international calls. Remplace them by "+" (normaly it's the same) "Mohsin Habib" <MohsinHabib@discussions.microsoft.com> a écrit dans le message de news:8B32A18C-E88B-4259-8CAE-332EB821883C@microsoft.com... >I am importing data from text file to access database but it is truncating > ...

Importing JEs through the Table Import
Is there a way to import JEs throught the table import. If so, how is this accomplished? Please help! Thank you! If you have access to table import you must also own integration manager. either way, yes, you can import transactions. please give me a call at 214-373-8550 and we can walk through the process. Leslie "Christina" wrote: > Is there a way to import JEs throught the table import. If so, how is this > accomplished? Please help! Thank you! You can use Table Import but Integration Manager is much, much easier to use. If you can use Table Import, you own Inte...

excluding cells from calculations by "marking" the cells?
Within a range of cells designated for a calculation, I am tyring t exclude certain cells without having to modify my function (usuall very complicated) or using conditional formatting. Example: - 10 data values, function calculates result - 2 of the data values (never the same two) are unacceptable I want these two values to be excluded from the caculation. What I nee is a way of designating these cells as "Ignore". I don't want to delet the values, nor do I want to cut out the cells. Any ideas? Thanks -- Message posted from http://www.ExcelForum.com Hi Are these '...

Formula needed for Sum(A1:C1) then Sum(D1:F1) in next cell
I am converting a monthly revenue table to a quarterly revenue table. I would like to add A1:C1 and put it the result in a new cell and then autofill the cell to the right of it so that it automatically adds D1:F1 for the next value A B C D E F 1 Jan Feb March April May June 2 10 12 12 13 14 15 Qtr1 Qtr 2 Qtr 3 sum A2:C2 Sum D2:F2 Etc... using autofill currently using autofill the first qtr is correct "=Sum(A2:C2) but the next cell gets filled with "=sum(B2:D2) instead of addi...

Importing a CSV file
Hi, When I try to import a CSV file into Outlook, it says I have to first install the DATA1.MSI file from my windows XP installation disk. I looked at my installation disk and here doesn't seem to be such a file. How can I import? Thanks, Randy If you have the correct installation disk, then all you need do is run the Office setup - Add/Remove options and add the Outlook import filters. Karl -- ____________________________________________________________ Karl Timmermans - The Claxton Group ContactGenie - QuickPort/DataPort/Exporter/Toolkit/Duplicate Contact M...

Locking cell color while allowing data changes in cell
In excel 2000, I created an attendance worksheet for my classes.(Alphabetized names down left vertical column. Dates across top of horizontal row.) I added a different color to all cells in every other row to make for easier reading of each student's name and absences. Every other row stays with a white background. My question: I wondered if it was possible to lock row colors while allowing data to change on top of them. If a new student is added to my class in alphabetical order, the alternating color pattern is often lost. It is a pain to rechange row and cell colors. Any shortcut ...

Import DB from CRM 1.2 to 3.0
Hi to all, How can I import the DB or data from CRM 1.2 to new CRM 3.0? Now my CRM DB are on SQL2000 server. Thank and regards. Augusto You should really upgrade CRM1.2 to 3.0. The data structutres are different enough that doing a migration would be pretty painful. Any reason not to upgrade? "IGBA75" <a.crippa@ieti.biz> wrote in message news:%23c97ZdkXGHA.3660@TK2MSFTNGP04.phx.gbl... > Hi to all, > > How can I import the DB or data from CRM 1.2 to new CRM 3.0? > Now my CRM DB are on SQL2000 server. > > Thank and regards. > Augusto > ...

why are my important emails going into junk email
I posted on outlook general and didn't get a response. so I looked here and there was no clue as to what is happening. I am using outlook 2007 on the desktop. now any mail coming in from facebook is going to junk email. also my zdnet newsletter is going there also. I have added their domain to the safe list but they are still going to junk. this probably started the other day when I got outlook 2003 going on my laptop so I could see my emails when I am away from home. I did lower the category for junk mail one item but that didn't help either. don't know what...

Updating a cell with data validation when another cell is changed.
In my sheet I have cell Y2, which when it contains "y", I want it to update the validation list =IF(Y2="y",DogList,NAList) That works, but if i delete the "y" from cell Y2, the validation drop- down list doesn't change until I click onto the cell I wrote some code to try and work around this: Private Sub WorkSheet_Change(ByVal Target As Range) Application.Calculation = xlCalculationManual If Target.Address = "Y2" Then Range("AJ2").Value = "" Application.Calculate End Sub But still doesn't do that I want, when I change t...

import pdf table
What is the best way to inmport a table in a pdf document into excel? Check out: http://www.library.mcgill.ca/edrs/services/publications/how to/PDFtoXLS/PDFtoExcel.html#basicexports HTH Jason Atlanta, GA >-----Original Message----- >What is the best way to inmport a table in a pdf document >into excel? >. > Those directions are for university students that have Adobe Acrobat installed (not just the reader). Though there is a reference immediately above that does refer to the acrobat reader Most people do not have the full Adobe Acrobat software. With just the Acrob...

Cell Colour Change
Hi, I have this code With Sheet1.Range(Runinfo1.ComboBox2.Value) If .Range("b2") <> "" And .Range("b7") = "" And OptionButton2.Value = True Then ..Range("b9").Interior.ColorIndex = 5 End If I'm trying to change the colour of the cell if option2 button is selected (after my command button is pressed) but it doesn't work. I'm assuming it is the ".Range("b9").Interior.ColorIndex = 5" part... Any ideas? Cheers... You may want to look at ths construction of this: I'm not sure about this with sheet1...

importing email
Can you import an email into Outlook from Aol.com using high speed internet cable? No. AOL uses proprietary mail format and does not play nicely with others. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Having searched the archives, Ben <mysanity@att.net> typed: | Can you import an email into Outlook from Aol.com using | high speed internet cable? ...

PIVOT
Hi experts, I want to enable users with few knowledge of pivot techniques to change the grouping of a pivot chart resp. the underlying pivot table. The idea is, to have a changeable cell value beneath the chart to enter the group (groups of 2, 4, 6, 5, etc...). The associated pivot table shall change it's groupings accordingly, thus forces to change the associated chart. Any idea, how to achieve this? Thanks and have a nice day Michael Hi, What kind of groupings are we talking about when we say 2, 4, 6, 5? What is being grouped and how? What fields, row fields, more than one row fie...

How do I change the color of cells with a macro
I was using conditional formatting, but this only gives three options, I need 5 I am looking for a simple piece of code which says If cell A1 is not zero color the row green, for example. I have tried With range, If Else and Case I get the first color, then it will not change You don't give us much select case range("a1").value case<>0:mc=6 case>2:mc=3 case else end select rows(1).interior.colorindex=mc -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Mike DFR" <MikeDFR@discussions.microsoft.com> ...