Formula Link Across Spreadsheets
Jun 12, 2006
I created a summary page for some reports I do and I'm having trouble keeping them linked together as my original data has rows deleted and inserted. There are 30 spreadsheets worth of information and I have them linked to a summary file. Each month I insert a new tab to each spreadsheet. I set up all of the formulas last month but see a problem this month when I copy and past the formulas to the new tab. On the 30 spreadsheets my data moves up and down so the row referenced in the summary formula is wrong. I am looking at having to type "=, click on original spreadsheet, select cell, enter, repeat" all over again. I pull 5 numbers from each sheet, and have multiple formulas to double check the math so I would be repeating this process over 300 times.
After all that, my question is...Is there a formula I can use that will pull this for me without needing to be edited each month. One idea I thought would work is to tell the formula to equal the first cell in Column B that is in red text and I could just change the font color on the orginial spreadsheet. But, I don't know how to make a formula say that or if excel can even do that.
View 9 Replies
ADVERTISEMENT
Feb 6, 2014
I have to enter data into a spreadsheet daily and update a line graph with the data for 10 different production machines. I want to place the charts on their own separate excel file that way they can be easily viewed without the data next to them. I tried copying and pasting the graphs to another excel spreadsheet, but when I update the graphs on the original excel file the data does not change on the one I copied the graphs to. I use the select data range option to update the line graph data. Basically, I just want both graphs to update at the same time on both spreadsheets, but the links don't seem to update. How would I link the graphs so that both update across files?
View 2 Replies
View Related
Apr 21, 2013
I have a Excel Spread Sheet which has been created, there will be 4 people in 4 different locations who will have access to this spreadsheet and will need to update/amend it through "Dropbox", my question/problem is, how can I link the 4 spreadsheets where information will be updated by one person, but will update all 4 sheets at the same time.
Is this possible and how do I go about it?
View 4 Replies
View Related
Jun 28, 2013
I have two worksheets, one with detail monthly information and one with YTD information. So let's say the three numbers I want to capture in the YTD sheet are in columns B, G and I on the monthly sheet. January's data might be in B5, G5 and I5. February's data is in B12, G12 and I12 and so on.
On the YTD sheet in cell C2 I link to Monthly!B5 and in C3 I link to Monthly!B12 so cells C2 to C13 on the YTD sheet show the monthly totals from column B on the detail sheet. On the YTD sheet, cells C20 to C31 show the monthly totals from column G on the monthly sheet, so cells G5, G12, etc. And finally, cells C40 to C51 on the YTD sheet show monthly totals form column I on the monthly sheet.
In the past I've always created all these links manually. After creating the links in C2 to C13 on the YTD sheet, is there a way to use a formula in C21 that uses the link in C2 to create a link for G5?
View 6 Replies
View Related
Apr 2, 2012
I have a workbook containing 101 sheets on the first sheet is a list of numbers in H1 to H100. Sheet2 will have the value in Sheet1 H1 in cell G3 then Sheet2 will have the value in Sheet1 H2 in cell G3 and so on for the 100 sheets Sheet101 will have the value in Sheet1 in cell G101 in cell G3 how do I automate this task. This would be easy on a small number of sheets to enter manualy but I dont fancy entering into 100.
View 7 Replies
View Related
Aug 25, 2006
I have 2 spreadsheets. One of them has various lines that sum different lines from the other spreadsheet, e.g.
Sales = 200100
Expenses = -300345
Other Income = 120000
Selling and Admin = 35000
The other spreadsheet has a lot of details that make up this first spreadsheet.
e.g.
Paper 100000
Cardboard 50100
bottles 30000
cups 20000
These add up to the sales number for example.
Is there a way that I can use the solver or something similar to get all the cells that relate to the first spreadsheet in one shot, and so I don't have to first find what the sales number is made up of, and then what the expenses number is made up off..can I look for the relating cells in one go. Is there a code or something that I can use.
The 2nd spreadsheet has a lot of rows, and it's quite difficult, also I find that when I look for the sum of the sales number and try looking for the expenses number, it gives me cells that are related to the sales number as well.
View 5 Replies
View Related
Jun 23, 2014
I have multiple spreadsheets that have formulas pulling from other spreadsheets that I use for daily, monthly & annual reports. After upgrading to Windows 7 from Windows XP these spreadsheets are no longer seeing the formulas if I open multiple spreadsheets at a time. This is a problem as my monthly and annual spreadsheets need to pull from multiple workbooks in order to give me monthly and annual totals. I have macros set at "Enable all macros" and have added the file location in "Trusted Locations". What else do I need to set in order to work on multiple spreadsheets at a time and the formulas pull data as needed?
View 1 Replies
View Related
Jul 31, 2006
I’m working on quite huge spreadsheets. The same formula will be used on more than 30 sheets.
So far if I have to change the formula in one cell I have to go to each sheet and change it manually (which is quite boring and time consuming).
I’m wondering if it’s possible to reference all the formulas on the sheet to one sheet (which some kind of a master sheet with all the formulas).
I can copy the formula with the “Past Special” function but the function is not updating if I change the formula on the main sheet.
So is it possible to link it and that it’ll be updated automatically when I’m changing the formula on the main sheet?
View 9 Replies
View Related
Feb 4, 2014
I've created a spreadsheet that I use to calculate weights of steel members for my job for estimate purposes. I'm trying to figure out how to save some time by not having to enter/copy/paste formulas every time I do takeoff for an estimate.
The bold green cells in column K are always linked to the yellow "Part Heading" above in Column A. As I do my estimates, I copy and paste a "Part Heading" with a "PL/Member" formula below for the next piece I'm working on. Every time I paste a new "Part Heading" with a "PL/Member" formula, I must change the formula in Column K to link to the next cell up in column A with a value present. In the screen shot, cell K/90-K/96 are linked to cell A/89. Cell K/83-K/87 are dependent cell A/82; K/74-K/80 are dependent on cell A/73, and so forth. Since each "Part" varies on total number of "Members", I can't seem to write a formula that will stick when copying and pasting.
My question is: Is there a way that I can automatically link each cell in Column K to the next cell up in Column A with a value present?
View 7 Replies
View Related
Jun 23, 2009
Within my spreadsheet all the formulae are sums of cell references from 7 other documents. I now don't need one of the documents and therefore want to remove just that one link only from the formula. I would do it manually but I have hundreds to change! I have tried breaking the link to that one document but that removes the entire formula.
View 2 Replies
View Related
Apr 21, 2012
I have a formula in the target workbook in cell C3 =-'data.xls!'np2011'
However, when UI copy the formula to D3 , the formula remains as =-'data.xls!'np2011'
2011 after np is the year. The names in the source workbook have been named np2011, np2010, np2009 etc
np2011 is a range name in the source workbook. When I copy the formula to d3, it should change to -'data.xls!'np2010'
In the target workbook I have the years in cells C1 to L1. How can I get the formula to change when copying/ Alternatively can one concatenate the range name to C1 for eg such as ="-'data.xls!'np&c1&"' I have tried to do this, but cannot get it to work.
View 5 Replies
View Related
Jan 5, 2014
How do I get rid of the link??? My formula seems like it is not working because it has a link which I cannot find anywhere.
dog.xlsx
Screen shot 2014-01-05 at 2.38.08 AM.jpg
View 6 Replies
View Related
Jan 20, 2014
It's a new year, which means new tables and lists. I have a list of 450 People with a reference to their own individual file. They all receive a new file at the start of the new year that I Need to be able to reference in a new list. Here's an example
Joe Bloggs has a cell next to his Name referencing
[Code] .....
In 2014 Joe will have 'Bloggs 2014.xls' as will 449 of his friends, each named individually. The only part that changes from 2013 to 2014 is the date in the Name of the file. Can I somehow just Change that one part of the link without having to manually relink everything?
View 4 Replies
View Related
Jan 9, 2008
I have one list in one sheet that is source for the userform and combobox used on another sheet. With userform I make a choice and data is entered in one cell.
But is it possible to compare the choice that I make in combobox with the list on the first sheet and then make a simple link between these two cells, so that cell in the second sheet is always showing what is in the cell on the first sheet.
Reason for this is that items on the list on the first sheet sometimes change and I need to update the second sheet automatically.
Private Sub cmdOK_Click()
With Sheets("Presentation").Range("V18:V32")
Set c = . Find(cbochange.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
ActiveCell.Offset(0, 2).Formula = firstAddress
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Unload Me
End Sub
View 3 Replies
View Related
Oct 14, 2012
Im looking to insert a formula in vba code that inserts the formula from a closed workbook to the active cell. At the moment i have this as an example of what i need:
VB:
ActiveCell.FormulaR1C1 = "='[SRS 5FFBEP.xlsm]7%'!RC"
However the file part in square brackets [SRS 5FFBEP.xlsm] and tab 7% i would like to replace with a link to a cell reference that contains the file path as this needs to change and update so i would end up something like this:
VB:
ActiveCell.FormulaR1C1 = "=('" & Workbooks(Worksheets("SRS Files").Range("d29")).Worksheets("SRS Import").Range("c10") & "'!RC)
Note that cell Worksheets("SRS Files").Range("d29") contains the full file path (in text format) and Worksheets("SRS Import").Range("c10") is the link to the tab that reads the 7% (in text format).
As it stands the first set of code works fine however i need it to be able to change as previously stated. So what i need is to replace the file name and tab with cell references
View 1 Replies
View Related
Oct 26, 2009
We're using SUMIF and it won't work unless the external excel file is open.
This is the formula we're using:
View 7 Replies
View Related
Jul 3, 2007
I have a worksheet. In sheet3 The value of column L2 is the value of column M2 in sheet . Everytime I copy the new sheet, I have to re type the formula for this new sheet. Is there a way to give formual such that when I copy new worksheet the formula will come automatically as it always has to link from the M2 column of the previous sheet?
View 9 Replies
View Related
Dec 31, 2008
I have a Date in Cell A1 Page 1 and I need a formula to put in Cell A2 on Page 1 that if there is a "matching" Date in any cell in Row B1-B7 of Page 2 that the data in the corresponding row C1-C7 on Page 2 will be placed back on Page 1 Cell A2.
View 9 Replies
View Related
Jan 8, 2010
I'm creating a spreadsheet which will have a grid of hundreds of check boxes (used to record progress on a project). In order to use the value of the check box in calculations, I'm linking each checkbox with another cell. So, if the checkbox is in cell F10, then i might link it with cell AF10, which is where the TRUE and FALSE indicators are.
If I copy the last row (which has checkboxes--let's call it row 99), and copy it below to expand the grid, the check boxes in the new row are still linked with row 99 cells (i.e., all the new check boxes in column F are still linked with AF99). So, presently I'm having to go through and manually re-link each pasted check box to a new cell.
Is there a way, using something along the lines of the INDIRECT function, where I can have a formula dictating what cell each checkbox is linked with, so that when I copy and paste a large number of these, they automatically point to the new cell (such that, F99's check box points to AF99, but if I copy this F99 check box into cell F115, then it would automatically be linked with cell AF115)?
View 9 Replies
View Related
Jul 24, 2006
Can cell comments be returned together with the results of Index / Match function. If not is there any way to link the coments ?
View 3 Replies
View Related
Oct 24, 2006
If I have a two files called test1.xls and test2.xls for example. Is it possible to link test1.xls to test2.xls by putting the filename in a cell?
i.e. In test2 put the filename in say A1 and in the link put the path and instead of naming the file you want to link to put in A1 instead.
I know it doesn't work if you do it that way I was just wondering if there was a way to do it.
View 5 Replies
View Related
Nov 17, 2006
Not sure if anybody has been succesful or not in the following but I am writing a macro to check that each hyperlink in my report is operational. My situation is as follows:
Cell A1 has the following hyperlink formula: =hyperlink(Worksheet1!B1,Worksheet1!B2)
I am attempting a workaround to capture the cell reference (B1) where the path and filename is stored and directing the macro to open the link. But when I try to send the hyperlink formula (Cell A1) to a string variable in vba, the result is the contents of Worksheet1!B2 instead of the entire formula.
View 2 Replies
View Related
Jan 22, 2008
I have a problem that may be a little bit diff. from must of the Readonly post that I have found here. Most people would like to have all of there files opened as readonly files, but I don't in this case. I am copying a number of files that has VBA code in just about all of them. I am using a CD writer to copy the files to a CD using Windows Explorer and that is working just great, No problem copying the files.
My problems comes in when I copy the files from the CD to another computer, all of the files are Readonly files on the new PC. I have not saved them as readonly and don't want them to be readonly files. Does anyone have any ideas why this is happening what I need to check or change to get the files to another computer without being a readonly file.
View 2 Replies
View Related
Mar 9, 2014
I have an excel that has dropdown column from form control, in F4 there is a formula of vlookup that take the range from A2 to B20 in consideration but the reference cell that is given to vlookup formula to pick corresponding value is E4 which is also the reference cell of dropdown box and therefore its not showing any name rather than a value, problem with the formula is that when we select any name from dropdown it does not respond to any value in F4 despite changing values in E4.
I am using these setting for the first time.
Image & excel file is attached
drop down.png
View 7 Replies
View Related
Nov 9, 2009
I'm trying to use some data from other workbooks in a formula. However, the path to the other workbooks we are using will often change.
I think that the user can enter the path to the other workbook in a cell in this workbook. However, I'm getting errors in my formula and am not sure how to make the formula use the cell to reference the location of the workbook that has the data that we want.
This is what I have, but it's giving me an error.
=Countif(' & 'Instructions'!B9 & Planning'!$I$3:$I$100, "C")
'Instructions'!B9 contains the location of the other workbook in the format \path[filename] Planning'!$I$3:$I$100 is the location of the range in the other workbook.
View 7 Replies
View Related
Jun 5, 2013
I have simple Countif and Averageif formulas that I would like to have linked to variable data in cell E6 on the Assumptions (2) tab however, the formula is not taking the cell as the criteria and only taking a hard number when I am using >=.
The example would be: I3 needs to be able to use the data changed in E6 for the >= number instead of the hardkey 29...
View 2 Replies
View Related
Feb 1, 2008
I want to be able to get a value from a specific cell in another workbook, the problem i have is that i want to be able to select what file the active workbook looks for from the start page of my current workbook. For example when i load the workbook i get my startup page ( sheet 1) where i will be able to select what external workbook the active workbook will be linking with, i will then link the active workbook to the value in a specific cell from the selected external workbook.
I know to link to another workbook i would use ='[filename]sheet!cell
However i want to be able pull up a 'open file dialog box' which i select a file name which will be used as 'filename' in the formula above.
View 2 Replies
View Related
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
Jan 27, 2010
I'm in need of a formula, or another automated technique, to link a column of data from one worksheet to another. I have an input worksheet that I copy the raw data into that is formatted as follows:
COLUMN A
Row 1 12001
Row 2 12004
Row 3 12011
Row 4 12020
Row 5 13050
etc...
I need this import sheet to link to an output worksheet that would be updated automatically as new data is entered into the input sheet. However, I need the output sheet to copy the input values a specified # of the times and list them. For example, if the specified # was 3, it would be formatted as follows:
COLUMN A
Row 1 12001
Row 2 12001
Row 3 12001
Row 4 12004
Row 5 12004
Row 6 12004
Row 7 12011
etc...
I need the formula, or method, to be one that I don't have to continually re-enter as the data changes, as I have thousands of records to do this with. I've tried filling down, but autofill doesn't recognize an accurate trend. Is this something that I can accomplish by combining several fomulas, creating macros, or a combination of the both?
View 2 Replies
View Related
Dec 4, 2007
I have worksheet1 with 200+ customers and 12 months of data for each customer. I want to link each month to a separate worksheet (worksheet2, 3....12) for all customers. (i.e., customer 1's January activity across row 1, customers 2's January activity across row 2.....)
Is there a way for me to create a formula or copy comand to link row1, worksheet1's data to row1 worksheet2 and then every 13th row in worksheet1 to row2, row3...in worksheet2?
Right now I'm just putting the = in worksheet2 and mousing over to worksheet1.
View 9 Replies
View Related
