Changing Section Of Link In Formula?
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
ADVERTISEMENT
May 25, 2007
I'm trying to create a macro to help me with a running issues list. I what to be able to have an issues column and a fix coulumn. Then I'll have an open issues section and closed/fixed issues section. all issues that do not have any Information and date in the fix colum will stay in the open section unitl the fix is entered and then when I run my macro it will move any open issues that have data in the fixed column to the closed section)
View 2 Replies
View Related
Apr 2, 2014
Refer to the "Example" file I have uploaded. Basically I want to turn table on the left to something similar to the table on the right by adding an additional column to put in card number related to the transaction in each row, so I have information of the transaction and its corresponding card number in the same row.
Example.xlsx‎
View 7 Replies
View Related
Jun 3, 2014
I have a checklist that I need to have do some different things.
I have two "sections" - not really sections but paragraphs so to speak. On each first line I have a checkbox. Now if the user checks "section" 1, I need the checkbox to change to NA for the "section" 2 first line.
View 1 Replies
View Related
Feb 17, 2014
A B
17-Feb 5.00
19-Feb 12.00
22-Feb 7.00
26-Feb 10.00
I would like to see the sum of B given it is in the range from 17-Feb to 23-Feb. My estimations so far:
=sumif(A:A,????, B:B)
How do I set up a criteria which would take values from 17-Feb to 23-Feb?
I also tried =sum(sumif(A:A,{17-Feb;?;?;23-feb}, B:B) but it wouldn't let me.
Particularly the problem is in entering the date in the array.
View 1 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
May 27, 2014
I have an overview worksheet that features a list of dates; I want to hyperlink the dates to the another worksheet within the workbook that represents the month. I want to do this without changing the dates in the overview sheet. For example, I have 2014-05-23, 2014-05-24,2014-05-25 in my overview sheet and I have a worksheet titled May 2014. When I try to hyperlink the dates to the May worksheet it turns the dates to #### in the overview sheet. How can I stop that from happening? 
View 13 Replies
View Related
May 24, 2006
In a worksheet I want that a link changes, after I change a value in another cell.
Example:
When cell A1 is 2006_04 the link should be:
C:/2006/2006_04/[...xls]Page1'!A1
When cell A1 is 2006_05 the link should be:
C:/2006/2006_05/[....xls]'!Page1!A1
View 6 Replies
View Related
Jun 12, 2007
Basically I want to point my Excel sheet to a bunch of external Excel files. To get data from an specific cell (in this case, cell F10) of the external file, all I have to do is this: ...
View 9 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
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
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 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
Apr 2, 2007
I want when I choice the section name,The report will display only selected section sales.
I have tried the function " SUMPRODUCT", But it's display all sales regardless section name.
View 6 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
Oct 6, 2011
I am wanting to select a section of a cells data, which can be shown in a selected cell using a formula i.e
AB1350309280000768436000076843
In the example above I want to exclude the first 8 digits and the last 1 from cell A1 and show it in B1
View 3 Replies
View Related
Oct 7, 2008
When autosuming a section of a column, is it possible to conditional format a highlight of the 'autosumed section' so i can visually see where it is at all times?? (i.e. so when i autosum, automatically a highlight will appear over the autosumed section)
View 9 Replies
View Related
Aug 5, 2003
I want to include a help section in a workbook I created. I need something more than just a MsgBox ;-)
I'd like it to be like the windows' help that you have everywhere.
View 9 Replies
View Related
May 26, 2006
I am creating a spreadsheet that takes data from the previous week's spreadsheet - it's a weekly report. Using vba how can I access and copy a specific range of data from one worksheet on the previous week's workbook to a range in the new week's workbook. The data in the previous week's workbook is formulas, and I'm not sure how it could affect it, but I only need the values calculated and not the formulas to be transferred. I would also prefer it if the other workbook did not open in excel, so that it happens without any changes on the screen.
View 6 Replies
View Related
