Formula Relative To Sheet Housing It
Oct 28, 2006
I want to set up a name which is a relative cell refence an is independant of the sheet. Something like this ITEMCOST =A1
So I would use this in each sheet (Sheet1, Sheet2, Sheet3 ...)
So it should always reference A1 on whatever sheet the forrumal is placed e.g =0.175*ITEMCOST should work on A1 of each sheet. However, when I try this the name always references a specific worksheet Sheet1. I also want the name to cope with the case where I insert a new row so that the new value is ITEMCOST =A2
View 3 Replies
ADVERTISEMENT
Mar 2, 2008
I have been looking around have this much code from this site, modified. What I am trying to do is calculate numbers by a position with cells D through K having numeric values. I have 'hardcoded' the cells (D2, E2, etc in the code below), but in reality I only want the current row (so if the formula is on the 2nd row, I want D2, if it is on the 3rd row, I want D3).
My problem is obviously the formula isn't working because I am not correctly tying back to the spreadsheet (Positioncalc.xls). When I put the formula in the spreadsheet it works, but in my script I get 0 everytime.
My Script:
Function Position(rCell As Range, Optional RightPosition As Boolean)
Dim vResult
Select Case rCell.Text
Case "QB"
vResult = (2*D2) + (2*E2) + (2*F2) + (4*G2) + (2*H2) + (1*I2) + (4*J2) + (3*K2)
Case Else
vResult = "Invalid Position"
End Select
If RightPosition = True Then
Position = vResult
Else
Position = "Position not valid"
End If
End Function
So, when I put =Position(A2,True) I expect to see the formula results of those cells calculated based on the position (QB, HB, etc with their unique formulas).
My next challenge after this is to highlight certain cells based on the Position. So if A2 = QB, I want cell D2 boldface and Red, etc. I have seen some scripts on colors and such here, so I might be able to figure it out.
View 9 Replies
View Related
Feb 24, 2009
Is there a way to write formulas so the variables are relative to a sheet?
I have a workbook that collects monthly electrical meter readings and calculates billing for each tenant. A new sheet is used for each month and the formulas need to refer to the previous month’s sheet for the prior meter reading.
For example cell C36 in the sheet Feb09 might contain: =Jan09!D36 Where Jan09 is the sheet name from the prior month. When I copy sheet Feb09 to make sheet Mar09 I’d like the formula in C36 to be =Feb09!D36.
View 9 Replies
View Related
Aug 21, 2008
From a formula, how can I reference other sheets in my workbook via a relative reference as opposed to needing to know the sheet name? I know I can write a macro, but looking for a non-VBA solution.
View 9 Replies
View Related
Jun 20, 2006
I'm having some trouble coding a macro that copies data from one sheet in a workbook to the next sheet in the workbook, and the problem seems to stem from my lack of knowledge about how VBA refers to sheets. What I'd like to do is copy data from a range of cells in the next-to-the-last sheet in a workbook to the same range in the last sheet in the workbook.
Each day, I add a blank sheet to the end of all the sheets in my workbook. I insert some data into it, then go to the previous sheet, copy some data, return to today's sheet (the one I just added) and paste the data. I can record a macro that will do this perfectly, but it only works for the two sheets in which I record the macro. I need a way to change the sheet references so it always copies from the next-to-last sheet to the last sheet.
View 3 Replies
View Related
Nov 27, 2006
I am dealing with a workbook that will hold an undetermined amount of worksheets. Each of these worksheets will have have a series of numbers that are summed to a total within one cell. Lets say cell A10. For every spreadsheet, no matter what, this number will be stored in cell A10.
My first sheet will then be a master listing of all the other sheets in the workbook. In column A I will have the names of each sheet and in column B I want excel to list the value of A10 for the corresponding sheet listed in column A
So normally it would look like this on the master sheet
A | B
Sheet 2 | =Sheet2!A10
Sheet 3 | =Sheet3!A10
Sheet 4 | =Sheet4!A10
Where the formula would give me the value of A10 in the respective sheet.
What I would like to do is, within column B, I want to replace the sheet reference with the cell that contains the name of the sheet. So it would then be a formula similar to the below setup(Although this does not work because I have tried it)
A | B
Sheet 2 | =A1!A10
Sheet 3 | =A2!A10
Sheet 4 | =A3!A10
The hope is that excel would substitute in Sheet 2 for A1 and there by give me the sheet reference that I need. This way I can continuosly add sheets to the workbook and as long as I have the correct Sheet names in column A, all I need to do is drag the formula down column B and I will pull all of the necessary information without having to retype the formula each time.
View 9 Replies
View Related
Dec 5, 2008
i am trying to do, i have a spreadsheet with 100+ tabs all with the day of the year. Jan1,Jan2,Jan3...Feb23,Feb24,Feb25... etc etc. I want to pull all of the same values for each of these sheets on a single tab (using vlookups, hlookups and reformatting the information for a macro to input all of this data into a database)
My question is, how can I put a entry form, lets call it a1 and fill in the tab i want the contents of the page to update with? For example, if i input "Jan18" in a1, all formulas on the page look for their source information on A1.
View 2 Replies
View Related
Aug 6, 2008
I'm importing comma delimited files into Excel. These files will contain formulas, but do not know on which row they will end up in once imported. My formula's need to reference the row they have been pasted to. For example, If I paste the formula on Row 2 the formula would be =A2+B2
Row 3 =A3+B3
However, since these formulas are coming in via a non-excel file, I need the cell reference to be dynamic. Something along the lines of =A#+B# where # would be interpreted by Excel as the current row the formula is on. (I know that '#' isn't correct.)
View 2 Replies
View Related
Oct 27, 2013
can I put formula to cells on relative basis?
For instance can I write "=A1 + B1" and have a change event the formula populate for the corresponding column that changes? such that a change in C7 for instance populated the result of the formula for A7 + B7 in cell D7?
View 7 Replies
View Related
Dec 8, 2008
It's been a long time since I have worked with Excell, it use to be the " $ " that made things absolute or relative.
I have this formula:
='1'!C194+C193 on sheet 1 named "1" without the " ". I need to copy it across 31 sheets. I thought that it was the " ! ", without the " ", that makes the address absolute so I took it out and then attemped to copie it to other sheets. The idea was that the formula would then read: ='2'!C194+C193 on sheet 2, and ='3'!C194+C193 on sheet 3 and so on. The formula is in the exact same location on each sheet, C194. But this won't work. I am at this time doing each formula one at a time. I have many workbooks that will use the same style of copy and paste; about 15 workbooks each with 31 sheets.
It would help if someone could explain how to save time. I do know how to use the Ctrl to select different cells and how to use Ctrl+Page UP/Down to change tabs. I just have to get the formula correct.
View 14 Replies
View Related
Sep 21, 2009
I currently have a list of totals that i want to easily be able to change the cells that make up this total.
I have been trying to set up one cell that contains the formula and then the remaining totals below are linked to this cell. Thus I change the formula in the top cell all the other totals also change.
View 9 Replies
View Related
Feb 20, 2009
I'm working on my first real macro - so I'm a greenhorn. I've spent a few hours trying to research a solution, but most threads are too technical for me so I'm hoping someone can really help me out.
I am trying to use a formula that references a cell that changes day to day from when I use the macro. I need to use a formula which grabs from a cell in a non concrete location. Let me try and show you what I mean.
From I4:I10, I need a formula like this:
View 13 Replies
View Related
Nov 25, 2007
I am updating a wsheet via a form using vb to locate next empy row etc.
The issue I have is with the formula's no matter how I try I cannot get the formula to increment with each row. I know I can drag and auto fill on the sheet but I want to use vb if poss. eg
Previous entry to sheet:-
A1=1
B1=2
c1=A1+B1(formula)
New entry via form selecting next empty.
A2=(updated from form.)
B2=(Updated from form.)
C2=(Update code VB)
This is all okay and functional the problem starts from here
I so far have been unable to increment the formula in c1 from A1+B1 to A2+B2 using vb, is this possible or am I just chasing my tail.
View 3 Replies
View Related
Dec 18, 2007
Is it possible to have the conditional format auto adjust the cell value range (similar to how the drop and drag with the autocomplete cell values turned on works)?
For example:
If I conditional format: A1 - does not equal A2 then change colour to blue. Then use the Format Paint to copy the A1 conditional format to B1. Is there any way that it can auto adjust the cell reference to change to - does not equal B2 (the next cell down and so on) rather than having to manually go to each cell and adjust the cell reference?
Ultimately I want the following to happen:
A1 - does not equal A2 (colour blue)
B1 - does not equal B2 (colour blue)
C1 - does not equal C2 (colour blue) etc
View 6 Replies
View Related
Jan 23, 2008
Here is my array code
Dim Values() As String
Dim count As String
count = 9
For count = 9 To 1800
Values(count) = "=IF(AND(E" + count + "<>"""",AH" + count + "<>0),(AH" + count + "/E" + count + ")*100,0)"
count = count + 1
Next
I'd make it a static array but eventually I'm going to have to set it up where I wont know the upper bound.
Here is the other part of my code that I don't know what I'm doing.
If Range("A9") > 0.01 Then
Range("AI9:AI1800") = 0.01
Else
Range("AI9:AI1800") = Values
End If
I keep getting mismatch errors on the count variable, but I need it to be a string so I'm not sure what do here.
View 9 Replies
View Related
Feb 13, 2008
I am using VB in Excel to write a formula for summing a column of numbers that will vary in length. I have been using the Indirect formula and have the start and end cells of the range identified in another location. My problem is, the macro in vb is sending the Indirect formula to the cells containing the start and end points by using relative functions ie. go across 30 columns and up 10 rows to get start cell. I wanted it to just save the cell location of the start and end points.
EG.
VB formula is: "= SUM(INDIRECT(R[-23]C[27]&R3C29):INDIRECT(R[-23]C[27]&R3C30))"
whereas I just want it to read "=Sum(Indirect(C4&D5):INDIRECT(C4&D6))"
where cell C4 gives the column letter and D5 the row number of the start of the range and C4 and D6 give the column letter and row number of the end of the range.
View 9 Replies
View Related
Mar 15, 2014
I have a below formula which extracts the v and number attached to it I would like to copy this across all columns and change row for each column. So for example 1st column reference will be B4, the second column would have a reference B5 and so on, it's probably worth to mention that cells I am referencing to are in other sheet hence why there's Ref! before B4 is this why absolute column and relative row not working?
View 5 Replies
View Related
Sep 30, 2013
I have a list of University courses listed in Sheet1. Column A contains a unique course code. If there is a problem with a course, a second worksheet (called Anomalies) contains the same unique course code with description of the problem(s).
What I'm trying to do is: in Sheet 1, create a hyperlink to the relative cell in Sheet 2 that shows the course code and description. I've gotten close, but am not sure how I can do this without having to update two formulas. In short, I can't find a way to combine the two formulas.
What I've done is the following: in Sheet 1, column B, created a HYPERLINK formula, thus: =HYPERLINK("#"&CELL("address",INDIRECT(C54)),A54)
Column C contains a formula to lookup the relative position in the Anomalies worksheet, which can be referenced back to the HYPERLINK formula, thus:
=("Anomalies!A"&MATCH(A54,Anomalies!A:A,0))
Sheet1 is fairly static, but Sheet2 (Anomalies) is being updated all the time. I could just hide column C and then column B will update based on that, but I'd prefer a cleaner solution.
I've tried joining the two formulas, by replacing the INDIRECT part with the second formula, as per the following: HYPERLINK("#"&CELL("address","Anomalies!A"&MATCH(A54,Anomalies!A:A,0)),A54)
but I get an error.
View 5 Replies
View Related
Jan 7, 2014
I was able to get VBA to post this relative reference, which sticks the formula
=DATE(YEAR($D2),MONTH($D2)+6, DAY($D2)+1) into a cell in Col J.
All fine and dandy, works as it should with this formula.
Code:
.Cells(Row, "J").FormulaR1C1 = "=DATE(YEAR(RC4),MONTH(RC4)+6, DAY(RC4)+1)"
However, when I try this with a different formula =IF(ISNUMBER(SEARCH(C2,H2)),"REPEAT","SAFE"), I'm getting a compile error: Expected end of statement with this code:
Code:
.Cells(Row, "I").FormulaR1C1 = "=IF(ISNUMBER(SEARCH(RC3,RC8)),"REPEAT","SAFE")"
The error message highlights the word REPEAT. I suppose it's something with all the quotes throwing off where compiler thinks the end of the formula should be.
View 3 Replies
View Related
May 13, 2014
So have large spreadsheet with grid of elevations. Each column/row is 1 foot grid. There are blanks in it where there was no elevation data available. I need to interpolate those elevations using the surrounding known elevations. I can setup the interpolation just fine using a circular reference but what i am having trouble with is getting formula into all the cells. I need to replace blanks with formula that averages all four cells around it.
This will result in circular references that will interpolate from known points nearby. So for example if cell D4 was blank formula needs to be
=(D3+E4+D5+C4)/4. D5 would be =(D4+E5+D6+C5)/4.
I have been playing around trying to make a macro or something to do this but am not having any luck. Basically need to find/replace all blanks with formula above but so the formula references the cells around it properly.
View 3 Replies
View Related
Dec 28, 2006
When I copy the formula to another cell, It doesn't show the result. But when I double click on it, it works. For example
there r values in A1 as "john" & A2 as "Jack". I write formula =A1 in cell B1. It shows "John". Now I copy this formula to B2, it shows "john" instead of "Jack" . When I double click on B2 & enter, it shows "Jack".
View 2 Replies
View Related
Sep 22, 2007
using a macro im trying to paste in a sum formula, however this will be pasted onto different sheets and the number of records will be different for each so i cannot use absolute locations. the only constant is that all ranges will start in row I9. when it runs the following code
Range("G" & (ActiveCell.Row)).Select
ActiveCell = "Total"
Range("I" & (ActiveCell.Row)).Select
ActiveCell.FormulaR1C1 = "=Sum(I8:R[-1]C)"
Range("J" & (ActiveCell.Row)).Select
ActiveCell.FormulaR1C1 = "=Sum(J8:R[-1]C)"
Range("I:J" & (ActiveCell.Row)).Select
Selection.Copy
Range("I4:J4").PasteSpecial
it fills in =SUM('I8':I13) how can i make it insert it without the ' ' around the I9? and how can i select the two cells? (again, cannot be absolute..)
View 3 Replies
View Related
Nov 21, 2007
I want my column to be populated by formula using VBA. Let's say on row1, the formula is = SUMIF(mysheet!$L:$L,$A1&C$1&"09",mysheet!$M:$M). And on row 2, the formula is
=SUMIF(mysheet!$L:$L,$A2&C$1&"09",mysheet!$M:$M). And on row 3, the formula is =SUMIF(mysheet!$L:$L,$A3&C$1&"09",mysheet!$M:$M). And this is done up to the last row. How do I do this?
View 9 Replies
View Related
Mar 5, 2008
I have the following code which Clears the content of a cell. The next thing I want to have happen is to have another cell equal a formula but make that formula relative to its place. The formula is the following =IF(ISERROR(VLOOKUP(F56,Routes_All,2,0)),0,VLOOKUP(F56,Routes_All,2,0))
I am not sure how to paste it where the row changes depending on where it is pasted. Here is the code that does not work...
Private Sub CommandButton1_Click()
Worksheets("2008 Log").Select
Dim cRow
cRow = ActiveCell.Row '
Cells(cRow, Range("Column_Type_Of_Ride").Column).ClearContents
Cells(cRow, Range("column_duration").Column).value = "=IF(ISERROR(VLOOKUP(F56,Routes_All,2,0)),0,VLOOKUP(F56,Routes_All,2,0))"
End Sub
As you can see, no matter where I paste it it will always refer to row "F". How can I have it refer to row "cRow"?
View 2 Replies
View Related
Oct 6, 2007
I have tried a little bit of different conditional formatting but cannot seem to get what I want.
So then I found this code that will do what I want if I could get it to recognize the highlight.
Sub HilightRows()
For Each cl In Range("C:C")
If cl.Value = 202546 Or cl.Value = 102725 Or cl.Value = 192473 Then
Rows(cl.Row).Highlight = True
End If
Next
End Sub
I want to be able to highlight rows that have certain numbers. When it finds the number it highlights it and goes to the next one and highlights it and so on. The numbers reside in column 'C'.
I changed Row(cl.Row).HiddenRows = True, To Row(cl.Row).Highlight = True. But it is not working.
View 3 Replies
View Related
Dec 11, 2007
I have developed a database that is ported to other computer systems. My problem is that I have to hard-code the filename into the VBA code to identify the correct Application Object. I found that if I have activated another spreadsheet and run the code, it performs the actions on the wrong spreadsheet.
So my question is, is there a way to get the VBA code to automatically associate itself with the file in which it resides? That way, when I move the file to another computer, it knows what .xls file to use as the "Application Object".
here is what I have now (I replaced the actual paths with {x pathname}):
fname = "FCS Hardware Inventory - Cat Rev E - Ver 2.3.xls"
'fname = "FCS Hardware Inventory - Includes IVA Cat Rev E and JCPC - ver 2.3.xls"
'fname = "FCS Hardware Inventory - Cat Rev E - Ver 2.3 - for distribution.xls"
Set xlapp = GetObject("{1st pathname}FCS Hardware Databases and Catalogs" & fname)
'Set xlApp = GetObject("{2nd pathname}" & fname)
'Set xlapp = GetObject("{3rd pathname}" & fname)
Set xlapp = xlapp.Application
When I copy the file to another location, I manually uncomment the correct fname and xlApp name... a real pain, especially if I share this with a large community. I don't want to have to teach everyone how to do this every time!
View 3 Replies
View Related
Jan 9, 2008
I have 4 cells in a single row. I would like the first 3 cells to contain various data for assigned tasks to employees. The 4th cell will contain a date, but that date will only be inserted once the task has been completed. The behavior of the cells over time needs to look like this:
1. The first 3 cells must have text in them and remain with white backgrounds until the date is inserted in the 4th cell.
2. All 4 cells must turn gray once the date is inserted in the 4th cell.
A user on another forum gave me Cell Value is Not Equal to ="" as the answer. This does not work as I have text in the first 3 cells and need them to remain white until the date is inserted in the 4th cell. Using "Formula Is" makes more sense to me as an approach, but I'll be hog-tied if I can figure out how to write the formula for it.
View 8 Replies
View Related
Feb 16, 2008
As I type in text into Excel often I see number signs instead of the text. Baffling - I have tried switching the format to wrap etc - nothing works.
Can anyone help - See attached file. The short solution is to retype the text - but this occurs alot and I am not sure why. Really appreciate your help.
View 3 Replies
View Related
Mar 13, 2009
I have a report that I run off a database and extract to Excel.
It has 3 fields: Title; FirstName; LastName. For example sake lets say they are in columns A, B, C. Now in D I have a fieldname of FullName and in D2 onwards the formula: =A2&" "&B2&" "&C2.
Now I currently manually type the formula and drag it down to the same row as the last entry in the first 3 columns so that I get the full title in one cell.
I wanted to know how I could enter the the formula above in column D so that it automatically does this for me?
I tried to record a Macro as a test for column D setting the field name in D1 and the formula in D2 and got the following:
View 2 Replies
View Related
May 26, 2008
In my data validation I have the following formula as the Source: =INDIRECT(SUBSTITUTE($F$2," ","_")). I want to be able to copy the cell and paste it into another cell and have the formula update to reference the new cell. Currently the validation is for cell G2 and references F2. I want to be able to copy G2 and paste it in G3-G6 having the formula update so it refrences F3-F6.
View 3 Replies
View Related
