Transferring Files To A New Location Messes Up Formulas
Dec 22, 2009
I have a bunch of spreadsheets in one folder with several subfolders. On one spreadsheet, I have a bunch of formulas referencing another spreadsheet. This is an example of the formula: =C:UsersKatie DestktopMake Your Day Program8th Grade[Math 8,xls]Sheet1'!G2.
I am building this program at home, but then when I take the Make Your Day Program folder and put it on the J drive at my school, the formulas still try to reference my desktop instead of changing the formula to J:Make Your Day Program8th Grade etc.. Why? It is a pain to go through all the formulas and change them to reference the j drive because there are a LOT, and I have already done that once.
How can I ensure that when I transfer this program to the school drive, after the holidays, that the formulas will change to their new reference location?
I have just added two date and time picker controls to my spread sheet to allow the user to select the date range.
They are linked to cells C9 & C10.
The problem is that the formula I using to check whether the first selection pre-dates the second has stopped calculating correctly when using the date and time pricker controls.
The formula is:
=IF(C9>C10,"Error","OK")
I have checked it on the same sheet by just typing the dates in and it works fine...I have tried formatting cells C9 & C10 to short date, general, text, number but it still does not calculate correctly.
I am trying to have an entire line of data copied into another tab depending on the status of a claim. For instance, if the claim on Tab A has a column for its status and I enter approved, then the entire line is copied and added to the tab for all approved claims (Tab B). If I enter denied, I want it to go to a different tab (Tab C), and and if I enter pending, yet another tab (Tab D).
Basically i want to be able to look at Tab A and see a complete list of all claims but then be able to click to Tab B and see an list of all approved claims, and so on.
I am in the process of transferring data from one workbook to another.
I am doing this simply by clicking on the cell where I want the data to be, typing "=", then clicking on the cell in the other workbook I want it to match. While there might be easier ways to do this, it isn't too terrible and my columns are not in the same order in the two workbooks.
The problem I'm running into, is when I click on the cell I want to copy over, the formula defaults to the fixed formula (puts $ around the cell name) and I want to be able to copy down the formula over multiple cells.
I can click on the cells and press F2 once then F4 three times to get the formula over to the desired non-fixed format, however, I have to do this for 12 different workbooks and on roughly 40 columns for each.
Is there a way to expedite the process or turn off this fixed-formula default?
Is it possible to have the formula written in one remote cell, so that any modification done in this formula is reflected on all places where its refereced to?
z26 : Formula A
A1 : Formula A A2 : Formula A A5 : Formula A A6 : Formula A A7 : Formula A A9 : Formula A A11 : Formula A
in this example i store the formula in z26 and variety of cells in A are using it (not all of them)Suppose if i change anything in z26. I want it reflected in all the cells that are using this formula?
I have a folder with many subfolders and I want to search ".xls" files from these subfolders and then cut and paste them to new location. There is a code available over interned for searching files in folder but apparently it does not work in newer versions of Excel.
I am using xp pro, excel 2010 and the ftp server has linux installed. To open files from server I press open button in excel, then choose ftp location from drop down menu. FTP logon box asks password with the option of default user selected. I provide password and go on.
Some days ago something happened, I don't know what but as a result of it when I opened the FTP logon box, the anonymous option was selected by default. So I selected user option, gave id password and hit ok. It did not connect. Then I removed that ftp location from excel ftp locations. Whenever I tried to add an ftp location, excel restarted stating error message " *** encountered a problem *** ". So I reached a site after some googling and was directed to add the whole ftp file location path in the "File name" box of the open dialog box. It worked and the ftp location was automatically added in ftp locations. [ any ftp setting in excel is automatically transferred to ms word as well]
PROBLEM > Now I have a different problem. I can access ftp folders and subfolders but when I select a file and click open, downloading bar at the bottom of the excel does not start and after a long wait an error message pops up " the internet address ' ftp://192.168.****/folder/subfolder/filename.xls" is not valid. [I can open ftp files using filezilla etc]
What I need is to insert a row at row 59 and autofill the formulas including drop down list from the above row. This is what I have so far (Thanks to Reafidy and shg).
Sub ChkDates() Dim c As Range Dim DelRng As Range Dim ArcRng As Range Dim i As Long Dim l As Integer Application. ScreenUpdating = False Worksheets("Report").Activate For i = 60 To 8 Step -1 Set c = Cells(i, 33) If IsDate(c) Then ..............
I am attempting to write an Excel macro that will be stored in a file called MacroFile. The purpose of the macro is to
1. Follow a hyperlink to an Excel file saved in a SharePoint type enviroment 2. Save the file to my laptop directory My Documents.
Below is the code I have written. The code is following the hyperlink and saving a file but is the focus file is incorrect.
Here is what happens:
1. Open up MacroFile and run macro 2. Hyperlinked file LinkedFile_1.xls is opened 3. File NewFile_1 is saved but contains the info from MacroFile 4. Hyperlinked file LinkedFile_2.xls is opened 5. File NewFile_2 is saved but contains the info from LinkedFile_1 6. Hyperlinked file LinkedFile_3.xls is opened 7. File NewFile_3 is saved but contains the info from LinkedFile_2
The files created are named correctly but have the wrong data in them. I need to know how to control which file is considered ActiveWorkbook.
Sorry that I'm very new in Excel VBA coder. And, for this topic, I don't even know how to start. I want to make a MS Excel database of a numerous files. This database must be consist of Filename, Location, and it's attribute (let's say updated date, size, hidden status)
I have the following code written but I'm wondering if it's possible to modify this to change the red line to update to the path that the workbook is saved in? Meaning that User1Folder1 would change but [Workbook1.xlsm]Sheet1'E1 would always be the same.
I have 40 files in one folder which I named it as "CA" + month's name that I am working on. I need to do analyse these files monthly and save it under new folder. how do I automatically save them in new folder and name them for that particular month. Also, each file has worksheet which has one cell as "Aug-07" and the cell next to it has number of that month that is "08". How do I automatically change this also based on the name of the file, because file name month and month in the cell are the same.
what I do with excel: I have an excel sheet that has over 18,000 rows in it. Since it would be a nightmare to scroll around to find what I want, I use the Custom Sort and Filter options under Editing>Sort&Filter. So for example, I can omit 17,800 rows using a specific setting so that I can work with a more reasonable 200 rows. Moreover, the 200 rows comes from all over the spreadsheet. Meaning their row numbers are not always consecutive.
Here's the problem: Whenever I try to copy anything from this "edited or filtered" excel sheet, the resulting paste is not an exact copy. Excel perfectly copies the first rows up until the point where the row numbers ceases to be consecutive. So, the copy function messes up somehow when the data being copied comes from a different section of the original 18,000 rows.
In case this isn't clear enough...
Let's say that the original file has rows 1,2,3,4,5,6,7,8,9,10
Once I filter/custom sort, I see rows 1,2,3,8,9,10
When I try to copy/paste 2,3,8,9, excel copies 2,3, but messes up the rest of the 8,9. And I end up with a totally useless copy that's generally shorter than it's supposed to be.
I require a bit of code that locates where the excel file is stored - it then searches that directory folder for all file names. Any file names with an extension *.hm for example are listed in column A, while their relevant 'Dates of last Modification' are listed in Column B.
Every time the spreadsheet opens it should ask the user if he/she requires a re-search of this directory and update of any file names accordingly. NB: if a file name is deleted from the directory, the file name should remain in the spreadsheet.
I am working on a file that has multiple worksheets with many links, lookups and formulas; some between the worksheets and some external to another Excel file. The plan is to use this file as a template and copy it over and over with new names. Once I save the file as a new name in a new folder on my network, will I lose all these links, lookups and formulas? Or is there a way to maintain the links or do a global change of the formulas (i.e., the original file name is "TEMPLATE" and the new file name is "PROJECT!1")?
I got a workbook that is linked to many external workbooks and it is very slow to calculate, so I work it with manual calculation and only calculate the sheet (SHIFT+F9) when necessary. To work with this workbook I need to be able to make faster manual calculations. I have tried many solutions and the only one that seems to work, is to work without the links to the other files (by breaking the links and turning the formulas to values). The problem with this solution is that I can't lose the formulas. I think that must be a way to calculate the sheet without calculating the cells that got a reference to the external files. Only the formulas in the current sheet.
I am trying to develop a spreadsheet that will calculate a cost based on a matrix. I am attaching a sample of the calculation created so far. The end result is in cell M13 and is highlighted in yellow. I kind of layed the formula out in a few different cells, so hopefully it would be easy to follow.
simplify this process with maybe another formula that I might not be aware of, or maybe show me how to get this done in VB code. I think VB code would be the correct way to go just not sure.
I have a macro that I use on this computer, and I want to put it on my coworkers computer...I am having trouble...I have the vba code in a text file, I emailed it to her, and then put the vba editor on HER computer, picked "insert new module" and then pasted it...but....no go....
I'm trying to print score cards from the scores sheet (two different worksheet pages). Some score cards (each score card is one page) will only have 3 players and some will have up to 5 players so I can't just go down the list and use a formula to transfer data line by line. I will set the score sheet up I just all of hole 1 players to be printed on a card and so on with hole 2-18 also I need First and Last names on the cards and possibly score total by the name.
Every month I get sent sales figures of products, from now I have been typing in every sales figure which is long and boring. So I was wondering whether there is a way to make excel match the products on each workbook (mine and the one I get sent) seeing as there is a product number on both workbooks and then have excel automatically fill in the field I type manually which are quantity and value. So excel would see on the one workbook that product X has sold 10 units with 1000 value and then would go across to my workbook and fill that in the area I need on mine matching from the product code?
So I have a spreadsheet with a list of item codes and a subsequent excel sheet with corresponding item codes. I would like to match up the item codes and enter everything found between column E to L.
I need to alter this piece of code so that it extrapolates the data in the same row as the keyword and not from the column. The keyword i'm using is "N" and is found in the first column.
[Code] .....
I have also attached a copy of the workbook : 120514 KCS CLEANING MAY DATA CALLED.xlsx
I have a workbook, which is having many sheets but I am sending here two sheets only.
Please refer test file.
I want here a 'Add' button which transfer Column C,D,E,F's data to 'EOD' sheet's F,G,H & I. next time when I click 'Add' Button EOD'S f,g,h,I data should shift to j,k,l & m and calci sheet's c,d,e,f's data to eod's f,g,h,&I, and so on.
I have a spreadsheet that has 5 columns. The first row is the Header row, the second row is for the data input, here are the columns:
Column 1: Company Name, I have a drop down list associated with this column.
Column 2: Sales Person, I have a VLookup function to check for the company name in Column 1 and fill in with correct Sales Person.
Column 3: Total Sale, Manual input.
Column 4: Commission %, I have a VLookup function to check Company Name and fill in the correct Commission.
Column 5: Commission, Basic function of Total Sale * Commission %.
I have a macro created that when run it will take all the information from row 2 and move it to row 3 and at the same time inserting a blank row 2 with the functions for me to fill out new information.
The problem that I am having are 2 things:
1: When running the macro the functions will get applied to the newly inserted row but the drop down list for the Company column doesn't.
2: I would like to be able to start typing in the company name i.e "F" and have it jump to the F's in the drop down list.
I am trying to get a macro to update information from attached sheet GTS807 to the Stock sheet. The code in Module 26 works only for the first line and will be very long if I repeat it for the 20 lines of the input sheet! Basically we need to copy the new stock balance from GTS807 column AA13 to AA32 to the individual corresponding product name on the stock sheet (Column E)!
I am trying to figure out how to update cost from a manufacturers price sheet in excel. I have one worksheet that has the manufacturer's part number and my current cost. A=item number B= current cost. on the second sheet is the same setup but my price list has roughly 3500 rows and the vendor price sheet is 23000 rows. Is there a way to automate the cost replacement by comparing item number and if they are the same replacing cost on sheet one with cost on sheet two?
I wanted to : when i write data on one cell (sheet A), i want to see the same i wrote into another cell (sheet B), thts very simple, i know, but when its in blank (Sheet A), in sheet B appears a zero. I dont want to have zero, i want to remain blank as in sheet A.
i did in sheet B "='sheet A'!A117".
i am a noob in VBA, just beginning, and i am loving....
I have somewhat of a complex issue (at least i view it as one).
I have included a sample Excel Doc.
I want to be able to enter data into the spreadsheet titles LIST as i receive it. i receive invoices and checks and i keep a running tally. but i am trying to avoid re-entering the same data into the BREAKDOWN tab. but i need the data to go to the proper place in BREAKDOWN. The problem is that i have no idea how big the list will be and how many invoices i will be receiving. so i think new lines will needed to be inserted in BREAKDOWN when i add to the running total in LIST.
