Documenting Formulas & Links

Feb 24, 2009

I have a lot of workbooks that contain many formulas which may include links to other worksheets.

Is there a way to produce a sheet in each workbook that shows the formulas on each sheet in the workbook. I would envisige that:
Column A would have the name of the worksheet
Column B the formula
Column C if possible the worksheet that the formula links to.

View 9 Replies


ADVERTISEMENT

Manipulating Links And Formulas

Jun 12, 2006

Is it possible to modify a link so that you can change the link easily by the value of a certain cell.

='[Labour Plan Week 24.xls]Hours'!$E$135 and I want something like ='[Labour Plan Week " &H5& " .xls]Hours'!$E$135. Where H5 contains the week number

View 3 Replies View Related

Copying Files With Links And Formulas?

Aug 5, 2014

I am working on a file that has multiple worksheets with many links, lookups and formulas; some between the worksheets and some external to another Excel file. The plan is to use this file as a template and copy it over and over with new names. Once I save the file as a new name in a new folder on my network, will I lose all these links, lookups and formulas? Or is there a way to maintain the links or do a global change of the formulas (i.e., the original file name is "TEMPLATE" and the new file name is "PROJECT!1")?

View 2 Replies View Related

How To Remove External Links From Formulas

Jun 9, 2014

I have recently come across an issue I have never seen before.

In the attached xlsx file, I have formulas which are trying to reference an external file (C:UsersDELLDownloads[test.XLSX])

How do I delete the external link without losing the formulas.

test.xlsx

View 2 Replies View Related

Edit Links - Break Links Not Working?

Jun 22, 2012

I have an unwanted link and I can't remove it. I have tried through the Edit Links dialog box, searched for [ in formulas throughout the workbook and gone searching manually for the reference but to no avail.

View 9 Replies View Related

Using Cell References In File Paths For Formulas To Create Dynamic Formulas

Dec 3, 2013

I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:

=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292

What I want to do is extract the file path from the above formula and make it a composite of several cell references.

So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:

_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH

I tried several versions, I am hoping for something like this:

=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292

View 4 Replies View Related

Converting Formulas To Relative/absolute References With Formulas Referencing Other Sheets

Dec 15, 2008

I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.

View 9 Replies View Related

Formulas To Hide Partial Concatenate Data And Determining Two Other Formulas

Dec 11, 2013

I'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines.

I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?

View 11 Replies View Related

Excel 2007 :: Formulas In Cells Not Being Recognized As Formulas?

Jan 10, 2013

I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.

View 3 Replies View Related

Paste Formulas As Values (strip Out Unwanted Formulas)

May 13, 2008

I have a macro running this code to strip out unwanted formulas and formatting.

Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False

Range("CDandC").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2

'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If

Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value .........................

A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.

View 4 Replies View Related

Hide Formulas From Formula Bar While Still Having Formulas Active?

Jan 16, 2014

Is it possible to hide formulas from the formula bar while still having the formulas active?

View 8 Replies View Related

Using Two IF Formulas (3 Or More To Count If Other IF Formulas Are Actually Returning A Value)

Aug 24, 2009

I have a spreadhseet with various functions on it and what I am trying to do is this.

Cell E4 returns a >35 or <35 true or false value
Cell G4 is either blank or has "Yes" text type into it.

What I am trying to do is get cell F4 to return certain arguments.

E4 = >35 and G4 is blank I want it to state "Email Hiring Manager"
E4 = ,35 and G4 is blank I want it to state "Wait"

I have a basic IF formula that returns this
=IF(E4>35,"Email Hiring Manager","Wait")

Then if cell G4 is populated with a Yes the formula needs to overwirte the origonal if with the return arguments of
=IF(G4="Yes","Email Agency","Email Hiring Manager")

If yes then what would be Email Hiring Manager (yes will only be input if E4 is greater than 35) will be overwritten with "Email Agency"

Can this be done with two If formulas or does there need to be 3 or more to count if other IF formulas are actually returning a value?

View 5 Replies View Related

Convert Formulas In Text Into Formulas

Dec 12, 2007

If you have a cell with the value ="2*c2+3" NB: (Notice the ""), then to make excel convert the formula in another cell to =2*c2+3 (notice the removal of ""), so that it can calculate the value of the cell instead of showing a textstring?

View 11 Replies View Related

Formulas Won't Calculate. Just Displaying As Formulas

Dec 11, 2008

I have formulas in a column and they are working unless I edit them to include another function, more cells, whatever, then they display as formulas instead of the result. I've gone to Tools --> Options --> View and the Formulas box is not checked. As well automatic calculation is on not manual.

View 4 Replies View Related

UDF Links

Jun 15, 2009

I've been having a small problem with my UDFs. I'm sorry if it's already been asked but english isn't my first language and I don't know the proper search key works to find my problem.

Basically, I've been programming functions destined for a single workbook so I don't use addins. If I write a function called J13F and I try to write that in one of my excel sheets, it generally doesn't work. Excel makes me write it like this:

='myfilename.xls!'J13F.J13F(Sheet!F21)

However, sometimes when I close then open my file, this won't work and excel will make me use my function normally: J13F(Sheet!F21)

I'd like to be able to use the function normally in every sheet in my workbook, how can I do that?

View 7 Replies View Related

Using Links In VBA

Feb 7, 2010

I am using several spreadsheets which are currently linked to specific cells in other workbooks. These are updated through dating the links whenever needed. However I would rather not use formulas in the cells at all and only have value updated by using the link function within VBA. I cannot find how to do this though.

Just to reiterate. I currently have cells that have"='\server01main_foldersubfolderDocument.xls" for example. The formula syntax is not exactly right but in the spreadsheets it is so you get the idea. These links are updated (when the workbooks are not open) when the links are update via the edit menu or a macro which does the same thing.

What I want to do is have no formula in the cells (only values) and use the feature that takes the value from the other workbooks but in VBA exclusively. This is what I have not been able to find out how to do.

View 5 Replies View Related

Spreadsheet Links

Sep 9, 2009

I've created a spreadsheet which pulls data from about 5 other spreadsheets via links in the formula.

Unfortunately I am having trouble with these updating. I have to open the 5 individual spreadsheets in order for the data to pull across otherwise I get VALUE in every cell in the amalgamated spreadsheet.

Any thoughts why this should be the case? I've tried it in the past and it's always worked fine.

View 11 Replies View Related

Open Links In A New Tab?

Feb 20, 2014

I just want to open some links in a new tab in a browser with a single click in a button.

For example:

[URL]

then in a new tab

[URL]

View 1 Replies View Related

Updating Links All At Once

Apr 11, 2008

if it would be possible to update all the links in a workbook at once. For example, say I have a worksheet which is linking to a file named something like 4-6-08 (1) and 4-6-08 (2) and so on with the number in brackets being from 1-21. Now say I have the same thing for 4-7 and 4-8 and 4-9. Now I want to change the links to point to 4-10,11,12,13 instead of 6,7,8, and 9. But still there are 21 copies of each sheet. Is there any way to change all the sources at once so all the 4-6's could become 4-10's and 4-7's to 4-11's?

View 12 Replies View Related

Transposing Links

Nov 10, 2005

I have very large sections of data which I need to transpose as links.
Problem is that the copy/paste links function cannot be followed up with the
copy (the linked cells)/transpose option, without having the transposition
screw the links up.

View 9 Replies View Related

Update Links When There Are None?

Jan 27, 2009

i have a complex workbook. Recently, when I open it, it asks me if I want to update links. I never (that I know of) established any links to other workbooks. I see that a workbook is referenced in the "edit/links" menu, but I can find no way determine which cells, graphs, etc, in my workbook are subject to this link. I would like to get rid of this useless and bothersome issue.

View 5 Replies View Related

Links Broken

Mar 4, 2008

I've got an excel file with LOTS of links... it's a sort of 'dashboard' that draws in data from all the individual data files in the company.

Recently, when I open the file it's telling me "this file contains one or more links that cannot be updates".

So... as I usually would, I use my 'find links' add in to find anything with a #REF in it... nothing.
Go into edit links and manually update each link... and they all update with no issue.

I just cannot find anything that's broken!

View 9 Replies View Related

Break All Links

Jun 26, 2009

Is there a simple code I can add to the end of my macro that will break all existing links in the active workbook?

View 9 Replies View Related

Links Not Updating

Mar 23, 2007

I have several Excel files saved on a network drive at work. We run cost studies and have numerous links mixed in with these files. My question is, when we change data in one sheet, it doesn't update the other sheet where we have it linked. When you click on the address for the link you get, (\fileshareN:Raetc...) If I remove the \fileshare and leave it as N: the links update. What am I doing wrong here? It automatically adds this \fileshare piece when I add the link. When I have removed it and close it, the extra information is added and I am back at square one.

View 2 Replies View Related

Excel Links To Outlook?

Jul 21, 2014

link a date within a Excel sheet to the Calander in Outlook so a reminder can be triggered on said date.

View 2 Replies View Related

Looking For Formula That Links Different Conditions Together?

Jul 29, 2014

I'm looking for a formula that links different conditions together.

For example, I'm entering the formula into G9 and I want the formula to be if G7 is equal or greater to G16 then the answer should be 30, if not then the answer is 0.

Simple enough I know this is my formula to use "=IF(G7>=G16,30,0)" but I want it to look past that.

Basically I want it to say if G7 is equal or greater to G16 then the answer is 30, if not it's 0 BUT if G7 is equal to greater than G17 my answer is 60 BUT if my answer is equal to or greater than G18 my answer is 120.

G17 will always be a larger number than G16, and G18 will always be a larger number than G17.

View 3 Replies View Related

Dynamically Change Links?

May 21, 2014

I have a link

='S:2014DomesticATL[ATL-BP Eval-2014-04.xls]Business Partner Evaluation'!F$13

Now i have to compile 3 years of data for 200 airports so changing the month and year for every airport would be a challenge. I can do Replace all and change the year and month but instead of that i was thinking if i could have the year and month on one row and connect the links to that cell so that whenever i drag the cell my link changes

im looking at

2014DomesticATL[ATL-BP Eval-",D2",.xls]Business Partner Evaluation'!F$13

where in D2 i will have 2014-04 . But this formula doesnt seem to work. Is there anyway i can do this ?

View 3 Replies View Related

Edit Links Greyed Out?

Sep 10, 2009

I've read a bit on the web about there being a bug with the 'edit links' feature. How/Can I get this function to work?

View 6 Replies View Related

Links Do Not Automatically Update

Sep 11, 2013

I have a dashboard (CW Dashboard.xlsx) that is linked to 3 main spreadsheets. In each main spreadsheet, there are 5 tabs. One tab contains raw data, the other 4 tabs contain tables: 2 tabs with formulas based on the raw data tab, and 2 tabs linked to 20 other spreadsheets (for now). I separated the raw data into 3 spreadsheets because Excel was calculating way slower than my patience would tolerate. All spreadsheets are Excel 2010. All spreadsheets reside on our network drive.

The links update as expected on the 2 tabs that are linked to the 20 different spreadsheets in each of the three main spreadsheets.The CW Dashboard does not update. I get #REF! in each cell (although when I click in the cell, the link shows no error). I am using VLOOKUP and HLOOKUP in this spreadsheet. I can of course open the source documents from within the Dashboard and the links then work as expected. However, I would prefer the eventual users of the Dashboard not have to open 4 s'sheets.

View 2 Replies View Related

Links Breaking From Two Workbooks

Dec 18, 2013

For three years I've created a workbook (A) that is dependent on another workbook (B) I upkeep. When I open (A), I Edit Links and update the location to my newest weekly file to map from (B). For my 2014 (A) book, I did the same process, but when I update the values, all the links break. Only when I open up (B) with (A) will the values appear.

As a short term solution, I saved (A) with the values in there and disabled the prompt to update values, so others can see the data. This isn't a feasible long term solution since updates will be made weekly, and not everyone will want to open up (B) just to update (A).

View 9 Replies View Related







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