Cell Text As Part Of External Reference

Apr 2, 2007

I am building a summary spreadsheet which will bring in a number of values from other spreadsheets located in folders on a network. Each set of external references will return the same cells, but the location and filenames are different. In order to make it quick and easy to modify the file names and locations without having to change the cell references in my summary sheet i want to type the full reference in a cell as text then use this in another cell as part of the external reference...

View 9 Replies


ADVERTISEMENT

Edit Part Text In DDE Arrays Linked To External Program

Sep 27, 2006

I have a datafeed program that offers multiple DDE link types to retrieve different types of data. After pasting the DDE links into Excel, all works well. However... To modify the links means manually editing upto 12 different array formulas located on different pages then pressing Ctrl + Shift + Enter after each to change the data source being analysed.

I found that I can manually copy and paste a few characters into each linked array formula, Ctrl+Shift+Enter but it needs to be by Macro referring to a couple cells (Say A1, A2) containing the variables that alter the data source being linked to.

I need to make a VBA Macro that refers to these two cells containing text, and modify the 12-odd linked array formulas.

eg. {=SUB33|getlocation!'N,pg,9,vp,A,30'}
In this example Linked Array formula, the bolded PG and 9 would be variables pulled from cell A1 and A2.

All other components of the array formulas stay unmodified, it is only the "pg,9" text that needs to change in each linked array.

View 4 Replies View Related

Link To External Workbook From Cell Reference

Nov 24, 2011

I've got a field where I enter the current month, from this I derive a file path which is stored in A4.

ie: A1 = '01/11/2011' (UK Date format)

A4 = '\myserverdirectory20111111MyFile.xls'

What I'm trying to achieve is to then have a link in cell A6 that will link to the file in A4. I can specify my cell reference as this will never change, just the file path.

So, if I changed A1 to '01/12/2011' my A4 changes to '\myserverdirectory20111211MyFile.xls'

I'm trying to set A6 to something like ="&$A$A4&"!B1 to get the value of B1 on my referenced sheet.

View 1 Replies View Related

Assign External Cell Reference To Variable In VBA?

Jun 29, 2006

I'd like to assign a value located in a cell in a workbook to a variable in a VBA program in another workbook. I do NOT want to open the workbook that contains the cell with the value. I can't get the hang of referencing an external cell reference. Let's say there's a value in a cell named "nbr" in a workbook named "tst.xls" located in a path "c: est" that I want to assign to a variable "tstnbr" in a VBA program, what is the best way to code that?

View 8 Replies View Related

Cell Data To Reference External Workbook

Apr 21, 2008

I am designing a spreadsheet at work, and need a little assitance. I can't find the answer I need online or here either. Here's the problem: I want to be able to enter data (A 5 digit job number, 80227 for example) into a cell, and then multiple other cells would auto fill with data from outside worksheets. The worksheets are in a seperate folder on my server and named under the same name as the project. (So if I entered 80227 as the data in A1, I would want A2, A3 and A4 to open spreadsheet 80227.xls on the server, locate the referenced cell/s, and insert the info from 80227 into them.)

I can easily reference the cells alone and have them input the data, but this is extremely time consuming since this spreadsheet has all the current jobs our company is working on. I currently am running a macro/VBA that automatically updates all linked spreadsheets without them having to be open, so that is not an issue.

View 3 Replies View Related

External Workbook Link With Dynamic Cell Reference

Feb 24, 2014

I currently have a file that is linked to an external workbook that is referencing a certain cell from a certain tab. The below is what Column A & B look like right now. What I'm wanting to know is is there a way to have the formula in Column B automatically reference the name in Column A (which is the tab name in the external workbook) instead of having to manually change the formula when the value in Column A changes?

In other words, I want everything in the external reference formula to remain the same except have the person's name change dynamically...

Mary
='X:Anderson CorporationEmployees[Work Hours]Mary'!$F$42

Joe
='X:Anderson CorporationEmployees[Work Hours]Joe'!$F$42

Frank
='X:Anderson CorporationEmployees[Work Hours]Frank'!$F$42

Michelle
='X:Anderson CorporationEmployees[Work Hours]Michelle'!$F$42

Sam
='X:Anderson CorporationEmployees[Work Hours]Sam'!$F$42

View 1 Replies View Related

Adjusting External Cell Reference (Links) In Excel

Aug 7, 2013

The goal is to have a data validation drop down list that adjusts the File.xls name portion of the external cell reference (example given below) and return the result of the cell I am searching in the new file after updating my links.

I am trying to set up an external cell refence within my workbook for example:
='[File.xls]Tab'!$C$9

When I have tried setting the equation to pull from the drop down list in cel A1 for example:
="'["&A1&"]Tab'!$C$9"

It only shows the new file path name and not the value in the new sheet--even after updating links.

I am also attempting to do this using the my network places file path instead of the letter drive in my computer.

View 2 Replies View Related

Reference Cell From Another Workbook With Part Of Location?

Feb 20, 2014

I have the following formula/array:

='fake folder location[2013-004.xlsx]MgmtRpt'!$A$4

Now this works perfectly fine, but I am wondering if it is possible to replace 2013-004 so that it reads the text from a cell on that sheet to complete the file name.

Example:

A
B

1
2013-004
='fake folder location[2013-004.xlsx]MgmtRpt'!$A$4

I want to replace the 2013-004 in B1 with text from A1. I have tried multiple methods but it just always assumes it is part of the file name or breaks the formula.

View 2 Replies View Related

Is It Possible To Use Cell Reference As Part Of Page Headers

Jan 5, 2007

I would like to know if there is an option available which will allow me to reference a cell on the active sheet and print it as part of the sheet header. The contents of this cell change often so the header should also reflect the change when the sheet is printed. Also, this header should be alinged in the center.

View 9 Replies View Related

Reference Part Workbook Name In Formula From Date In Cell

Feb 20, 2008

i want to return a value from a cell in another workbook where part of the the workbook name is determined by a date set on the current open sheet.

so on my open sheet i have a cell where the user can change the date and i want the below formula to then use the date to look at the relevant file.

any ideas would be appreciated. know how to do this in VBA but no clue with formulas.

what i am trying to do is concatenate part of a file name and a cell value which contans a date

=VLOOKUP(A4,[accountsummary17022008.xls]Sheet1!$A:$B,2,FALSE)

View 3 Replies View Related

Cell Entry As Part Of A Cell Reference

Dec 4, 2008

Let me try to explain.

I know that I can do this to fetch a cell entry from an external workbook

View 5 Replies View Related

Text Trimming - Display Part Of Cells Text Value Inside Another Cell

Jun 15, 2014

I want to know how to display part of a cells text value, inside another cell.

Suppose in cell A1 i have "20-Jun-14"

How would I get cell B2 to display just "Jun"?

View 4 Replies View Related

Delete Row If Cell Contains Certain Text As Part Of Text String?

May 15, 2014

I have a column of data with letters in each cell, no numerical, only alpha. Now, some of those cells contain the letters "adj sub" as part of the text string in each cell. "Adj sub" is always at the beginning of the text string. As an example, a cell will look like this - "adj sub mhm". I want to delete rows whose cell description does not contain "adj sub" as part of the text in the cell.

View 5 Replies View Related

Reference External Workbook

Feb 5, 2008

I have a list of objects in column A

I want to select that list or part of it then run a VB module that references each item in the selection and compares it to a master list in a separate workbook that contains both the object and an additional value, and display that additional value in column B of the original workbook against each object in column A

View 9 Replies View Related

Add Text Based On Part Of Another Cell Text

Oct 16, 2006

I have been working on this worksheet part of which is attached herewith. I would like excel to automatically enter Updated/Inserted in column B against Individuals' names as per the instructions given in column A. For example: As per instruction in A9, B13:B16 should show Updated. I have tried to use the nested if function, but it does not work as I want it to. Also as I am not used to macros or VBA codes, could this problem be solved with formulas?

View 2 Replies View Related

Creating External Reference With Variable?

Feb 27, 2014

I'm wondering if there's any way to put a variable for a cell address in an external reference formula. Basically, I'm creating an abridged report of customer data that takes a single customer's info from 2 other workbooks on a networked drive and compiles the chosen cell data into one worksheet summary of their info. The base external reference formula looks like this:

='F:Projects[CustomerDB.xls]Sheet1'!VariableCellGoesHere

I want to know if there's a way I can make the formula use a variable cell address that I can just type into another cell to tell it which row I need to grab info from. Generally, all of the customer data I need is spanned across multiple columns in one single row. The formula above appears multiple times in the sheet I'm trying to make. The first is for "Name", the next is for "Address", next is "Phone Number", etc. So for instance, if I want customer data from the main spreadsheet and that customer's info is on row 355, my first few formulas are:

='F:Projects[ProjectDB.xls]Sheet1'!$A$355
='F:Projects[ProjectDB.xls]Sheet1'!$B$355
='F:Projects[ProjectDB.xls]Sheet1'!$C$355

Is there a way I can maybe have a designated cell where I just enter the row number I need (e.g. 481) so the formula sees that number and plugs it into the cell reference above resulting in the formulas adjusting themselves to ='F:Projects[ProjectDB.xls]Sheet1'!$A$481?

View 9 Replies View Related

Reference To External Workbook Objects

Dec 14, 2009

I'm having trouble working this around, i use the following code in one of my Workbook sheets.

View 10 Replies View Related

External Formula Reference Error

Dec 5, 2012

When I open my Excel workbook, I get the following error:

Removed Feature: External formula reference from /xl/externalLinks/externalLink1.xml part (Cached values from external formula reference)

I am asked if I want to recover the contents of the file.

View 1 Replies View Related

External Workbook - Reference With Single Link?

May 16, 2013

Is there a way to specify the filename for an external workbook that can be referenced throughout the spreadsheet? In my case it would be better if I could change the single reference instead of having to change it throughout the entire spreadsheet everytime I needed to use data from a different file.

View 2 Replies View Related

Remove First Part Of String Of Text And Keep The Second Part?

Feb 12, 2014

Is there any way to remove the first part of a string of text in a cell and save the second part?

The first part of the text string is a team code that has a variable number of numbers, capital letters and sometimes spaces. The second part of the text string is a variable number of words in a team name that all start with a capital letter and have lower case letters. Every line has a different team code and team name.

The original spreadsheet also has a column with just team code. Is there a way of using this column to "subtract" the team code from the text string to just leave the team name?

View 5 Replies View Related

Vlookup On Part Of The Text In A Cell.

Jun 18, 2009

I am trying to do a vlookup and am having soem issues. I have data like this in the cell i am using as the reference: "60030 - COLUMBUS, OH". The first column of the range I am searching would have "60030" in it. What i need to know is how do I get the vlookup to just reference the first 5 digits in the reference cell? As you can see by the formula below, I set the logical value to true hoping that it would find the closest value, but it did not work. =VLOOKUP(A7,sdoalign,3,TRUE)

View 2 Replies View Related

Extracting Part Of Text From One Cell To Another

Aug 20, 2006

I have a column of cells with similar to the following text.

First Marine Avenue 18 1303 11 1

There will always be a final 4 groups of text numbers, each with a space
between, but each having different number of numbers (this particular
sequence has 2,4,2,1 - but others may be different, but always 4 groups)

The true text ( a series of Road names are potentiaaly all different, having
a sequence of words that may be up to 5 words long before the numbers start)

I need to extract the 4 groups of numbers into seperate cells, leaving the
Road name text in a singe cell. I would then have a table of 5 columns, the
first the Road text, and the next 4 columns being the group of numbers, which
I would then format as numbers.

View 12 Replies View Related

To Bold A Part Of The Cell Text

Apr 8, 2007

I am creating a sheet with the cell information coming from different cells. I want to bold and/or underline a part of the cell to add clarity.

For e.g.
Cell A1 = "Trans X" (Name of the transaction);
Cell B1 = "System Y" (System where the transaction exists)
Cell C1 = "Type Z" (Type of transaction)

I want to create a cell which has
Newsheet.Cell (x,y) =
"Name: Trans X
System: System Y
Type: Type Z"

I created the String by reading and concatenating the values, but I am not sure how to add the formatting to a part of the string.

View 3 Replies View Related

Trim Part Text To New Cell

Jul 17, 2007

I want to put text after firsr 15 chars to new row.

View 5 Replies View Related

Pull Part Text From Cell

Aug 8, 2007

Consider the following:

Cell A1 has this text in it:

WT*495*001559369~G3**D1234567**XX**20070626*1740****DHLC*20070807~B4G*RX*1~C32*UDD**UAD~ITQ*05*3****20030711*12~N1*FF*LALALA*25*00802109480~N3*12A46 KRESSASLE

Is it possible to somehow filter, search, find, etc, and basically clean cell A1 so that in the end it has just G3**D1234567?

I made a search macro, but the problem is there is so much text that it takes 2 seconds per cell to do a find. There are 6 columns with 65000 rows. I assume if I clean up each cell and do a "xlWhole" instead of "xlPart" in the find code, it will be much faster.

A1 = WT*495*001559369~G3**D1234567**XX**20070626*1740****DHLC*20070807~B4G*RX*1~C32*UDD**UAD~ITQ*05*3****20030711*12~N1*FF*LALALA*25*00802109480~N3*12A46 KRESSASLE

Result:

A1 = G3**D1234567

Possible?

Note that all rows do have a "~G3**" before the actual number I am doing a search for.

View 5 Replies View Related

Hyperlink Part Of Cell Text

Jan 31, 2008

in order to hyperlink a single peice of text in a cell and not the whole cell I have created a shape with no fill or line but added text. I then hyperlinked it to a location in the workbook. When I set the Move and size with cell property I am not geting the expected result. If I resize a row well above that cell, the object isn't following the cell it wa placed in. I would be happy with alternative mechanisms for putting independantly linked text.

View 4 Replies View Related

Macro To Remove External Reference Path From Buttons

Jun 11, 2008

I have a little boo-boo I need to fix. I have a customer that has about 100 workbooks. I needed to add some functions to them so I made up a new sheet of forms and buttons as well as a series of new macros to bring into these workbooks. I knew I couldn't import the macros, so I added the module containing those by hand to all workbooks. I then imported a master copy of the new sheet from a master file i use to hold my working macros. The problem is the buttons on the new sheeets in each workbook now references the master workbook for the macro name, even though each workbook has the same macro of the same name in itself.

My question is, can I make a macro that will edit all the buttons on a specific named sheet (maintenance is the sheet name) in a workbook and take out the external reference.

For example, instead of the assigned macro pointing to c:master.xls!macro1 i just want it to call macro1. And yes, I can go into them and do it by hand. Do I want to manually edit 100 workbooks for a dozen buttons each? No. I want to automate it if I can. If anyone can help I would greatly appreciate it. It would save me a bunch of time.

View 9 Replies View Related

Finding Part Of Text Then Copy Value Of Other Cell

Mar 26, 2014

Here's the situation (an example):

I have sheet1 that contains the following information

Column A Column B
company1 - book value 0,5
company1 - earnings 0.2
company1 - R&D 1111
company1 - total assets 200000
company2 - book value 2
company2 - earnings 333
company2 - total assets 12

So column B contains only numbers and note that for company 2 I do not have any numbers on R&D.

I have in sheet2 only once the company name. I would like now that if column a contains the text "book value" to have the value of column B pasted in sheet 2 column B. Same for earnings to be pasted in column C. R&D in column D and if R&D is not present for that company then the value should say something like unknown.

View 3 Replies View Related

How To Break External Reference Link Without Converting Formula To Value Within Sheet

Jul 1, 2013

I have a formula in a cell which is refered to other cell within the same sheet and also to other sheet from other file. say

C1 = A1+B1+data file c1 Data file is another file in other location.

I was trying to break a external reference link (from Data File) from Data _ Edit Link, but it is converting all the formula to value. How is it possible to break only the external link without damaging the formula within the sheet say c1 = A1+B1+26 (value from data file c1)

View 5 Replies View Related

Changing Formula To Reference Spreadsheet From External Excel Document

Nov 22, 2011

how to change the formula below which is referencing an internal worksheet, to a forumula that will reference an external worksheet that is located in an excel document on the G: Drive.

=SUMPRODUCT(--(1210laradj_v_2009!F2:F65536="HMO-ALT SELECT"),--(ISNUMBER(MATCH(1210laradj_v_2009!AO2:AO65536,{3,5,9,12,37,39,46,47,54,73,74,75,76,77,78},0))))

So what it's doing is totaling the counts in the 1210laradj worksheet that match columns HMO-ALT SELECT as well as the numbers 3,5,9 etc.

Well now these worksheets will be on a seperate excel spreadsheet located on a network drive, so I am trying to figure out how I can fix this.

I tried adding it in like so, but it fails.

=SUMPRODUCT(--('G:ROC-CLAIMSAudit ResultsProvider infomonthly statsReproc'd claims2011ProductionCounts[0111rpt2235_laradj_v.xls]0111laradj_v_2009'!F2:F65536="HMO-ALT SELECT"),--(ISNUMBER(MATCH(1210laradj_v_2009!AO2:AO65536,{3,5,9,12,37,39,46,47,54,73,74,75,76,77,78},0))))

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved