Dynamically Updating Function / Formula?
Aug 8, 2014
What I need to do is basically use one sheet from my workbook as a 'template' for all the other sheets, but in a way that when I make a change to a formula on my 'Template' sheet all the other sheets based off of it follow suit. I need to do this because for 100000's of rows, spread across different sheets, I have product information populating Columns A-G, and their unique cost in Column H. I need to apply markups to each product (row), but all of these markups are standard across every product and thus every sheet. So in the perfect world I would be able to edit the formula on Sheet #1, and then have that change be reflected in every other sheet.
So let's say I have something like this on Sheet #1...
[URL] .....
Where Column C is a product of A & B...ie. "=A2*B2"
What I need to happen is on Sheet #2 is for it to somehow pull the same formula that is on Sheet #1 in Column C, but apply that formula to 'A2' & 'B2' on sheet #2. And I also want it to update whenever I change the formula in Sheet #1.
If it requires that I set up a template on a completely different workbook, that is perfectly fine as Sheet #1, my 'Template' Sheet, does not hold in actual product information.
View 14 Replies
ADVERTISEMENT
Apr 20, 2009
I'm hoping this is a simple question someone might help clear up for me.
I've defined a few cell ranges on a worksheet (in my example I've defined their names as site1, site2, site3, site4, site5). All these ranges are identical in # of columns and rows.
On a separate worksheet, I'm trying to display a single named range, depending on the value in a dropdown menu.
In my example, I have a pulldown menu with the following items: site1, site2, site3, site4, site5 (mirroring the named cell ranges). I'd like to display one of these ranges, depending on which name is selected in the dropdown.
View 6 Replies
View Related
Oct 26, 2011
My workbook simply has multiple columns of data which will be summarized visually in a tag cloud (I have the tag cloud part taken care of already creating either a jpg or png, I simply have to change the extension within a batch file to change between picture formats)
I need to have the tag cloud image display and update depending on which column the user would like to summarize (for now I have simplified the issue by only having one of the data columns in my workbook, and will worry about the user selection of the data column to be summarized later).
My Main Question:
Focusing on the image display and updating now, whether it be an ActiveX Image Control or any other way. I've been playing around manually before I try and code it and I can not get either a jpg or png file to attach to the ActiveX control (both of which cause an Invalid Picture Message).
The file name can stay the same if need be and for now I think/assume that'd be easiest (I have my Tag cloud generator just replacing the previously existing image file of the same name).
Would ActiveX control be the way to go for this dynamically changing image?
View 9 Replies
View Related
Jul 20, 2006
I am using the STDEV function on a range of values: STDEV($I$26:I2545). My starting cell is constant and I would like to make my ending cell variable based on a value I enter in a master cell (A1). The value I enter in the master cell is multiplied by 252 the product would equal the row number I would like to stop at. So if I enter 1 the formula will be STDEV($I$26:I252). I tried: STDEV($I$26"I"&(A1*252))) and other such variations to no avail
View 5 Replies
View Related
Apr 21, 2009
I have a Big Question about the SMALL Function in Excel. I would like to know if it is possible to use a syntax that will make the small function update my graph dynamically.
Personnel transfer out of the Department on know dates... but they come in to the Department all the time. I would like to know if I can increase the "range of my data" even if I don't have data in the cells (i.e. B2:B6 have data, I want the range to include B7:B16, which has no data)... When I have no data in the cells I get errors/ circular references...
Ideally, I would like be able to add or remove personnel and the SMALL function would "organize/update" my graph dynamically. Is this possible?
View 6 Replies
View Related
Dec 30, 2009
I have the folowing vba function that i use to check the existance of files but when the workbook is refreshed, the formula doesn't update. It's only updated when i double click it's cell.
View 2 Replies
View Related
Mar 1, 2013
I created a simple custom function called File_Path() using this macro line:
File_Path = ThisWorkbook.Path
I use the function in Cell A1 and it displays the path, but it doesn't automatically update when I change the path. I have to use F2 to get it to update. I have automatic calculation on.
View 6 Replies
View Related
Jun 25, 2008
I'm using a heavy excel file and I have linked some cells in one sheet to other worksheets in the same file. My problem is that when I change the value in some cells they do not change in the other cells linked to the ones I have modified.
View 7 Replies
View Related
Aug 1, 2007
I have a formula in the first worksheet called 'Summary' of my workbook. This formula calculates average of the data in other worksheet. How do I change the worksheet name in the formula dynamically?
e.g. The second worksheet is January. The formula in cell A1 of worksheet 'Summary' calculates the average of the data in the range A1...A12 of worksheet 'January'. When I copy this formula to cell A2 in worksheet 'Summary', the worksheet name in formula remains 'January' and it does not change to 'February'.
One more important thing is that the worksheets are being added automatically i.e I can not define all the worksheet at first go.
View 4 Replies
View Related
May 5, 2009
I have two sheets. One called "Roster" and one called "final". On the final sheet i have cols for each question on the final exam. I also have a total col which sums up the pts for each question.
On the "roster" sheet. This is kinda like a summary sheet. On this sheet i use a vlookup (shown below) in the cells which are supposed to reference the cells on the "final" sheet for the total pts.
View 10 Replies
View Related
Oct 29, 2008
I have fought with this for 2 days and I am trying to have a worksheet cell update the date and time if the cell it is referencing (B3 see below)
Here is the formula I am using however it updates every time a new row is added to the worksheet. So the Date/Time stamp on each row is always updated to NOW() everytime.
=IF(ISNA(B3),"0/0/00 00:00",NOW())
I would like to have a Date/Time stamp stay as is once that row has been added. Is there anyway to stop the field from updating once the date and time have been set?
View 9 Replies
View Related
Aug 5, 2013
It seemed to be working OK for the first hour I had my sheet open. I then noticed that a variation of the following Sub would not update unless I clicked somewhere else in the sheet or edited another non-included cell. It's like something is holding up Excel from running this function and updating the cells if b2 is greater than a3 then a3 will not update to = b2 immediately.
Private Sub Worksheet_Change(ByVal Target As Range)
If [b2] > [a3] Then [a3] = [b2]
End Sub
My data in column B is formula derived and that formula contains data that is live updated data brought in from DDE link.
My question is, should I setup this formula as part of a macro, instead of workbook code and attach an Application.Ontime function to ensure reliable execution? Could too many IF statements cause delays (about 100 of them currently in the code Private Sub Worksheet_Change(ByVal Target As Range))?
View 1 Replies
View Related
Jul 25, 2006
I am trying to get the following routine to work. I need this formula to run dynamically when a user changes cell F27.
Sub PotentialUnitSales()
'Dim x As Range
Set Range("J27:J200") = "R[1]C[5]-(RC[5]-RC[3])"
'Range("J27").FormulaR1C1 = "R[1]C[5]-(RC[5]-RC[3])"
rng = Range([J27], Cells(Rows.Count, "J").End(xlUp))
End Sub
View 4 Replies
View Related
Jan 23, 2007
Below is the code. It seems to be creating 50 thousand rows below the data already entered in the worksheet "DELPHI DATA". What I need to do is change it to only add those formulae or pasted values to as many rows as already have data entered in them (which may eventually approach 50000, but may remain at only a few thousand.)
Sub Refresh_Current_Month() ...
View 4 Replies
View Related
Feb 17, 2009
I want to write a formula that inserts a column and then does a vlookup in that new column using the cell just to the left of it as the first variable. The problem is, this could be in any random column so I can't do a static reference to $A2 as an example. So, my question is...
Instead of using =vlookup($A2,Sheet1A1:B50,2,false)
How do I replace the $A2 part with some code that says, "the cell to the left of here..."
View 7 Replies
View Related
Oct 21, 2011
I am using a spreadsheet which imports information from an access database then calculates prices based on that.
At the moment in my column "0" I am referencing the number values in column CT from my access database (which gives me a standard retail price)
Code:
=IF(AF5="","",IF(OR('Line list'!CT2="",'Line list'!CT2=0,'Line list'!CP2=0),"0",'Line list'!CT2))
And then I use the following formula to work out the correct prices wholesale prices for different countries based on that.
Code:
=IF(AL5>1,AL5,IF(AK5
View 5 Replies
View Related
May 10, 2012
I have an Excel Sheet1
The sheet has horizontal info a list of "headers" going horizontally across row 2. So row 2 may have A2= Date, B2=Name, C3=Style, D3= ........
The header rows will vary in some cases. I want to use a formula to copy and transpose the information to Sheet2. I would like the list of headers to go vertical instead of horizontal. So A1=Date, A2-Name, A3=Style, A4= ..... all the way down.
I know about Copy -> Special -> transpose but I need something that is dynamic and simple. I know I can use the indirect function and do something like this =INDIRECT( "Sheet1!" & ADDRESS(1,2) ) . However I was trying to figure out two things. How best can I make the columns dynamic and increment up especially if I am not starting at A1? Is there a better formula to use that is not volatile like INDIRECT?
View 3 Replies
View Related
Aug 21, 2006
I have a table which takes the average of the last three years. The formulas are in cells below the data. The data is set up to be first data down to the oldest data. each time data is added, a row is put in on top. how do i get the formula in the cell to update dynamically. What would the average formula look like in the cell? Do you just say =average(name of file!RANGENAME).
View 5 Replies
View Related
Sep 1, 2006
How do I return the data from a single cell in a named range. If I type
=INDIRECT("B12")
into cell "B10". It will return data once, but as cell "B12" changes the value in "B10" does not update. The "B12" cell is part of a named range called "Datainfo".
View 6 Replies
View Related
Oct 26, 2006
I have a workbook (we'll call it Mainbook)that has links to files on a shared drive (call these subbooks). For some reason, unless the "subbook" files are open, my links in "Mainbook" will not update.
View 9 Replies
View Related
Jul 29, 2014
I thought I could update formulas dynamically by dragging into new cells but it's not working.
Problem: I have a time series of input from a device that samples at 40Hz. The output I get from this device in Excel consists of 40 columns in row 1 (representing the first second) and then it creates a new row - row 2 - which also consists of 40 columns of values (representing second 2) - and it does this until the end of the response period which for me is 10 seconds. When I do a quick filter I end up with 10 rows, each consisting of 40 columns of data and all of this represents 1 trial. This then repeats for 32 trials.
I want to have all of my data for each trial in the same row. So I want the first 10 rows essentially collapsed into 1 row so instead of a 10x40 matrix representing one trial I have a 1x400 matrix representing that trial.
It starts out well enough - I make a row for my first trial and, if trial 1 second 1 = H2:AV2 and I'm typing in cell AY2, I just write =H2 in cell AY2 and drag across for 40 cells until I get to CK2 which will have =AV2 in it because of the automatic updating from dragging. Then I move one cell over - to CL2 - and type in =H3 and start the whole process again until I have all my 400 values in one row. I know this is a tedious way to do this but I figured once I did this it would be a simple matter of formulas and dragging to fill in the rest.
Not so. Is there a way to dynamically update references? So for example, cell AY2 has the formula =H2 in it. Now I KNOW that in AY3 I want to have the formula =H12 (because the beginning of the next trial is 10 rows down from H2) and I know I want AY4 to have =H22 etc. but when I drag the reference to H2 down it just changes it to H3, which makes sense but having a formula like =AY2+10 returns the value in AY2+10 instead of the reference, which again makes sense but I'm totally blanking on how else to do this. I've tried using offset and indirect and offset, for example, will work if I hard code in the numbers (e.g. =offset(H2, 10, 0)) but if I drag this formula down neither the 10 nor the 0 changes so I get the same formula in every which is obviously not what I want (and I guess if it did change, it would just change the 10 to 11 anyway, which again, is not the increment I'm looking for). I've also played around with adding constants of 10 and got nowhere, probably because I'm doing it wrong because I'm fairly sure I'll have to add a constant of 10 somewhere.
View 5 Replies
View Related
May 28, 2013
I have the following formula that picks up the cost of the item with a certain number and the description includes the word post, however I now have a scenario whereby under a certain number there are several items that include the word post and the formula is only picking up the first one and not the sum of them all.
Formula:
{=IF(ISERROR(INDEX(Sheet1!$E$3:$E$706,MATCH("*"&$C$1&"*"&A3,Sheet1!$C$3:$C$711&Sheet1!$F$3:$F$707,0))),0,(INDEX(Sheet1!$E$3:$E$706,MATCH("*"&$C$1&"*"&A3,Sheet1!$C$3:$C$711&Sheet1!$F$3:$F$707,0))))}
Data:
Description
ID
NAME
Qty
Unit $ exc
Number
[Code] .....
Result:
Job Nos
Product Description
Post
24741
IN Wave
124.3
Is there anyway to pick up all the information?
View 5 Replies
View Related
Jul 30, 2013
I am trying to create an IF formula to check a cell (A2) and if that cell is populated to return a non updating date in a different cell (K2).
View 4 Replies
View Related
Jul 27, 2012
I have the below formula working correctly. However the issue is when it pastes and fills down values its using H1 reference in the formula even when it goes to the next column. So basically when it goes to cell I2 it should update the sumifs formula to lookup I1 instead of H1 and so on and so forth for all columns till the loop stops.
VB:
Sheet1.Activate
Dim frmla As String
frmla = "=SUMIFS($C:$C,$A:$A,H$1,$B:$B,$G2)"
Range("H2").Activate
Do Until ActiveCell.Offset(-1, 0) = ""
ActiveCell = frmla
ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1).Activate
Range(ActiveCell, ActiveCell.End(xlUp)).Select
Selection.FillDown
ActiveCell.Offset(0, 1).Activate
View 1 Replies
View Related
Aug 13, 2009
if it is possible te refer to a cell in another worksheet.
I have 10 worksheets in sheet one I put in cel A4 a digit en sheet 2 I put the formula that refers to sheet 1 cel A4.
Now I want a formula that refers to sheet -1 so in sheet 3 the formula automatically refers to sheet 2 cel A4 and in sheet 4 the formula automatically refers to sheet 3 cel A4 without corrections to the formula.
View 9 Replies
View Related
Jul 26, 2007
I have a worksheet with many different formulas in many different cells. When I insert or delete a row, there is one formula in one cell that does update to reflect the change in rows. The cell is located several rows below the section where rows are changing. It is a simple formula too. Here is the formula: "= SUM($E$3:E11)*0.09". If I delete more rows that cause the highest row number to be less than 11, this formula updates. If I insert more rows and go beyond row 11, the formula does not update. I have tried using $E$11, $E11, and E$11 to no avail.
View 9 Replies
View Related
Jun 9, 2008
I have a series of worksheets, named, 'sheet1' 'sheet2' etc. through to 'sheet10'. A final worksheet, 'sheet11' has been produced that uses the formula: address=Sheet1!A1, address=Sheet2!A1, address =Sheet3!A1 etc. The same goes for other identical cells on each of the sheets. I am at the moment entering each cell on 'sheet 11' separately because if I copy the formula down it progressively changes the cell value and not the sheet number. How can I produce a formula whereby the cells remain identical but the sheets change progressively?
View 4 Replies
View Related
Jan 27, 2014
I am trying to drag down a formula (see below). the cell reference A6 and E6 is not updating to A7 and E7 etc etc when i drag the formula down using the corner thing. it remains constant at A6 and E6.
=HYPERLINK("#INDEX('Shipping Tracker Week X'!$F$4:$F$3000,MATCH(1,('Shipping Tracker Week X'!$B$4:$B$3000=A6)*('Shipping Tracker Week X'!$D$4:$D$3000=E6),0))","Link 1")
im pretty sure the problem comes down to the hashtag and quotation marks that i added, as the formula properly increased the cell references when i drag down without the hashtag and quotations. however, i sort of need it because adding those seems to be the only way i can get the hyperlink to link me over to the other sheet and to the correct cell containing the hyperlink.
part 2 of problem: the hashtag and quotations is currently a workaround method for me. the original intention was for the hyperlink to directly link me to a website, which is referenced from a separate sheet. however, without the hashtag and quotations, i get a "Cannot open specified file" error.
View 14 Replies
View Related
Nov 5, 2008
I've got a workbook that I'm currently designing and I've just realised that I need to delete a few sheets, paste in some new template sheets and rename them. The problem is, I've already completed my summary sheets and I don't want to have to redo the values. Essentially, I just want to (temporarily) turn off auto-updating of formulas when cells / sheets are changed/moved/deleted.
For practical purposes: I need to delete the sheet named "Jan", paste in a new sheet "MonthTemp", rename "MonthTemp" to "Jan" and have all my formulas not updated (e.g. still referencing "Jan" instead of "#REF!")
View 13 Replies
View Related
Oct 24, 2008
I have attached 2 files. One of them is called Macca.xls. This is the file that was created for me by shg on an excelforum. The other isOreDep_updated.xls - this file is the updated list that I want to convert into the Macca file or make it look like the Macca file.
I awas trying to make a couple of plots, but they were not llooking like some examples I have.
In the OreDep-updated file attachment is my data I needed to use. I needed to make 2 plots:
Plot 1: Resource Grade (log scale) against the proportion of deposits (linear scale)
Plot 2:Tonnage (log scale) against proportion of deposits (linear scale)
This was done with my old data that is shown in the Macca.xls attachment.
They are meant to cumulative proportion plots. As a line or scattergram. An example of what they are meant to look like is at: http://www.nbmg.unr.edu/dox/ofr962/c11.pdf on Page 4 and 5.
shg managed to make them look like I wanted with the log normal smooth line too.
My problem now is that I want to change the data under Name, Resource Tonnes and Resource Grade (basically replace the old data in Macca.xls with the new OreDep_update.xls, to come up with same type of plots). But whenever I try to copy and paste the data into the Macca file it messes up everything. I looked at the formula that was created and I really havent a clue how to change it, without affecting everything else like the plots.
I also need to add 2 new columns for date and for reference, and it wont let me do that.
View 9 Replies
View Related
