Now what I want to be able to do is when I add a new row to worksheet 1, in this case row 3, then the references in worksheet 2 either to automatically (or manually do it in one simple go) increase by one and now refer to the new row. So the references now become =A3, =B3, =C3 etc.
I am running a research experiment in triplicate. Upon generation of data for all three, I create an averaged value of the three. I then take this value and do more calculations with it on a separate sheet. I wanted to know if there was a way in which I could quickly generate formulas based on a changing reference cell.
Example Data:
V1A 28 V2A 26 v3A 30 AvA 28
V1B 20 V2B 22 V3B 18 AvB 20
Vxy represents the value of y sample, x triplicate. V1A being sample A, first triplicate. AvX is the average of the three values.
I want to be able to generate a formula that references AvA which can be applied to the cell immediately below to reference AvB. A simple example would be to square the average.
I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.
I have been working on this for a couple of days and even tried EE, but to no success.
I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range
On Error GoTo mEnd Set rng = Sheets("Log").[F14:F10000] If Not Intersect(rng, Target) Is Nothing Then If Target = "" Then With Sheets("Log")
I am trying to do a simple copy and paste of number of cells A42:H76 to another sheet (in the same workbook) and have all the references stay when pasting to the new sheet. Instead when I paste it I get #Ref and the cell formulas no longer point to the right cell (they are all off by the same number because I did not paste the cells on the same row/column on the new sheet). Also, they don't refer to the older sheet where I want them to refer to.
I've created a user form to enter some values in a few adjacent cells. When the user clicks on a button, I want several other cells to be loaded with special formulas that refer to the new values. This happens on a row by row basis. First, a working formula in that cell which is AB5 is:
I want to copy =d8*k10 into several cells, but the references keep changing. I've tried several things that I've found on the internet, but nothing seems to work and the I can't seem to copy to a columnof cells.
I have about 85 files that I need to rename. These files contain data concerning delivery and quality data of each of our suppliers...1 file per supplier.
Current File Name: SupplierA 2012.xlsx New File Name: Supplier A 2013.xlsx
Is there a way to do mass rename the files..
and that leads to my second question...each of these files contain vlookups to 2012 data...which is contained in the a file with 2012 in the file name. I need to replace source data file (i.e. 2012 Index) with the new file containing the 2013 data (i.e 2013 Index). Is there a way to replace that source data across 85 or so files using some time of mass find and repalce? All the cell references remain the same
I have a large workbook with LOTS of large formulas. However they dont contain $ characters before the ranges as I haven't needed to manipulate them til now. Now I am changing the sheet and I cant seem to move or copy these cells without the ranges whithin the formulas changing! Is there a way to do this? If not is there a way in VBA that I can check through selected cellls and enter a $ before all the ranges?
I'm working on an excel report that handles reporting for a large number of people. Another person at work is doing the VBZ coding, and I'm doing the formulas that aggregates the data. which has led to an interesting problem whenever I use a vlookup. I copied the sheets with the formulas from the test document I had been working on to an updated version. And all the vlookups automatically adjusted themselves to reference the document they had been created in. I had also moved over the tabs they were supposed to reference, but they keep looking at the old document. I spent an hour removing the references last night, but I don't want to do it again when I get the final product.
How to get excel to copy a formula EXACTLY as written, and not att in the name of the original document?
It should say =VLOOKUP($A438,totals!$A$1:$AH$63,32,FALSE)
and instead it says =VLOOKUP($A438,'[reporting formulas.xlsx]totals'!$A$1:$AH$63,32,FALSE)
I have one sheet with all the formulas for the entire workbook and would like to copy and paste the formulas from Sheet 1 to Sheet 2...Sheet 1 to Sheet 3 etc. without changing the cell referening in the original formula. I am not too sure how .formula works.
Sub CopyAndPaste() ' To copy formulas from Summary sheet to their respective sheets
With Worksheets("Summary") .Range("R3").Copy Worksheets("2").Range("X3").Formula = Worksheets("Summary").Range("R3")
I have formulas in wksheet #1 going down 20 columns (they are all the same formulas). On wksheet #2, I need to understand how to switch all column references in only one column to another (from wksheet #1) to show different sums. Clear as mud? The way I want to do this is by having one cell on wksheet #2 where I change a number from 1 to 20. This in essence would be the column heading in wksheet #1.
Example: *There are more formulas but for the purpose here I'll use 3 for each Wksheet
In wksheet #1 I have C24 is =SUM(C14*C15,C16*C17,C18*C19,C20*C21,C22*C23)*12 C55 is =IF(B66=0,"",C7/B66) C56 is =IF(C11=0,"",C7/C11)
In wksheet #2 I have .......................................
The problem is that when the data on sheet 2 gets updated and more lines added, the computer changes my formula for the cell on sheet 1 as well. the new formula will change to
I'm working in a workbook with several sheets, the first two collums of each sheet are =to the first sheet. some times I need to insert a line, but when I did that, I need to type again all formulas or drag the previous ones, and format again the cells. is there another way to do this?
What I have is basically a form on the main sheet. Here other users can add and/or delete information in a form. I refer to the information in another sheet with formulas.
Now everything was great when using protections/ validations, to the where they fill in the information, but the problem occur when they are allowed to delete and remove rows.
This is something that I want to allow them, even though I do realise that this is something that many advice against it.
To begin with I found the post about changing relative references to absolute references (ConvertFormula). Still the formulas changes when rows are added or deleted and I would have to redo all formulas and run the macro again. I then found posts advicing on the use of the INDIRECT forumla. This formula was exactly what I needed as the formulas would not alter when rows were added/deleted. Now the problem is a combination of the use of INDIRECT and the the ConvertFormula. Now if I write a formula, for example:
=INDIRECT("'Sheet1'A1")
Then when I try to expand the formula over to the rest of the cells, then the references stay pointing at A1, when I want them to continue pointing at A2, A3, etc...
Now what I am looking for is some way to tell all my formulas, after expanding the original formula, that they should all have the INDIRECT function. If it was possible it would be great if you could implement it in the same way as with the absolute reference, for example:
For i = 1 To MyRange.Areas.Count MyRange.Areas(i) = Application.ConvertFormula(MyRange.Areas(i).Formula, xlA1, xlA1, xlIndirect) Next i
Now the above code sample does not work of course, as there should be xlAbsolute instead of xlIndirect, but I hope you understand my intention. Do anyone have the answer to my problem? Is there an easy way to implement the INDIRECT function into a big amount of formulas?
I'm working in Excel 2010. I'm trying to create a dynamic range, using the OFFSET function. I've got it working beautifully but as soon as I save it and close the Name Manager the range stops working. When I go back into Name Manager I find all the cell references have changed (from C1:C600 to C1048572:C595 - or some other strange range).
P.S. What it's doing isn't so important as when the cell references are right it works. But just for full info: It's an OFFSET function, starting at A1. It will look at a list of companies and use a Match function to find out how many rows to offset (based on where the first instance of a particular company. And it uses the COUNTIF to make the range the same number of rows as there are entries for that company.
I have a basic formula =C17+'Asset Depreciation 2008 Onwards'!C24, and I want to copy it down just using the drag function. Problem is that the second reference range of cells are in rows and hence when I copy it down it doesn’t automatically update the cell references because it want to update them by column number instead of row number. IE I want it to display =C17+'Asset Depreciation 2008 Onwards'! D24, instead of C25. Do you know if there is any way of telling Excel that I want it to increase the column number by 1 every time, instead of the row number for this part of the formula?
I have columns with sort filters, columns with tables, unformatted cells and frozen panes. When sorting one column how do I make all other columns sort simultaneously with it?
I am attaching a sample excel workbook for reference. I have a set of coded data in numbers in ColumnA, and want to replace them with actual names which are in Column G.
As explained before, I set up columns such that all template number codes are in Column F and names in Column G. The actual raw data in Column A.
However the macro is giving me wrong replacements i.e. if the coded number was 21, its replacement is name2name1 not name 21, I hope you understand what I am saying.
It will save me bucket loads of time as I have a multiple similar columns. I am a novice in macros so I am not sure how to find full numbers (21) and replace with the specific name.
Sub FindReplace() Dim Frange As Range Dim Fr As Range Set Frange = Range("F1", Range("F65536").End(xlUp)) For Each Fr In Frange Columns("A:A").Replace What:=Fr, Replacement:=Fr.Offset(0, 1), LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Next Fr
i have read through all threads about permutation, and also a few others. I if I were an Excel Pro I would probably have been able to come up with a solution to my problem based on all the suggestions made, but unfortunately it is (still) all a bit too complex for me.
The problem:
I have between 2 and 5 lists of words, each list in one column:
Column A
red green yellow black blue green pink
Column B
shirt jumper blouse coat t-shirt skirt
Column C
for women for men for girls extra large for boys one size fits all
I have folder with 427 workbooks in, named TAB001.... TAB427.
These workbooks contain listed tables out of a bespoke apllication. Each workbook only holds one worksheet (which is named TAB001, etc corrsespoonding to the workbook name).
Each worksheet holds in cell A1 the name of the table
I want to rename each workbook to whatever is in cell A1. SO TAB001.xls becomes, say, Property.xls, and so on for all 427 workbooks.
I envisage a new workbook holdinga macro to do this?
Sub ImportCSVFiles() Dim sh As Worksheet, sPath As String, sName As String Dim R As Range, fName As String Dim sh1 As Worksheet
But I've noticed that on import run, the macro mixing the ascending order of the files. An example: Inside my_folder I have 3 csv files: advertise.csv adwords.csv adwords keyword tool.csv
When the macro runs, it takes advertise.csv as the 1st. (Thats Ok) then adwords keyword tool.csv data before thaking adwords.csv
(I have over 100 files to import, so you can imagine the mess I'm facing..)
I have a sheet which is a basically my template, it has a bunch of formulas etc on it
I need to create about 200 copies of this exact sheet and its formatting etc, is there anyway you can bulk copy a worksheet or does this need to be done individually?
How do I copy 100 email addresses at the top of an email and post them in an Excell spreadsheet where each email address is in one cell i.e. 100 cells?
I am trying to compare deductions from two different pay periods. I have the Data from check 1 & check 2 on different worksheets, in no order, and would like the data to auto populate on a third worksheet into a side-by-side comparison of the data. The problem comes when there is a deduction missed or a new deduction that only shows up on one of the two checks. I am having to manually insert lines to make my side-by-side comparison match. Is there a macro or something that can pull the data from these two soures and display them in a side-by-side comparison even if there is no match?
