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?
I have a worksheet that contains data in sheet1 Cells B2:B14. I need to copy this data into the Summary worksheet B2:N2 and to link it so that it updates on the summary Sheet.
When using Paste Special Transpose, it does not allow one to Paste Link. Is there another way to do this besides using a linking formula?
The code below looks at file names in column A and then goes to a folder and opens and copies the data in range c2 -lastrow from each file and pastes the data into sheet2.
how I could add to the code so that it also inserts the file name in column c?
It would make it easier to track the data in column B.
VB: Sub CopyFromFile() Dim fPath As String Dim lRow As Long
[Code].....
I have attached a sample workbook. The list of file name is in sheet 1. An example of the output is in sheet 2. The data in column A is dummy data generally spans 100's of rows not just 10 as in the example.
The purpose of the code is to be able to put a list of file names in column a in sheet 1 and extract data from those files in a folder. The data extraction works fine. The reson for adding the file names is so that I know what file the data came from.
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 have a workbook with 3 worksheets (year 2005 data, year 2006 data, and a total data sheet). I used PASTE LINK to display the data from year 2005 and year 2006 on the total data worksheet. The problem I am having is that any blank cells in year 2005 or year 2006 are being displayed as 0 on the total data worksheet. Is there a way to have blank linked cells displayed as blanks? I am working with Excel 2003.
I have this long macro that among other things copies data from one sheet to another.
Once I finished it I moved it onto a shared drive and now when it is run I get a popup reading: "Update Values: Book3.xls" asking me to seach for a file. I think it is trying to reference this other book that was in the folder where it was origionaly written, however there is no need for it to, its just processing data within the existing workbook... Ive used Ctrl+F and searched through the formulas to find book3 and it doesn't return anything...
the popup occurs at a line that is: "activesheet.paste"
how to undo this reference to another book? is there some way to supress it from looking outside the selected book?
Is there a simple way to combine Paste Link and the Paste Transpose function? I receive a lot of data in rows that I need to put in columns for display purposes. Using transpose is great for doing that but I would like to link it using paste link so I don't have to keep doing each time the data changes. I have recorded macros to see what is happening when I perform the operations seperately as shown below but have been unable to combine them.
I am trying write code withVBA to save separate file in a different folder with Loop.
Write a code with "loop" till it finds empty cell in the column and for every change in number a separate file needs to be saved in specified folder with file name as "10010, 10011,10012... and so on with data copied in the file saved.
Average formula that will give an accurate answer. My issue is that I need an average of what has been entered rather than for the entire selection. I have paste link transposed the data as it needs to run on Excel 2003 which can only hold 30 items in the Average(number1, number 2 ... etc) and I need 52. My problem occurs when the data is paste linked all blank cells appear as a Zero therefore increasing the count of the average and providing a wrong answer.
Eg when (1, 2, 0, 3) Average = 1.5 is entered the formula works out (1, 2, 0, 3, 0, 0, 0, 0, 0, 0, etc) Average = 0.12 because it divides by 52 instead of 4. As you can see I can't ignore zeros as they are a possible input. What would be perfect is for the paste link to paste the blank cells instead of the providing a zero then the plan =Average(B3:B55) would be fine.
(Note: the new entered data is on the same row but different columns hence the reason for the paste link transpose to get all the data in one column)
I would like to copy cells from one worksheet to another, but have the cells that I paste link to the original document. Is there an easy way to do this?
So if I'm pasting from worksheet A into worksheet B, I would like worksheet B to link to worksheet A.
Column A = Date Column B = Value 1 Column C = Value 2
I have a folder with excel files named by date (e.g. 081212 = 8th December 12). Within each of those files is Value 1 and Value 2. They are always in the same cell (B6= Value 1, B16= Value 2).
How can i create a macro/script where the Values in Column B and Column C in my spreadsheet are automatically updated where there is a Date in Column A but no values in Column B or C?
In other words, i need the script to read the date in Column A and if Column B and Column C are blank, then it needs to find the corresponding date excel file and copy Value 1 and Value 2 into the cells in my spreadsheet.
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.
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.
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?
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"
I have a Piece of Code that copys a Row in my Spreadsheet and Pastes it underneath the original Row.
The Problem that I am having is that within the Row it Copys there is a Spinner in it. I want the Spinner to be copied also but the Cell that the Spinner is Linked to Says as the Original Cell, it doesnt move Down with the New Row Pasted in.
Here is the code that i have written to Copy the Row and Paste it down a Row.
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.
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.
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:
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.
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.
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?
I have a file where the links to external spreadsheets need to be updated on a monthly basis. There are a couple of hundred cells containing links along the lines of: =IF(WEEKDAY(F2)=1,E11,IF(WEEKDAY(F2)=7,E11,'J:DAILYDaily ReportUKSeptember 2006[daily_file_AsAt_01Sep2006.xls] Stock - Consolidated'!$G$5))
I want to be able to enter a month and year and have all the links update...i.e if I type in October 2007 I want the links to change to:...............
