Link Formula To File Based On Cell Value
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
ADVERTISEMENT
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
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
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 14, 2007
I'm sure this is an easy one but it's unfortunately beyond me. I have a update links formula in which I would like for it to update with the name of the file being in a cell. Example:
The file name is Week20.xls
Cell A1 has the value of Week20.xls using a formula (="Week" & A2 & ".xls")
Currently I have it by looking at the actual file location like this: ='C:Documents and SettingsMy DocumentsWeekly Sales6 Sales Comparisons[Week20.xls]Daily Comparisons'!$I$4
But rather then pointing directly to the file path I want it to update by the name of the file in A1. So I'm sure its something as simple as replacing [Week20.xls] with some formula but I don't know what that one is.
View 3 Replies
View Related
Jan 9, 2012
I have a restaurant and every evening the manager emails me a sales report. The names of these files are all based on the day's date so I know what the file name will be in advance (e.g. today is 20120109.xls and yesterday was 20120108.xls)
I have a master spreadsheet collating the monthly sales data. It links to the daily spreadsheets with links like this:
=IF(ISERROR(VLOOKUP($A5,'C:UsersSimonDocumentsPersonalTOSEADaily Sales[20120105.xlsx]Sheet1'!$B$6:$F$50,3,FALSE)),0,VLOOKUP($A5,'C:UsersSimonDocumentsPersonalTOSEADaily Sales[20120105.xlsx]Sheet1'!$B$6:$F$50,3,FALSE))
Each day I create a new column and drag the previous column's formulas over. I then do a find and replace to change the file name (20120105.xlsx in the example above) to the current day.
What I want to be able to do is change the filename reference in the formula above to a cell within the master spreadsheet that contains the date, so that when I drag over a new column it picks up the date from that cell and automatically uses it to create the filename in the link. The path for the file name remains the same.
View 3 Replies
View Related
Jun 26, 2014
There is a problem in joining one cell to the other file
The below is screen shot when l click the on every cell the image must be open
How can do it?
Capture.PNG
View 1 Replies
View Related
Feb 22, 2012
In short I what to be able to check a check box and have the adjacent cell, which contains the tab name, to then reveal that tab and add a link to the tab to cell contain the tab name. I have done this with copying and pasting existing linked cells, but now I have 100 more to do and I do not want to write repetitive code to get it done.
View 4 Replies
View Related
Nov 15, 2006
I need to hide columns in a spreadsheet without using the hide/unhide columns option. I found an article which suggested setting up a combobox from the forms toolbar, this is populated by an input range and linked to a cell for a numeric value. The article although using a macro to hide rows suggested this code
Private Sub ComboBox1_Change()
If ComboBox1.Value = "A" Then
ActiveSheet.Range("1:3").EntireRow.Hidden = True
ActiveSheet.Range("4:6").EntireRow.Hidden = False
ElseIf ComboBox1.Value = "B" Then
ActiveSheet.Range("1:3").EntireRow.Hidden = False
ActiveSheet.Range("4:6").EntireRow.Hidden = True
End If
End Sub
I want to amend this code so that when the number in my linked cell changes the various columns are hidden.
For example if the cell link number = 1 hide columns e to az if the cell link number = 2 hide columns d and f to az etc.
I have had varying success with amending this code to entirecolumn.Hidden and changing the Combobox1. to dropdown1. but am unable to get it to work, I also stupidly deleted the code that was partially working and am now unable to remember what I'd done.
View 9 Replies
View Related
Apr 29, 2008
I have a spreadsheet of various works-in-progress items, the first three columns of which refer to relevant documents, effectively the request, the estimate and the work order, i.e. RXXX-0001, EXXX-0001 & WXXX-0001.
So the text is entered as above (without file extension as it's also our reference number), and I'd like each to be linked to their respecive .doc files, which are held in different locations, i.e. \servernamefolderRequests or Estimates or Work Orders
e.g. \servernamefolderRequestsRXXX-0001.doc
There's a backlog of over 300 items (so 900 links) so I don't want to go through and manually create the links and as each day can see over 12 items being created it'd be much easier to have it done at the click of a button
Most of the solutions I've seen involve creating a new cell/button with the hyperlink in it - I just want to effectively "right click, create hyperlink" but with the cell contents inserted as the file name (adding ".doc") and with correct folder name too.
FYI the cells are adjacent to one another e.g. A1, A2 & A3 so the selection can be Offset (0,1) for the estimates and work orders.
I don't mind if it ends up being a case of running the macro on each line - some links have already been created already.
View 9 Replies
View Related
Apr 24, 2009
when I link cells from one file to another and insert a new line on the file I am linking to. the file that is linking goes down as follows:
=Safety.xls!$A$5
=Safety.xls!$A$6
How do I get Cell A5, to link to Cell A5 even if I add a new line in the Safety.xls spreadsheet. Taking the $ away does not work I have tried that.
View 3 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
Oct 17, 2006
I'm trying to make a floor plan which shows what seats are available depending on the time it is. So I made sheet1 = to the map (where the seats are located), sheet2 - rows = seat #'s, columns = Time (broken down by half hours). Now i have conditional format to show "t" in red (which means taken), "f" in blue (which means free), and "n" to show in gray (which means not working).
What i need is example - Seat # 200 in sheet 1 to show red, blue, or gray depending on the value in the row that corespond to this seat and the column that corespond to the current time.
View 9 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
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
Jan 13, 2010
Is it possible to link a cell e.g. A10 that has a time value in it (there is a formula in this cell that puts in the time value) to another cell in a range of cells e.g. B1:B1000 that has the same time value?
I know someone out there knows how to do this.
View 9 Replies
View Related
Oct 22, 2009
I have attached the file I am working on. I am attempting to create a link to an external file based on the value of cells in column A. Then I would like to simply copy the formula down, lets say in Column B, the rows and as I do the external file reference will change depending on the value within the cell in Column A. I hope that I am making sense.
I am using Windows Vista with Excel 2007. The files will all be within the same file folder, however, there are hundereds of files so I won't be able to open them all for the indirect to work.
View 9 Replies
View Related
Jul 10, 2014
I have a Workbook that already has a macro in it that will generate multiple sheets based on certain criteria.
For each of these newly generated sheets (numbered 1-6 in the attached example), I need to be able save each of them to a PDF file based on a unique name contained in a certain cell (in this case, each named is referenced in cell Q1 of each sheet). As such, I should end up with 6 pdfs based on the attached sample files.
The PDFs would ideally need to be landscaped and should be just 1 page per sheet.
In my attached sample workbook, you'll notice that I have a "PracticePrint" macro that doesn't quite get the job done. One other criteria involves not printing any sheet with the characters "1010" in it (my PracticePrint macro is setup to do that, but it fails to do much else).
View 1 Replies
View Related
May 16, 2013
My raw data is exported in CSV format. Is it possible for Excel to link to the data or can it only link to an Excel format workbook?
View 1 Replies
View Related
Jul 25, 2008
linking a CommandButton to another File. I made a CommandButton but would now like to know how to write the Macro to make that Button, when clicked, bring me to a file saved in Excel.
View 9 Replies
View Related
Jan 29, 2009
I have some code that looks at the part # in column D and then goes out to the D drive to drawings (.pdf files) and creates a hyperlink.
The problem is that it is only creating a link to the .pdf file and not to folders. I need a hyperlink to the folder so when it is clicked the open folder pops up and shows the multiple files associated with that part.
Link should be the same regardless of file or folder, no? As long as the name matches link should be made, correct?
E.g.
123-100.pdf (column D part #) links to 123-100.pdf file on CD
456-200 (folder name in column D) no link created to folder named 456-200 on CD.
Sub Hyper()
MyPath = "D:"
StartRow = 2
EndRow = 20
x = 0
For i = StartRow To EndRow
Cells(i, 4).Select
MyFileName = ""
MyFileName = Dir(MyPath & Cells(i, 4).Text, vbNormal)
If MyFileName "" Then
x = x + 1
Cells(i, 4).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="D:" & MyFileName
End If
Next i
End Sub
View 9 Replies
View Related
Feb 26, 2007
What is the script in order for me to link a MS Word file to excel. What I intend to do is to customise the commandbutton, so that once a user click on it, the linked MS Word file (stored in the local drive) will pop-up and he/she can do the necessary amendments to the file?
View 3 Replies
View Related
May 18, 2007
I have a 'master' worksheet, which contains a list of active workbooks. Each row on the master contains several data elements from each of the active workbooks:
File#, Date, Status, Customer, Etc.
This is accomplished by copying a row of data in the active workbook and pasting as a link on the master. When an active workbook data element is changed (date, or status, for example), the link on the master changes as well. Then the active workbook is closed until needed again. The linked data on the master allows for an overview of active files, and for sorting based on certain criteria, such as the next date to action the file.
What I now wish to do is eliminate the process of copying & pasting; this would be done, in theory, by putting a formula in each cell of the master that references a variable: the file number, located in column A.
Thus, where the pasted link formula reads:
='C:Active Files[4545.xls]Home'!$O$1
The desired theoretical formula would read:
='"'C:Active Files["&$A25&".xls]Home'!$O$1"
View 3 Replies
View Related
Dec 21, 2012
Basically, at month end, my company will create a new folder with the name as YYYY_MM (e.g. 2012_11). In this folder, a new file will be created as: earnings_YYYY_MM (e.g. earnings_2012_11).
What I want is to automatically update the link to the most recent file: earnings_YYYY_MM, whenever it is available. For example, next month there will be a new file earnings_2012_12 created in the new folder 2012_12, then the new file will be linked with.
View 1 Replies
View Related
Feb 22, 2013
I have a "cost.csv" file that contains the cost of the toys. The cost is show in $ with two decimal places i.e. $0.75
I created a new MS Excel 2010 workbook file named as "customers.xlsx" that has four columns i.e. Item, Cost, Order, [Total Amount] columns. I used the paste link command and the Cost column data is linked with the cost column in "cost.csv" file.
When I write order quantity in the order column and then try to use the following expression in [Total Amount] column i.e. "Cost * Order" then I received error due to following reason i.e. "Cost is a text value and Order is a numeric value and excel is unable to evaluate the expression." I have done it by first remove the $ sign from cost using RIGHT and LEN FUNCTIONS.
Now I want to change the format of "Cost" column i.e. I want to show the cost in Euro but not in $. Here I am stuck as I am trying to change the format of the Cost linked cell but unable to change it either I applied the new format, the text remains shown $0.75?
Is this the problem as data is coming from .csv file? If it is then how I can accomplish the task?
View 2 Replies
View Related
Jun 27, 2013
I have attached 2 files to explain what I really need:
In file A.xlsx I have 2 sheets. In the B1 cell from Sheet 1 I have created a data validation criteria based on the sheet 2.
I would like to get that same data validation criteria based on the A.xlsx onto the B.xlsx.
Is it possible? If not do you have any trick, solution, workaround to link a list from one excel file to another?
View 4 Replies
View Related
May 19, 2014
I have a template excel file that has links to data in another excel file in the same directory as the template. However, I save the template with a new name in a new folder and the link no longer works. The link changes to the location with the new file and of course the file I am trying to like to is not in that location.
The link shows the full path in the template file so I would think it would keep the full path when saving to a new location but the file doesn't.
How do I make the link address static?
View 6 Replies
View Related
May 31, 2006
I'm using Excel 2002.
I have one workbook with data linked to another CSV file (It's about 40000rows). When I open the workbook, "THis workbook contains one or more links that cannot be updated." message appears and asks me to open csv file if I wanna to update (although I set full path for links in cells). I wonder if there's any way to update link without opening csv file? Or Excel can not update link without openning the resource file?
View 3 Replies
View Related
