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?
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)
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.
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.
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?
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? 
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: ...
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?
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.
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.
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.
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
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:
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:
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
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?
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.
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)?
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.
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.
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)
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.
