Cell Retains Value Derived From Dynamic Function.
Jan 14, 2009
Let's say I have three cells; A1, B1, C1. A1 = a random number (RandBetween 0 and 100) B1 = If(A1>99,1,0). I want to increment C1 everytime I refresh the randbetween via f9 so that the first time A1 returns a value of over 99, C1 picks up a value of 1 from B1 and then the next time I get a value of A1 over 99, C1 adds the value of 1 from B1 to its existing total, giving a total of 2 etc etc etc.
Essentially, what I want is: If A1>99,C1=C1+1. I can't work out how to get C1 to hold its value when I refresh the sheet. I realise it's probably pretty simple, but it would appear that so am I.
View 2 Replies
ADVERTISEMENT
Nov 12, 2009
Is it possible to have a sheet name derived from a cell?
I am creating a workbook where the users will be able to edit a list of names, and add certain data to each person. There will then be a separate sheet for each person that gives analysis if their individual data. The user will change the names in the list as people come and go, and want the sheet names to match.
View 9 Replies
View Related
Mar 6, 2014
I'm wanting to lookup the content of a cell, which is a string, "Name", resulting from a formula within that cell.
For example the first code is referencing the cell output from the second formula.
Code:
=VLOOKUP(N2,RandomTab!$A$2:$E$60,5,FALSE)
where N2 is
Code:
=UPPER(VLOOKUP(B2,OtherTab!$A$1:$H$68,8,FALSE))
presently it, the first VLOOKUP, returns #N/A
View 3 Replies
View Related
Jul 24, 2012
I am trying to use a cell reference to have excel automatically Save a worksheet using a cell reference as the workbook name. When I step through the code and use the immediate window I get the correct name for the file (fname in this case) but as soon as Excel tries to save the file, I get an error.
Here is the code I am using:
fname = Sheet3.Range("I1").Value
ChDir "S:Op CostsBudget 2013Data"
MsgBox "The actice file will be saved as " & fname
ActiveWorkbook.SaveAs filename:= _
"S:Op CostsBudget 2013Data" & fname & ".xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
View 5 Replies
View Related
Apr 2, 2013
I need to return a value from a cell in Column H, but with a row number that varies with each entry.
I repeat this formula every 5 rows or so. But the cells referenced on another spreadsheet are only one row apart. Because of this discrepance, when I copy my highly developed cluster of formulas, they only reference every 5th entry on the other sheet. What I've been doing is manually altering long, complicated formulas by hand. I can't do a replace function because every once in awhile it'll find an extra instance of the digit I'm replacing and mess up parts of the formula I don't want changed. I could avoid this tedious, time-consuming work-around if I could make a formula that would do something like this:
=Display Value from in column H: (row number per cell B1)
And cell B1 would include the number 3789.
So the returned result is the value from cell H:3789
View 4 Replies
View Related
Dec 11, 2012
I'm trying to create a dynamic COUNTIF function that references a previous cell.
For example, if I have 10 columns:
A B C D E F G H I J
a 1 b 2 c 3 a 1 b 3
I am trying to write a function that will let me reference A1:J1 and then if the cell contains an A, do a countif among the cell+1 for cells that have a value of "1". In this example, that function would return a count of 2. I think that I need some kind of combination of IF, INDIRECT, and COUNTIF, but I'm not sure how to string them all together--or maybe there's another way altogether.
View 5 Replies
View Related
Aug 23, 2006
My industry works with public money and must account for it perfectly. The formulas that are in use with my estimating spreadsheet are creating rounding problems due to formulas carrying out numbers to many decimal places. When I then shorten them to currency they appear to round up but when you multiply these numbers they give incorrect totals due to still being many decimal places. How can this be fixed so that the cells are permanently rounded?
View 5 Replies
View Related
Nov 8, 2008
I am selecting a block of cells to apply numerous "Replace" functions to.
So I use the Selection.Replace command - for example:
Selection.Replace What:="Street", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
if I have been working with Excel previously and did my own "Replace" and applied it to the entire Workbook (i.e. changed the within field from ' Sheet' to:
"Within: Workbook"
Then that field stays set to "Workbook" for subsequent Replace activities, including my VBA code !! Therefore if I do not manually go back and run one "Replace" and set the within field back to "Sheet", the VBA code will apply my Replacements to every sehhet and every cell in the entire workbook. Even if I have selected a Range of cells before issuing the command !! It ignores the selected Range and runs the "Selection.Replace" for the entire Workbook.
The "fix" I found on another site is to run a dummy command:
Set dummy = Worksheets(1).Range("A1:A1"). Find("Dummy", LookIn:=xlValues)
Which works. However, I am looking for a way to add a parameter to the "Selection.Replace" command that will cause it to search using the "Within: Sheet" setting. Otherwise I always have to remember to add that dummy line of code for every single Selection.Replace line of code.
View 9 Replies
View Related
Apr 12, 2012
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"))
View 3 Replies
View Related
Feb 28, 2014
The goal of this chart is to show how sales are adding up relative to bonus levels for the month. The problem for me is in getting the right axis tick marks, labels and chart lines to match the values in Columns C and D. This need may be further complicated because the target amounts increase by a fixed percentage (11% in this sample, but it can vary).
View 4 Replies
View Related
Feb 21, 2010
I'm trying to sum each months visa purchases. The data is in three columns: date,value and payment type.
I can't get this formula to work:-
{=SUM(IF(AND(Purchases!C6:C999>DATE(2009,12,28),Purchases!C6:C999<DATE(2010,1,29)),IF(Purchases!K6:K 999="Visa",Purchases!H3:H999)))}
It's returning a value of 0 (and if that were true I'd be delighted!)
View 12 Replies
View Related
Dec 7, 2009
I have a sheet with cells that will vary and change in value. The far right column (in this case G) is calculating a percentage from cells in columns D and F. At the moment only 9 rows are being used but I have made provision for the list to extend down to row 100 for future use. Cells in columns D and F are receiving their values from sumif formulas on another sheet, therefore a lot of cells are showing a 0 value for the moment (which I have formatted to show blank).
My problem is that I have cells in column G (percent formula) which are of course showing #DIV/0! due to the fact that they are calculating cells with 0 value. How can I format the cells in column G to show blank until they receive a real calculation!
View 2 Replies
View Related
Jan 20, 2010
I'm trying to automate the solver using a vb program. I have found this example which is just about spot on for my needs:
View 4 Replies
View Related
May 14, 2009
I am trying, starting from a list that has the same structure as a Pivot Table, to recreate the list it was derived from.
I added a sample to show what I mean.
View 8 Replies
View Related
Dec 10, 2013
I've been trying to get this SumProduct formula to return anything except an error for a while now.
=SUMPRODUCT(('Main Log'!$E$13:$E$17>=$C$2)*('Main Log'!$I$13:$I$17
View 2 Replies
View Related
Jun 3, 2009
I'm trying to create a sumif formula that finds the column the information is in that it needs to sum. I have attached a copy of the file I'm trying to create a formula to get results that I want.
Generally you would normally use the "=SUMIF(range,criteria,sumrange)". I was wondering if there was a formula I can substitute "sumrange" to also search a range of data (Eg: "G16:DA28"). I need the range to be dynamic as the column to be sum can change based on the criteria/option button being selected from the product category. I assume there is an indirect function involved, but I am not familiar with it. If you have a better and less complicated formula please suggest.
View 6 Replies
View Related
Oct 1, 2013
I'm trying to create an index array that resizes its array starting location.
{=IFERROR(INDEX($V$17:$AU$17,1,MATCH(TRUE,V18:AU18
View 3 Replies
View Related
May 5, 2006
I have created a Macro in VBA which takes a data set, puts in in a pivot table which I then use to sort/select etc. after that I copy the data in a new sheet as ' values only' with paste special.
As the source date is variable (number of lines can vary) I have set the macro to take the variable number of rows into account.
Evertyhing works as it should.
Now my question:
in the final sheet, I want to fill the first empty column at the end of all filled-in columns with a count function. selecting the last column works, but I have problems with making the COUNT function variable. It should count the number of cells with a value in it. However as the number of columns can vary, the count function should take that into account. Furthermore, it should never take the first and last column into account.
Here's where I got stuck: ...
View 4 Replies
View Related
Jun 24, 2006
I want to use a dynamic range in order to use this formula below in column A as long as there is something entered in column H = SUMIF($N:$N,A5,$I:$I). any ideas on syntax and will this be using the offset function within excel. I know I have posted a couple times about getting this formula to replicate itself down certain columns as long as someone enters text in another. I looked into the offset reply information somone posted but it seemed a little over my head.
View 4 Replies
View Related
May 9, 2007
I am trying to replicate the following code using Cells references so that the row can be made a variable. I basically want to make the search array smaller with each loop.
LMatch = WorksheetFunction.Match(Cells(3, 3), Sheets("Product Matrix").Range("B3:B250"), 0)
The above works, the following two variations don't:
LMatch = WorksheetFunction.Match(Cells(3, 3), Sheets("Product Matrix").Range(Cells(ArrayStart, 2), Cells(250,2), 0)
LMatch = WorksheetFunction.Match(Cells(3, 3), Sheets("Product Matrix").Range("B3:B250").Item(ArrayStart, 2), 0)
View 7 Replies
View Related
Jun 1, 2008
=INDIRECT(CHAR(39)& F2 & CHAR(39) & "!BC7")
I would like to make BC7 dynamic. In other words, the text in this formula, BC7, should come from a cell A4 which today says BC7 but next month the value in A4 will say BD7.
View 9 Replies
View Related
Dec 10, 2013
Basically i have a list of data for example from B2 to B2000 for which i want to calc a standard deviation across however the number of data points in the standard deviation need to be dynamic.
For example in Cell a1 i can enter 150 it will then calc the standard div across the first 150 data points (points 1 to 150) then in the cell below calculate the next standard div.
Standard Div Points10
Data PointData ListStandard div
10.167442459
20.539073451
30.29740845
40.309440859
50.640742715
[code].....
View 2 Replies
View Related
Aug 18, 2014
I have attached a workbook.
I have a calculated value in H2. This value will correspond to values in the table provided. This value is dynamic and will change based on criteria that will be added.
The value H2 is a combination of to "letters" D1 and A2 or A4 and B1 i.e. trace back where the intersection of H2 occurs within the table
In Cells A8 and A9 I am trying to represent which combination of letters will result in an intersection of the value H2. In the example provided it is C and A.
What i would like to do is have Cells A8 and A9 be more dynamic. So if for example the value in H2 changed to say R12,06 then the intersection would be B and B. Therefore Cell A8 = B and Cell A9 = B
The formula is cell A8 and A9 doesn't cater for this as the range is fixed. I need a dynamic range for the index and match function based on the where the intersection of H2 happens to be in the table.
View 4 Replies
View Related
Jun 22, 2013
I will receive one master file which contains the table and it's values and I have another working sheet where I will update the require details. Every time I will copy the entire master data into working file in order to extract the values by using vlookup function. note that the master file has different names each time. So, that if I use vlookup by taking master data path but not getting the results.
I am looking one macro code to select file (master data) and paste the values based on vlooup references in workings sheets. Note this master data file name may change but reference range always same.
View 9 Replies
View Related
Jul 19, 2014
I have a worksheet where the number of rows will change on a weekly basis and I would like to print the sheet out without having to change the print range every week.
I have tried to use the OFFSET function in the print range in print setup but once this has been entered and printed, it changes to the actual print range the OFFSET returns.
View 1 Replies
View Related
Sep 18, 2006
I have a dynamic validation list (see attached) which will not accept the indirect function as a formula in the listbox.
Using the named range on its own works fine, but using the indirect generates an error.
View 3 Replies
View Related
May 27, 2013
i'm running out an option, i must use offset or index function but there are problem found: 1.Offset function cannot use a dynamic height or number of columns and the result are #NAME?, here is the code
VB:
Private Sub CommandButton1_Click()Dim k As Variant
Dim b As Variant
Range(Me.RefEdit1).Name = "item"
[Code]......
View 3 Replies
View Related
Apr 9, 2012
I have an excel workbook with a dynamic dependent drop down list setup, works a treat on the first worksheet i set it up on.
However here's the problem, my workbook contains a good handful of worksheets each named to reflect the certificate on the page, i need the drop down list to work on each tab. problem is when i setup the index/match function it requires a specific sheet name to make it work i.e.
=INDEX(Table1,0,MATCH(PI!$C20,Table1[#Headers],0))
However what i need it to do is reference the worksheet name, as new ones get added and names may change, something like
=INDEX(Table1,0,MATCH(&ActiveSheet.Name&$C20,Table1[#Headers],0))
However this doesn't work, i do have a cell on each page that holds the Sheet name using the function:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
But I cant even get it to use that cell as a reference in my formula.
View 9 Replies
View Related
Dec 13, 2012
Essentially i get a Runtime 5 error on the bold bit of code....
I am trying to define a range .... by using thexlUp function find the last row ( which works ) i then pass this variable into the sort code and get the error its probably very simple to fix, all it does i sort columnA but finds the last cell .... instead of the whole column,,,,
Sub Macro7()
'
' Macro7 Macro
'
' Keyboard Shortcut: Ctrl+e
NumberOfRow = Sheets("Sheet1").Range("A3000").End(xlUp).Row
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Sheets("Sheet1").Range("A2", Cells(NumberOfRow)), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
[Code] .......
View 2 Replies
View Related
May 18, 2007
i have 2 worksheets. one with portfolio stock data, and a second that i would like to use for dynamic charting. the chart worksheet has a drop down menu that chages a refernce cell to a number corresponding to the collumn in the portfolio worksheet for the chosen ticker
i would like to make a dynamic chart that updates the data ranges based on something like this: ....
View 9 Replies
View Related
