Use Cell Variable In Formula
Aug 17, 2007
I am having an issue using vlookup with named ranges.
I have a work book with 3 sheets on, the 3rd sheet has lists defined as named ranges and is named as lists.
The first and second sheet has references to the lists on this sheet, using the validation e.g
=(named range)
I was using the following if statement =IF($E$7="web policy",web_policy,IF($E$8="NUD",BU,IF($E$8="RAC",BU,IF($E$8="IB",IB,IF($E$8="BAU",BAU,not_known)))))
But I now need to add some more to this and have decieded to use Vlookup instead.
I have the vlookup in the 3rd sheet (lists sheet) and when the vlookup result is returned it returns the list name that I need to select, the problem I am facing is using this result to produce the list on sheet 1 or sheet 2.
The vlookup I am using is =VLOOKUP('Test Conditions'!E8,AD1:AE7,2,FALSE), but I can't use this on the first sheet as validation can't look at other sheets.
I have now changed formula slightly to;=VLOOKUP(ad32,AD1:AE7,2,FALSE), AD32 contains the formula that references the first sheet, but when I paste this into the validation I get the following error.
The list source must be a delimited list, or a reference to a single row or column
View 9 Replies
ADVERTISEMENT
Aug 13, 2009
I have 2 sheets, Sheet1 and Sheet2. I want to populate Sheet2 Column B with a formula which will reference Column B of sheet1 so that on each iteration of the For..Next, the cell being referenced in the formula will change. So if i = 5, I'd like the cell to be populated with the formula =Sheet1!B5
View 4 Replies
View Related
May 18, 2009
I have a number of Cell names LR0Cost, EX0Cost, IM0cost that hold %
formula might be
View 4 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
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
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
Apr 10, 2007
when i am doing the coding
may i know if there is a way to replace the cell address $A9 below: ...
View 3 Replies
View Related
May 24, 2007
Say cell C5 contains the name of a project (workbook). eg
C5 = "[Project1]"
In my current workbook, I want to find the value in cell B8 of Sheet1 of the project shown in C5. If I wrote this directly, it would be "='[Project]Sheet1'!B8"
But the name of the project is a variable shown in cell C5 (as explained above). So I need a formula that will find the value in cell B8 of sheet1 of the project shown in C5.
View 7 Replies
View Related
Oct 8, 2007
Here is what I would like to do: Have a formula like Max(B12:B14) change to Max(B12:B18) when I change the value in different cell from 3 to 7. I would like to stay away from the macro world and keep in formula world if that is possible.
View 6 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
Mar 3, 2007
The master has rows corresponding to numbered files:
4300 | CellValue1 | CellValue2 | etc
4301 | CellValue1 | CellValue2 | etc
In attempting a simple formula (a couple VBA codes I tried did not work and were probably substantially more than I needed anyway), I made the following:
='CMain FolderSub Folder[concatenate(cell w/file number,".xls")]Sheet1'!A1
This is identical to the copy/paste link formula currently in use except that I am trying to have the actual file number/name generated off the master list instead of what is in the file itself.
Purpose: in case I was not clear on this above, I am trying to automatically populate the master worksheet with the data from the workbook. The current procedure is to copy a line of the data from the workbook, then paste as a link in to the master worksheet on the row corresponding to the file number.
View 6 Replies
View Related
May 29, 2008
I am having to copy and paste rows of data into a new worksheet where the rows sizes change and I am wanting to add a new row at the end of the pasted rows but with the sumation formula to add the relevant column
e.g copy range B14:AA17 with in this case columns E to AA holding the numerical values. Therefore I wish in cell E18 to sum the value of E14:E17 and so on ending with cell AA18 holding the sum of AA14:AA17
As these vary I have all relevant variables, Range to add sumation values to eg E18:AA18
Start Cell E14 and so on.
I tried adding "=SUM(x:d)" where x and d are vars relating the the column cell required eg x = E14 and d = E17
View 3 Replies
View Related
Apr 5, 2007
I'm trying to enter the = sum formula using vba with one of the ranges in the formula being a variable. Can not seem to get the following to enter the formula correctly.
Last_cell_4 = Range("A4").End(xlDown).Offset(0, 3).Address
Last_cell_5 = Range("A4").End(xlDown).Offset(1, 15).Address
Range(Last_cell_4).Offset(1, 0).Formula = "=Sum(D4..Last_cell_5)"
View 4 Replies
View Related
Nov 23, 2012
I have a data set which is structured such that there are variable numbers of products (column A) from 1 - 48 and these repeat multiple times for each "Name" held in column B. I have attached an example of this which uses a data set with 7 entries reapeating 7 times.
I need excel to insert 2 formula for me multiple times which needs to varry according as follows
1. Calculate the average value of data in Column C - G for n cells starting at a specific cell (C2), I have inserted an example of this and highlighted it in yellow. This then needs to repeat down column C several times, the number of times this will repeat depends upon the number of different names held in column B. But I have this calculated already and stored in a cell im my main document.
2. Calculate the variance of each value in the x cells above from the average calculated in point 1 above. I have highlighted this also in yellow.
View 1 Replies
View Related
Jun 18, 2014
I would like to do the following in a copied down row where n5 is a cell that contains a number that is added to a row number in order for the range to maintain n5 rows when copied down.
=average(b1:b1+n5)
View 11 Replies
View Related
Nov 3, 2009
I'm using Excel 2000/2002. I have a workbook with 12 sheets named Jan, Feb, etc.
I want to add a new sheet (Report) with formulas in various cells to get data from a cell in a particular sheet.
For instance, in a cell of the new sheet is: =Jan!D64. I want the user to be able to select a month from a drop down list and for the formula to change sheets depending on the month selected. The formula should be: =(sheet name!)D64. I tried playing with INDIRECT, but maybe couldn't get the syntax right. I can't use macros, the Excel is on a server and they are not permitted. The end user is less knowledgeable in Excel than me.
View 2 Replies
View Related
Apr 7, 2014
Adjust this piece of code:
[Code]....
The lookup is for 00.2014, but this is to fixed. Want to use the same code next year to. So I already defined the variable 'jaar' which the user can choose with a validation. (and next year they set it to 2015).
I thought this code would do it but no luck:
[Code] ....
So what would be the correct way? Been shifting with the " " but its only make more and more mess.
View 2 Replies
View Related
Mar 31, 2007
ActiveCell = "=(SUM(R3C5:R[0]C[-3]))"
In a macro I use that formula to add the cells of column E (or 5) from Row 3 down to the row of the active cell which is in column H (or 8), (the active cell would be H9 in this instance); and the result goes to the active cell as the formula =SUM ($E$3:E9).
But I want the result to go directly to a numeric variable, not to a cell. Something equivalent to this:
a=(SUM(R3C5:R[0]C[-3])), which is not correct.
What would the correct syntax be?
View 14 Replies
View Related
Jul 14, 2008
Column A will be completely empty.
When an * is inserted into a single cell in column A, that Row number will become the variable in this equation:
=Sheet1!$G$'variable'
i.e. if i put a * in A14, then the formula should read (=Sheet1!$G$14).
View 10 Replies
View Related
Nov 8, 2011
It's probably O so simple but: Using a variable in R1C1, here's the attempt:
-----------------------------
Dim RS_Period As Double
-----------------------------
RS_Period = Sheets("Sheet1").Range("B17") - 1
-----------------------------
Range("E2:E" & LastRow).FormulaR1C1 = "=(RC[-2]-OFFSET(RC[-2],9,0))/OFFSET(RC[-2],9,0)"
-----------------------------
I lieu of the "9" I need to use the "RS_Period" which is an offset value.
View 4 Replies
View Related
Jan 8, 2012
I try to adjust chart range using variable value instead of constant value.
My current code :
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).Values = "='Check_2G'!$Z$2:$Z$32"
I need to make range ( "='Check_2G'!$Z$2:$Z$32" ) adjestable with varible like m instead of 32 in previous code.
View 2 Replies
View Related
Jan 17, 2012
I am trying place the "sum" formula into multiple cells, changing with "i" (itteration process).
Something like:
.Range(17,counter).formula = "=Sum(B2:B20)"
when the couter itterates, the same line of code should give:
.Range(17,counter).formula = "=Sum(F2:F20)"
My actual code looks like: ... which doesnt work.
.Offset(17, " & ColumnHeadCout & " + 1).FormulaR18C" & ColumnHeadCout + 1 & " = ""=SUM(R[20]C[" & ColumnHeadCout + 1 & "]:R[" & NumberStreamElem + 20 & "]C[" & ColumnHeadCout + 1 & "])"
View 9 Replies
View Related
Jun 18, 2012
I need to create a new variable according to the formula:
Suma( Foreign_Owenrship_Plant * Number of Full Time Employees)/ Suma(All Full Time employees in the sector)!
However previosly I need to filter the firms within a sector within a country according to a three digit ISIC code. Lets say in Albania, in year 2002 in the Transport sector there are 5 groups of 3 digit ISIC codes. Once I have filtered them then I can proceed with calculating the new variable. How can I do this using programming?
View 1 Replies
View Related
Nov 6, 2013
Trying to Figure out the syntax for setting range properties with a variable. Is this possible?
Code:
Sub Testing()
SomeVariable=7
.Range("B7").Formula="=Average(A & SomeVariable:C & SomeVariable)"
End Sub
View 2 Replies
View Related
Jul 25, 2007
I allocate result of formula FORMULA R1C1 into the active cells,,,AND NOW I wan to allocate it into a variable x or y (I dont want that the result appears in the spreadsheet, just into a variable to work the code!)
View 9 Replies
View Related
Mar 7, 2008
I have a macro which defines a workbook selected by the user as the name ServicingInfoFile.
The macro then proceeds to write vlookup formulas in another workbook to pull data from the ServicingInfoFile workbook.
Only problem is that the code errors out at the vlookup formulas every time.
Can I not use a Variable in a formula?
Is it just my syntax?
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'" & ServicingInfoBook & "'!C1:C2,2,FALSE)"
View 9 Replies
View Related
Jun 22, 2009
I am trying to write a formula in code that uses a changing variable, "X".
here is what i have thus far:
.Value = (("G" & X) + 1) - (("G" & X) + ("B" & X))
I am trying to say: ((GX)+1)-((GX+BX))
X is a variable that changes in a loop. it will be the row number. so for example:
(G18+1)-(G18+B18)....I AM APPEARNTLY NOT WRITING THE SYNTAX CORRECTLY.
View 9 Replies
View Related
Sep 16, 2009
if it is possible, to use a string variable in a Formula via VBA.
For example: ActiveCell.Formula = "=SUM(Sheet3!strVariable)"
Of course the code is not working that way. The Variable has the following structer: CellAdress;CellAdress;CellAdress... so it would perfectly fit into the formula-syntax.
Is there any possible way to do that? The reason behind it is, that I have to deal with a flexible range and I want to process this range with a formula. Actually the range is flexible, because of different filters and I only wanna count all numbers in the unhidden rows.
My varibale would then contain all celladresses, for the visible cells.
So in a nutshell: is it possible to somehow use a varible in a formula?
View 9 Replies
View Related
Apr 21, 2006
Just a quick question, is it possible to use a string variable in a Excel formula (in VBA)? Im trying to do the following in VBA
mortgageSchedName = acName & " Schedule"
Range("B6").FormulaR1C1 = "=mortgageSchedName!RC[254])"
the above results in an error because there is no such sheet as "mortgageSchedName", but I want it to access the value of that string variable.
View 6 Replies
View Related
Feb 6, 2007
I’m trying to have a transpose formula with a variable in the VBA code. The Data is in column AG, starting in row 6 but the end is always different. The transpose formula should copy the data into Column Z (also with a variable row). So if the Data is AG6:AG7 it should go for example to Z 50 and AA 50 etc.
Dim lngTransposeBottom As Long
Dim lngFormulaDataBottom As Long
Dim countnonblank As Integer, myRange As Range
Set myRange = Columns("AG:AG")
countnonblank = Application.WorksheetFunction. CountA(myRange)
Range("Z" & lngFormulaDataBottom).Offset(3, 0).Select
Range(Selection, Selection.Offset(0, countnonblank)).Select
lngTransposeBottom = Range("AG65536").End(xlUp).Row
Selection.FormulaArray = "=TRANSPOSE(R6C33,R""" & lngTransposeBottom & """C33)"
Unfortunatly I always get the following error message: “Unable to set the FormulaArray property of the Range class”. how to change the code to get it up and running?
View 3 Replies
View Related
