Create Formula In A Column That Increments Cell Reference From Data In Row
Sep 8, 2012
I'm new to Excel (4th day in class) and I can't find the answer to what I'm looking for on the net. Part of the problem is that I don't know what to call it, so I have a hard time looking it up.
Very simply put, let's say there are examples numbers 1,2,3,4 put in cells A1 to A4 (vertical) and more example number 50,60,70,80 are put in cells A6 to D6 (horizontal)
What one formula can be written in B1 that I can copy down to B4 that will make B1=A1+A6, and B2=A2+B6, and B3=A3+C6, and B4=A4+D6
View 1 Replies
ADVERTISEMENT
May 9, 2012
i'm looking for a loop macro to perform the following task:
Sheet 2 is my look up table, and sheet 1 as a number of references such as:
='Sheet 2'!B1
='Sheet 2'!E1
='Sheet 2'!G1 etc
I need a macro that increases the row references "1" to "2", saves the sheet then increases "2" to "3" and so on until the bottom of the reference table is reached
View 1 Replies
View Related
Mar 10, 2013
I have a list of headings and items and I have a set of formulas that work out depending on the heading what items are listed.
Say theres 10 items and the heading starts at C4 and that heading has 10 items, so it puts "C5" as text in G1 and "C15" as text in G2 so i now know my cell range of items
How can i use the text in those cells to put in a formula to call that as a range?
If I use the indirect formula it shows me the value of the cell, but im after using it to reference the cell
View 4 Replies
View Related
Sep 6, 2007
I am looking for a formula that I can drag down a column that is pulling data from left to right at the top of my spreadsheet. See the attachment.
View 5 Replies
View Related
Jan 16, 2013
I have a spreadsheet with a column showing dates (dd/mm/yyyy). I need the column next to it to display the month only (Jan,Feb etc).
How do I create a column to show the month only using the date column as a reference.
View 5 Replies
View Related
Mar 6, 2008
A 'Days Attended' cell (N8) and a 'Days Absent' cell (O8). N8 needs to count the number of "Present" values there are on another worksheet. The other worksheet has dates across the top and names down the side.
When i use
=COUNTIF("Attendance!C9:Z9", "Present"),
and the next date comes along the formula changes to
=COUNTIF("Attendance!D9:AA9", "Present")
ie. the reference moves a column across - the new date's absent or present is not counted. Using =COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present"). is no good because when i add a new name i need the row reference to move down as a row is inserted. ie. both person's formulas count the same row. So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system.
View 2 Replies
View Related
May 26, 2009
I'm working on setting up a chart. The formula I'm using for cell B21 is this: =ROUND(F3,0) & " " & IF(E3<F3 ...
View 2 Replies
View Related
Sep 7, 2012
Very simply put, let's say there are examples numbers 1,2,3,4 put in cells A1 to A4 (vertical) and more example numbers 50,60,70,80 are put in cells A6 to D6 (horizontal)
What one formula can be written in B1 that I can copy down to B4 that will make B1=A1+A6, and B2=A2+B6, and B3=A3+C6, and B4=A4+D6
View 1 Replies
View Related
Oct 20, 2008
I have created a table which is an index of all the worksheets. Now i would like to create a formula using the index reference. I get the right result if i concatenate but the text doesn't turn in to an excel formula.
=CONCATENATE("=";A1;"!";"D4") A1 contains the worksheet name, if the worsheetname is "A" then i would like it to return a formula that is =Sheet1!D4
View 3 Replies
View Related
Sep 12, 2008
I have a column of over 500 replies to a survey. I want to create a pie chart showing the average ages of the respondee to that that question, broken down into increments of 5 years (this was the question -- how old are you) and reported as a percentage.
View 9 Replies
View Related
Aug 14, 2006
I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.
Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following
View 3 Replies
View Related
Mar 19, 2014
I attached document, in that document I made formula in Pressure cell which is INDEX MATCH MATCH to get the result.
But what I wanted is, when I type 10.4 in Temperature cell, the Pressure cell will show the result which is 1261.24 directly without I have to fill in two temperature cell to make it 10.4.
View 11 Replies
View Related
Aug 15, 2008
I have a workbook containing a lot of worksheets, each sheet contains approx 60,000 rows of data (each sheet varies in the number of rows slightly but the data is always contained in column A). There are only 4 unique values and my task is to filter out the 4 seperate values and copy & append them to a master list in 4 columns side-by-side i.e. valueA in A1, valueB in B2 etc.
The problem I have run into is that when I have filtered out one of the values and try to copy it I get an error message - "Excel cannot create or use the data range reference because it is too complex".
This has left me thinking that the only way to process the data is to manually copy approx 20,000 rows at a time (I'm not sure what the maximum amount of filtered data Excel can handle is but this it can definitely handle) and paste to the master list.
View 9 Replies
View Related
Mar 20, 2007
I'm making a user form in excel/vbe, that will enter data into a worksheet behind the form. I would like to make a unique reference number that is automatically generated by the form as the next in the series. It can be as simple a number as 00001 so nothing too complicated I hope.
When the user opens the user-form i would like the reference to appear on the form so that they know what it is. Then when they enter the data I want the number to go in the database with all the rest of the info they have entered and also for a message box to come up reminding them to rememeber their number.
View 9 Replies
View Related
Jan 8, 2012
How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.
________A________________B_____________C_________D
1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)
If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?
View 4 Replies
View Related
Feb 15, 2010
I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.
EXAMPLE:
12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.
View 14 Replies
View Related
Dec 18, 2013
I am trying to create a budgeting spreadsheet that contains 26 worksheets that represent 2 week expense cycles. The worksheet will tally all the expenses in its 2 week cycle, compare them to a target budget, and calculate the remaining budget. I would like to roll the remaining budget over to the next 2 week budget cycle by having a cell refer to the remaining budget in the previous worksheet.
I am trying to accomplish this using the following VBA script. I get an error (Run-time error 1004: Application-defined or object-defined error) on the 3rd to last line of the code. I think the issue is with how I am constructing the wsReference variable. I have seen other codes do something similar (VBA - How to refer to a different worksheet cell) but I am not having any luck.
Sub Budget_Rollover()
Dim i As Long
' Loop through all the worksheets, starting with the second sheet
For i = 2 To Worksheets.Count
Dim wsReference As String
Dim wsName As String
' Define the name of the previous expense cycle worksheet
[code]......
View 2 Replies
View Related
Apr 25, 2014
Let's say I've ended up with the number 8 in Cell D4 for example. Is there a formula that can return the letter "G" (The 8th Column) so I can use it in future cell references ? If so, let's say we store that in Cell B5. How do I now refer to a cell in a chosen Row of that same Column by reference to Cell B5 ? For example if I want to refer to Cell G33 can you refer to this Cell in some form like Cell(Contents of Cell B5;33) ??? Don't want to use R1C1 type references if possible.
View 9 Replies
View Related
Apr 3, 2014
I have a form in which users will manually enter a date in Column A. I would like to create a formula in Column B which will add 4 days to the date based upon Column A. However, the 4 days should only be added to a select set of dates which I would like to specify. If the date is not found in this select set of dates, then the result in Column B should be the same date as Column A.
View 3 Replies
View Related
Aug 14, 2009
I'm trying to revise a salary payments workbook that isn't set up so well and I'm struggling with finding a good way to enter payments from an old sheet onto a new sheet. The values (for example) I want to appear on the new sheet are in range C2:AQ2 on the old sheet. Instead of going left-to-right, I want them to appear from top-to-bottom. The first formula I wrote was just a simple:
View 5 Replies
View Related
Jan 18, 2008
I'm trying to write a formula that will keep the Column ID static while allowing the Row ID to be reference. Each week my worksheet adds one line and the formula in question is moved one cell to the right and thus the formula cell makes the same move (1, -1). I need the formula to keep the column the same from week to week, while allowing the row to shift downward with the formula. I've attached a sample spreadsheet with the formula I derived. Check out the highlighted formula to familiarize yourself before running.
View 2 Replies
View Related
Oct 1, 2011
Version: Excel 2007 WinXP
I'm basically looking for something almost like an inverse function to INDIRECT. This function would first look at a cell's formula as a text string, parse out the first valid cell reference in A1 format, and return that cell as a text string.
Detail: I have a spreadsheet with cells that point to other values. I would like to get only the row number from the first cell reference in the formula residing in a given cell. For example:
Suppose A1 has the formula =AL267. and A2 has the formula =SUM(AL94:AL235)
I would like a formula in B1 that returns the text string, "AL267" so that I would know this is the first reference.
Ideally it could be dragged down to B2 such that it returns the text string "AL94" (and not "AL235") because AL94 is the first cell reference in A2's
Currently I am copying the formulas after hitting ctl+` and pasting that text into a text editor, followed by text operations to manipulate the results into the desired values. Any solution that didn't involve going out to notepad.
View 2 Replies
View Related
Jan 5, 2009
Easy scroll to down ways, should I copy rows to a column? See the attachment for a more clearance.
View 4 Replies
View Related
Aug 7, 2013
I have a array as
01-12-2008 pump-1 25
01-12-2008 pump-2 28
02-12-2008 pump-1 24
03-12-2008 pump-1 26
03-12-2008 pump-2 38
03-12-2008 pump-3 35
I need sum value as
03-12-2008 pump-1 75(=26+24+25)
02-12-2008 pump-2 if no answer blank
03-12-2008 pump-2 66(=38+28)
View 9 Replies
View Related
Aug 22, 2008
I need to copy a range of values from sheet 3 to sheet1. When I recorded macro, I got the below code. But, instead of RC in the 4th line <ActiveCell.FormulaR1C1 = "=Sheet2!RC"> I need to pass values like A1,A20 etc.
Since the values range & column to be copied would be varying dynamically, (say for first iteration it would be A1: A20 & for second iteration, I need to copy C1: C20.) how to pass these variables to the macro and use it instead of the static "RC". Whatever column I specify, it should copy from that <column>StartRow to that <column> EndRow.
Sub Updtval(StartRow As Integer, EndRow As Integer, f As Integer)
Sheets("Sheet1").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "=Sheet2!RC"
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A6"), Type:=xlFillDefault
End Sub
View 3 Replies
View Related
Dec 3, 2007
COPYING indirect formula. When I copy, the lookup reference does not change. My formula is =INDIRECT("'"&$A247&"'!j106"). When I copy horizontally across cells, I want J106 to increase, ie j107, j108 etc. At the moment it remains at J106 and i have to manually increase the numbers by 1.
How do I change my formula so that the numbers increase automatically?
View 6 Replies
View Related
Feb 7, 2008
In Sheet1,
C14 = Sheet2!D2
C15 = Sheet2!D3
C16 = Sheet2!D5
What i want to do is to change "D" to "E". Next time i run it, it will change from "E" to "G".
I want a macro to do it, so that I don't have to change it manually one by one. For some reason, when i try to use .formulaR1C1, the reference change, and I couldn't get it to work.
View 5 Replies
View Related
May 22, 2014
I'm dealing with forecasting invoices. The main column 'BALANCE' has a formula referencing the previous cell and then adding or subtracting invoices (ENTRY). The problem I have is that I'll put values in ENTRY that I expect to come in at some point but as they arrive I need to change the order of the cells. So I'm constantly changing the order and then it changes the formula in BALANCE. I tried absolute cells (and even found a work around way to flow absolute cells by flowing then viewing formulas then using the replace function but that didn't work.
So my formula in BALANCE is:
=$I$173+$E$174-$G$174
but if I move G174 to another cell I'd like to keep the BALANCE formula to stay G174 instead of following the move.
View 2 Replies
View Related
Jun 17, 2009
i have the table of holiday days with 7 columns(7 countries).Each column consists of 10(or more rows (holiday days).
I have a formula for calculating start day for a deposit which has to be renewed.
formula is =workdays(end date of deposit,-2,A1:A10).
-2 in formula is because all the trades in the market are traded Today+2days.
A1:A10 is column of holiday days.
This formula calculates the start of deposit excluding weekends,holiday days.
BUT I have more then 7 countries and I would like to have a macro so when in cell F10 I type SWEDEN,my formula automatically use the column with holiday days B1:B10 for sweden.
When I type Finland, the formula uses column C1:C10 with holiday days from Finland respectively.
View 6 Replies
View Related
Jul 21, 2013
I have a range that I named. Let's say that range is called "RangeX". Let's say RangeX is defined by B2:E10
I have a series of formulas that give me the row and column numbers of the starting and ending cell of a subset range I want to create within RangeX. Let's say the starting cell is defined by (1,1) and the ending cell is defined by (3,4) within RangeX. Translated, the starting cell would be B2 and the ending cell would be E4.
How do I create a subset range using the starting cell (1,1) and (3,4) in VBA? I would like to use the row/column reference as RangeX itself could move around.
View 1 Replies
View Related
