Contents of B2 are "=A2"
Contents of B3 are "=A23"
I want to select B2 and B3 and drag the formula to continue
Desired results:
Contents of B4 are "=A44"
Contents of B5 are "=A65", but when I drag the fill handle...
Actual results:
Contents of B4 are "=A4"
Contents of B5 are "=A25"
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 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 .......................................
It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.
I have tried to do it with structured references and with cell references I get a column of zeros!
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 cell which references another cell (i.e. =A2). When I insert a cell above A2 the new formula becomes =A3. How do I stop it from changing?
I want to insert as many cells as I want above A2 and the cell reference will always be =A2. I played around with $'s but to no avail.
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.
View 2 Replies View RelatedSay I have this data in worksheet
A
B
C
D
E
14/1/2012
1
2
3
4
15/1/2012
5
6
7
8
And this in worksheet 2
=A2
=B2
=C2
=D2
=E2
=A2
=C2
=D2
=E2
=B2
=A2
=D2
=E2
=B2
=C2
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 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.
View 3 Replies View RelatedI 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?
View 2 Replies View RelatedI'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")
End With
End Sub
On my excell I have a lot of data on sheet 2 that i automatically pull form a database.
I have this fomula in a cell on sheet 1.
= SUMPRODUCT(--( 'Sheet2'!P5:P50000=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))
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
=SUMPRODUCT(--( 'Sheet2'!P5:P50126=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))
when there is 126 lines of new data addes in on Sheet2. Id only 50 lines were added in on sheet 2 then the Formula would change to
=SUMPRODUCT(--( 'Sheet2'!P5:P50050=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))
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?
View 4 Replies View RelatedWhat 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.
Here is my broken function:
=OFFSET('Map Point'!A1048572,MATCH("Company Name",'Sheet Name'!C1048572:C595)-COUNTIF('Sheet Name'!C1048572:C595,"Company Name"),0,COUNTIF('Sheet Name'!C1048572:C595,"Company Name"))
How can i copy references from a row, down a column. Like I want to reference A1:A10, but I want to reference them down A2:11.....I would like to just the references down.
View 1 Replies View RelatedI have a spreadsheet with 2000 rows, and I need to add one column with the IF function that looks at the cell in the column to the left for each row, all the way down. How do I do that without typing in the specific different cell references for each row in my IF column?
View 4 Replies View RelatedI'm using a macro to update individual formulas in a sheet that contains data from external documents. (i.e. ='C:September[Book2.xls]Sheet1'!$AZ$1 + 'C:September[Book3.xls]Sheet1'!$BA$1 ) Every quarter I have to change the column references in this formula (i.e. $AZ$ will become $BD$ and $BA$ will become $BE$ and so on). Also I have to change the September reference to December etc (but I've mastered this already). My current code works well, but requires manual updating:
Dim ColumnReference1
ColumnReference1 = "BD"
Dim ColumnReference2
ColumnReference2 = "BE"
Worksheets("Sheet1").Range("H48").Formula = "='C:September[Book2.xls]Sheet1'!$" & ColumnReference1 & "$1 + 'C:September[Book3.xls]Sheet1'!$" & ColumnReference2 & "$1"
My problem with this method is that I need to automate the updating of ColumnReference1 = "BD" to ColumnReference1 = "BH" to ColumnReference1 = "BL" and so on and so forth. It's handy that the reference shifts across 5 columns, which remains constant. Ideally, I'd like to tell the macro to look at "AZ" then add 5 columns.
I need a script that will look for all the formulas in a sheet and increase them by a digit.
Lets say one that particular formula is =(A1-A2)/A2*100
I need the macro to change this to =(B1-B2)/B2*100
I have already programmed in a Find/Replace command, but realised that this would only work one time only. I need generic code that will bump them up each time rather than having to hard code the Find/Replace command for every single cell in the book.
I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.
All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.
=COUNTIF(E14:Z14,"*Y*")
=COUNTIF(E11:Z11,"*DI*")
I would like to have the following fill down a column. =B5 =B7 =B9 etc. Is there a way to do this without doing it manually?
View 3 Replies View RelatedI'm working on a workbook where I'm linking data from a bunch of spreadsheets to a summary page. I have the first column (A) set up. I dragged the data across several more columns. Now I need to change reference to the tab in each column. Is there a quick way to do this without copying and pasting the tab name into each row (about 30 rows)?
(Each column represents a tab, the rows are the same type of data in every tab.)
I am using the dsum formula to sum some values...the formula in B2 is:
=DSUM(BaseSistemasFebrero,"vlfinf",OFFSET('Planes Entidades'!B$1,0,0,COUNTA('Planes Entidades'!B$1:B$49),1))
The Planes Entidades sheet the data is layed out like this: ....
I have a pivot table which draws data automatically from a database
What I would like it for the customer field of the pivot table to only equal the customers which are present in another worksheet (Column A:A)
get a cell to return a value depending on whether or not a cloumn is populated
for example. If i Have 3 columns EUR, GBP and CHF and there can (99% of the time) only be one entry in any of the 3 on any given row. How then can i get a 4th column to return the value EUR, GBP or CHF on the same row as an entry. I have a relatively complicate nested IF fromula but i think this is slowing down the worksheet as there are 500 or so rows containing it. Ive attached an example segment
I have been given a spreadsheet with standard configuration with column headers etc, but when you scroll down the sheet the actual column headers change from the normal A-Z display to be the column headers that have been entered into the cells.
I know this can be achieved similarly by setting Freeze Pane but how this other option has been switched on. I have attached the spreadsheet (which has totally fictitious content).
I want to merge two tables with keeping the data in the two tables in two worksheets, so my idea is to create a drop list in one of the two worksheets and depending on what the user will chose the datas will appear. for informations, the datas are all Vlookup in the same workzone but for each table the lookup will be done in differents columns, so how to make the column for the vlookup changement and depend on what I choose??
clarifications: it's like the choice on the droplist will be a link cell to the column of the research.
I've got a task list in column A and a priority list in column B. When I finish one of the tasks, I put "Done" in column B next to the task. I'm looking for the best way to reprioritize the rest of the column (i.e., when I finish the priority 1 task, priority 2 becomes Priority 1, priority 3 becomes priority 2, etc.). The column will obviously have numbers and the word "Done" in cells, so there are two different data types. I'm thinking the best way would be with VBA, but there may be an easier way with a helper column with formulas.
View 4 Replies View Related