Variable Cell Reference Based On Minimum Difference
Feb 12, 2010
I have a monitoring system that records a data point with a date/time stamp several times a day at random intervals. For each reading I want to calculate the change compared to the first reading that was more than 24 hours ago, which could be anywhere from 1 to 20 rows above the current one. Hence with the timestamp in col A and the value in col B, the formula in col C, for example cell C20, needs to read something like =B20-Bxyz, where xyz is the row number of the first reading that is more than 24 hours, i.e the first row xyz where A20-Axyz >1.
View 4 Replies
ADVERTISEMENT
Jan 30, 2010
I have been given the following code and it works great. I now need to adapt it to the following scenario: In the attached sheet, the user has to select either, "Suburban" or "Squad" in row 5. If the user selects, "Suburban" I need this script to compare the values they enter in a given row to the value in column "B".
If the user selects, "Squad" I need this script to compare the values they enter in a given row to the value in column "C".
Look at row 48, for example. If the user enters, "Suburban" in cell D5 then the value they enter in cell D48 should equal "1". If it does not equal "1" then it should proceed with the adding of a comment. Conversely, if the user enters, "Squad" in cell D5 then the value the enter in cell D48 should equal "2". If it does not equal "2" then it should proceed with the adding of a comment.
View 5 Replies
View Related
Mar 23, 2009
I am trying choose the nearest date, from todays date out of a small array of numbers, and then also find the number of days between them. Additional to that, i would like it to ignore a date if that is in the past i.e. <now(). So I have a list of dates in column a, and i would like it to show me the number of days till the next closest 1, considering it hasnt passed already. I have tried many different ways and itteration of IF statement to solve this 1 but just cannot do it.
View 2 Replies
View Related
Jan 27, 2009
I need to find the minimum difference between any two elements in a row or a column. While it's easy to do for a 3-4 elements by doing subtractions for all elements in the array, doing it for more elements leads to a very long formula.
For example, I need to find the difference between any two elements between C5 and C9: ....
View 9 Replies
View Related
Jan 9, 2014
I am an architect and recently I was required to do some extensive calculation relating to water requirements and toilet requirements for a building that i was designing. i created a excel file, with a basic vlookup function to put certain values in the cells that are pre-defined for a particular building typology (mostly created by governmental bodies as guidelines). for example, when i choose a particular type in column E, column F and G fills up automatically. now this table also gives me occupant load based on what is the area i enter in column D.
The problem is that I want to automate the calculation of toilet requirements. Now, each typology will have a different formula to calculate WC/Urinal/washbasin.
1. there will be 17 different formula - one for each typology (as shown in sheet 5)
2. Column j will calculate what is the number of WC required based on 2 criteria - first id what is the typology and then looking what is the number of occupant.
The problem here is for example, if in E9, selected typology as "offices", the formula of offices typology will be used in J9 and occupant value from H9 will be used but if i have E18 also selected as offices typology, the same formula will use occupant value mentioned in H18 instead of using H9.
Hence, excel will first have to look what is the typology selected. based on that, it will load formula - out of that 17 different formula - specific to that typology and finally it will refer to corresponding row for occupant load and will use it in the formula to get the result.
View 3 Replies
View Related
Apr 11, 2007
I have counted the number of rows using i. Now I need to pass that variable to the ActiveCell referances shown.
Do While Not Selection. Offset(i, -6) = ""
ActiveCell.FormulaR1C1 = "= round(R[i]C[-8],2)&""""&round(R[i]C[-7],2)"
ActiveCell.Offset(0, 1) = "=round(R[i]C[-7],2)&""""&round(R[i]C[-6],2)"
ActiveCell.Offset(1, 0).Select
Loop
End Sub
View 9 Replies
View Related
Jan 10, 2010
I have a formula that pulls data from another sheet using standard reference of sheetname! I want to build this forumla to use several sheets, and would like to pull the sheet names from other cells.
For example... instead of LEMONS!a1 and LIMES!B2
I would like to use REF1!a1 and REF2!B2
where REF1 and REF2 are pulled from cell Z1 which contains test 'Lemons' and cell Z2 which contains text 'Limes'
How can I do this?
View 9 Replies
View Related
Feb 22, 2012
I have a spread sheet that lists a bunch of dates and expected volume that will be associated with the date. I need to build a macro that will look at the first date expand it out for several months then look at the volume and break that out over a week's time.
That part is simple enough but I need to turn this into a looping statement and I cannot figure out a way to get my macro to always come back to the next date in my list.
Ideally I would like something that is a variable that references the cell I want so I can do something like this.
Code:
MYRANGE = range("B2")
Do
range(MYRANGE).select
[Code]....
This should work for me perfectly if I can just figure out how to make the MYDATE varaible reference a sepecific cell rather than a value or text in the cell.
View 2 Replies
View Related
Jul 15, 2014
I'm trying to use a variable as a cell reference in a formula using '&', but can't seem to get it to work. I've looked through the forum, which have examples of how to do this using a range, but not just as a single cell reference. This puts the formula '=VLOOKUP("A & lastrow &"" , Vlookupinput, 2,0)", which obviously gives me an error.
My code is:
Code:
Dim lastrow As String
lastrow = Range("A" & Rows.Count).End(xlUp).Row + 1
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 1).Select
ActiveCell.Formula = "=VLOOKUP("A & lastrow &"" , Vlookupinput, 2,0)"
View 5 Replies
View Related
Jul 9, 2008
i'm trying to use the sum across sheet function - i.e. - =SUM(Start:End!B1)
Whereby "Start" and "End" are the two relevant sheet names and "B1" is the cell I want added up.
However, can I make B1 variable, whereby there is a cell, say in A1, that contains the string "B1", so that I can make it sum C1 or B20 or anything on the other sheets?
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
Jan 13, 2009
Not been around for some time now but have to do my annual update for this workbook and implement some new changes.
In the attached example workbook, I have created some formulas which reference a particular cell for each client e.g.
View 2 Replies
View Related
May 29, 2012
I am building an Excel sheet for my company which keeps track of who has done what with a new client in a step by step process. I have it set up so that once a department clicks complete in a box it automatically emails (VIA a macro) the next person who needs to do the next task. Some tasks are done by the same person no matter what, other tasks are done by a project leader which is different depending on the client.
The problem I'm having is that, within the macro, I need to reference an email address in a cell X cells to the left in the ".TO" line.
So, in Column A I have the CLIENT, Column B The PROJECT LEADER, and Column C The PROJECT LEADERS EMAIL. Then the next 22 Columns are labeled steps with drop down boxes, once the have selected "COMPLETED" it triggers the macro and send the email, just not on the ones when it needs to email the PROJECT LEADER, since it's a variable. Below is the macro, how I can have the ".TO" line in the email reference the email address in Column C to the left of it?
Sub Mail_Workbook_1()
Dim OutApp As Object
Dim OutMail As Object
[Code]....
View 2 Replies
View Related
Jun 29, 2006
I'd like to assign a value located in a cell in a workbook to a variable in a VBA program in another workbook. I do NOT want to open the workbook that contains the cell with the value. I can't get the hang of referencing an external cell reference. Let's say there's a value in a cell named "nbr" in a workbook named "tst.xls" located in a path "c: est" that I want to assign to a variable "tstnbr" in a VBA program, what is the best way to code that?
View 8 Replies
View Related
Sep 28, 2006
I want to create a formula of the form: =INDIRECT(ADDRESS(4,2,2,TRUE,($A9) and insert this in a cell BUT the $A9 reference needs to reference the row of the cell where the formula is being inserted which will vary. ie. A30, A31, etc. How do I do this?
View 2 Replies
View Related
May 17, 2008
I will show with an example of what I would like to do. I italized and underscored the part of the formula.
---A----B
1 24 formulaB
formulaB = "= CELL("contents",DifferentSheet!G(A1))"
I want B1 to contain the value from the cell G24 in sheet "DifferentSheet". I want to use the value in A1 to determine what row on "DifferentSheet" I should use.
View 2 Replies
View Related
Jun 18, 2014
I have read meanwhile dozends of articles and comments about absolute and relative cell references.
But I found nowhere an example on how to make a the column part of a cell reference variable and the row absolute.
Is
K$10
a valid expression?
In opposite to $K10 is must work this way
View 3 Replies
View Related
Mar 16, 2009
Excel 2003 > Attached is a small model of what I am trying to accomplish. Cells B2 and B4 contain the same formula a formula that calls a simple function. The function has a variable passed to it and the value of that variable changes depending on the location of the cell. Now, see the function in Module1 it is called CellCalc. If the variable passed = Jim then value = 3. If the variable passed = Jack then value = 8. That is straightforward.
Note also that when Sheet1 is activated, I calculate the cells from left to right and top to bottom that is important. Here is the challenge. If B2 > 1 then I want to add B2 to B4 and set B2 to 1. I can set B4 properly but I cannot reset B2. You can see my 2 attempts that are commented out. Is there some way of accomplishing this or am I simply stuck in a circular reference?
The alternative to this is to write a function that operates externally on these cells. That will work for sure but then the values of the calculations will overwrite the functions in those cells, thereby taking away the dynamic nature of this application.
View 4 Replies
View Related
Mar 25, 2014
I have been trying to determine the maximum/minimum value and additional count from a variable range which then I can use to subtract the first data of the range; I have been able to do that for a fixed range but not a variable one.
I have Column A with random positive numbers.
I have Column B with random negative numbers.
I have Column C with random numbers.
I have Column D with random numbers.
I have Column E with random 0’s and 1’s.
I have a set of 1600 cells of numerical data on each of the columns and there will be times when the random 0’s and 1’s from Column D will have appear repeatedly before changing to the opposite number, fx, I will get 7 nr. 1s before I get a 0 (zero).
I have been able to obtain the values in 2 cells within a fixed range:
If the last 8 cells in Column D have been “1” then I do:
F1=MAX(A1:A8)
G1=INDEX(C1:C8;MATCH(9.99999999999999E+307;C1:C8)) ---(Which provides the first number of the range in Column C after Column D has changed from 0 in D9 to 1 in D8)
H1=(F1-G1)
If the next 3 cells in Column D have been “0” then I do:
F9=MIN(B9:B11)
G9=INDEX(C9:C11;MATCH(9.99999999999999E+307;C9:C11)) ---(Which provides the first number of this range after Column D has changed from 1 in D12 to 0 in D11)
H9=(G1-F1)
Additional to this, the COUNT has also been challenging since I want to obtain in Column I, the COUNT of repeated 1’s from each range of 1’s in Column D; and also in Column J the same but for 0’s.
Fx:I18 (size of the range of 1’s originated from D1:D8)
J93 (size of the range of 0’s which consist to be the next range before 1’s were originated)
I12X (size of the range of 1’s which consist to be the next range before changed to 0’s)
However, that only works for a fixed range and continuous updates are made.
Book1.xlsx
View 2 Replies
View Related
Jul 11, 2006
I am creating a problem report spreadsheet for work. In col C I have a date/time that the problem started, eg, 23-Jun-2006 15:20 and in col D I have a date/time for when the problem was resolved, eg, 26-Jun-2006 15:40. I need to calculate the amount of hours that we were not available for use (due to the problem). This is based on a list of available hours. Eg, on Mondays we are available 19.5 hrs, T-F 22.5 hrs, Sa 14.5 hrs, and Sun 0 hrs.
If I do a straight subtraction, it uses 24 hrs for each day - not taking into account our available hrs. If I do NETWORKDAYS it automatically doesn't consider Sat or Sun - and i need it to - and it does have a DayEnd and DayStart but it assumes they are constants. I found the day corresponding to the date and thought I could determine the series of days between it, e.g., if the start day was Friday & the end day Monday, the series in between is Sat & Sun. Then I could do a lookup on all 4 days in a table & add them all together. But I can't figure out how to do this!
View 4 Replies
View Related
Jul 27, 2009
how to achieve what I'll call "parameter driven" code where the "parameters" are variable names.
For example, in the following code... I'm looking for an answer to the question posed...
Sub Test_Sub()
Dim vX, vY, z
vX = 10
vY = "vX"
For z = 1 To 3
'This next line generates a Run-time error... But...
'=====================================
MsgBox z * vY '
View 9 Replies
View Related
Jan 4, 2010
Attached is a print screen. I'm struggling with using the min function in vba. I want it to find the minimum cumulative cost in week 0 out of the first three, and the copy the permutations of it (1,0 or 1, 1 , e.t.c.) to Week one column C & D of the model.
View 3 Replies
View Related
Mar 11, 2014
I would like to find min value in column B for 1 order number (they are sorted Asc). Values must be shown in column C (currently are in column C expected values).
Example table:
No.
A
B
C
1
Orders
Values
[Code]....
View 2 Replies
View Related
Oct 15, 2008
I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.
Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.
View 5 Replies
View Related
May 12, 2014
I have several worksheets and I want to summarize all of their data on the main page. The second worksheet is called Grady, the third worksheet is called Stella and the forth worksheet is called Westminster.
On the Main Page cell B1 is, "Grady", C1 is, "Stella" and D1 is, "Westminster". On B5 I need the value of cell C5 on Grady but I don't want to have to type it in as this formula will have to be carried over for several columns.
Currently I have:
Code:
=Grady!C5
In cell B5 on the main page but I want:
Code:
=B1!C5
But this only gives me #REF!
Can this be done without using VBA?
View 5 Replies
View Related
Apr 4, 2014
I require calculating difference between two columns. (Actual and Estimated).
Difference between these two numbers are calculated in third column, but it is reset every time actual number is non-zero.
Scenario:-
-Estimated Numbers are always followed by an actual number.
-After actual number is obtained, estimated numbers till that date will be added and deducted from actual number.
-After this, again estimated number will be added till next actual number is obtained for deduction.
If estimated number is not followed by actual number, then no need for difference calculation.
View 3 Replies
View Related
May 2, 2012
I am creating a macro to automate data analysis for work, but I've become stuck.
I need to find the coordinate of the first cell in a column with word "reserved" or "extended" contained in the cell, and then assign the row number of that coordinate as a variable to use for moving data around. Basically, I want something like VLOOKUP, but instead of returning a value, I want it to return the coordinate.
View 1 Replies
View Related
Jul 7, 2009
I'm looking to use MIN to look at a range of data in L:L based on matching certain criteria in A:A. In the attached spreadsheet in Totals!C1 I would like the MIN value from List!L:L for user "CHRISTOPHERJ" in List!A:A
View 3 Replies
View Related
Jan 1, 2008
Trying to create a Math formula that allows for Taking advantage of sale pricing.
In Excel the following:
Qty to Order (G3), Std Package(I3), Price per Unit(J3), Sale Price(K3), =
Extended Price(L3)
Example
2case X 40peices per case,1.00Reg Price,.90Sale Price = 72.00
Not quite sure how to handle the Sale price taking over regular pricing
View 9 Replies
View Related
Jun 23, 2009
HTML Imagine the following table (Ax, etc is the cell position):
A1=John C1=Name D1=Date E1=Version F1=IP
A2=10/2/2008 C2=A1 D2=A2 .... ....
A3=Client version 7.01 C3=A1+5pos D3=A2+5pos
A4=172.24.3.5 C4=A1+10pos D4=A2+10pos
A6=Michael
A7=10/2/2009
A8=Client version 7.03
A9=172.24.3.80
A11=Susan
A12=2/2/2007
A13=Client version 7.02
A14=172.24.3.200
I have a table of data on the left side and I want to re-organize just like the right side. Let's take C2 as an example, the value for that cell = A1;
Now, C3 in the other hand, the value for that cell = A1 + 5 positions down;
C4 = A1 + 10 positions down;. The same type of formula applies for the remaining columns on the right. This seems like an easy solution, but it's not easy when you don't know it... :
View 4 Replies
View Related
