Updating One Cell Changes Another Or Vice Versa
Dec 11, 2006
Let's say that row a,b,and c contain a list price, discount %, and discount price respectively. I want to be able to change either the discount % and it will recalculate the discount price or change the discount price and it will recalculate the discount %. So to put it more clearly:
cells in row A: Contain the List (undiscounted) price. This will never change.
cells in row B: Will be a discount %. It is equal to:
(list price - discounted price)/list price. needs to be recalculated if discounted price changes. Also, it should only contain data if the cell in Row A - list price - contains data.
All cells in row C: Will be a discount price. It is equal to:
(1-discount %)*list price. needs to be recalculated if discount % changes. Also, it should only contain data if the cell in Row A - list price - contains data.
View 9 Replies
ADVERTISEMENT
Apr 9, 2014
I am looking for a code that will be able to link cells H9:I14 on Sheet 1 with cells H7:I12 on Sheet 2 of the same workbook so that if I change H9 on Sheet 1, H7 on Sheet 2 will show the same figure and alternatively if I change H7 on Sheet 2, H9 on Sheet 1 will show the same figure. If this could work for all 12 of the cells and their equivalents respectively.
Furthermore, If a blank column or row is inserted, hence the cells move, the link will remain useable.
I have plenty of different columns throughout the workbook where this needs to be done so I imagine I can just adjust the code as necessary to incorporate different cells.
View 3 Replies
View Related
Feb 12, 2007
How can I separate a text or a number. For example in column A I have a data written like these 123text, text1234, 123text123, 123-text and in column B I only want to put the text or the number only so it means that if I have in column A "123text" in column B I only want to put "text" word. Another information is that the number is not always 3 number and the text is not always 4 character.
View 9 Replies
View Related
Aug 22, 2008
I need to have a formula or code so that when a number is entered in cell E12 or F12 or L12, or M12 would treat a positive number as a negative and a negative number entered would be a positive in that respective cell.
View 9 Replies
View Related
Jun 3, 2009
I've attached a sample/equivalent workbook of what I'm working on which will hopefully make it clear(er).
>There are two worksheets/month. Both worksheets (represent 2 different categories) are structured the same, two columns: model code & $ amount. >The model codes change (in # and actual model), between categories and month.
>The data for each month rolls up into a year-to-date summary worksheet, with 4 columns: Model (includes all models YTD, each only listed once), category1 YTD, category 2 YTD, & Total YTD).
Previously this had been done by manually entering any new models for the month into the rows in the YTD summary sheet. And the totals for each model (highlighted in yellow in the YTD tab in my sample) were just done by an adding formula, with the new month's data manually entered into each individual cell at the end of the formula (...+X). I know there's a much better way to do/automate this! (there are a lot more models than I've put in my sample aka it's way too time consuming manually).
My problem is twofold:
1. (main issue) I have been trying to do this using various IF statements nested in VLOOKUPS, and vice versa, but the issue that arises is for models in the summary sheet that don't exist in a given (month's) table. I want the value for those models (for that specific month) to be zero, but I cannot figure out how to get that to work in my formula. The only piece that works for me thus far is =VLOOKUP(A3, 'Jan Cat1'!A2:B18, 2, FALSE), but I've tried nesting it in IF statements, nesting IF statements in it, using ANDs & ORs, no avail.
I'm not even sure any of these options are the best ways to reach what I'm ultimately trying to do. A pivot table may be better? But I will need to keep/preserve the summary sheet for each month (so there cannot just be one big updated master pivot table).
2. If I could find a way to automate/refresh & update the row of models each month, it would be the sprinkles on the icing of this cupcake.
View 10 Replies
View Related
Jun 16, 2014
How do i turn column letters to numbers and vice versa
take y values from column and take x values from row
I have 'resolved' values in column A1:A10
I have 'received' values in row B11:K11
I need to fill out a table using the tables axis values stored in the column and row above.
View 3 Replies
View Related
Jul 1, 2008
I have a column of numbers such as
1001150
1001124
2224445
I need add a period in the following locations
10011.50
10011.24
22244.45
I figured this out using a format rule of
#.##
I then need to make the numbers negative so I did
-#.##
but this doesn't "stick", if I filter the numbers by negative numbers, none of them show up. So how do I make the formatting actually become the numbers? Auto Merged Post Until 24 Hrs Passes;After doing some more research I found the "precision as displayed" option. I can't find this option on Excel 2007, but I moved the files into 2003 and the option doesn't do anything. It is not permanently changing the column that I have added the formatting too.
View 4 Replies
View Related
Feb 5, 2010
Attached is the sample data worksheet. Chart 1 is XY type chart using Seconds (2nd column of sample sheet as x-axis from 42510 to 42530). How do I change it to Line chart using Time (1st column of sample sheet as the X-axis) retaining same data from 42510 to 42530 on both primary and secondary axis?. And how do I again change it back to XY chart?
View 3 Replies
View Related
Apr 24, 2009
In column F I have values (eighter 4 or -4)
Is it possible to use countif to count how many time there is s a switch from 4 to -4 and visa versa.
View 9 Replies
View Related
Feb 24, 2009
I am trying to trigger a Sub if the value of a cell change. I came up with this which work fine when I type in a new value BUT if the cell is a formula and the value change without editing the formula then nothing happens
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("L14").Address Then
Selection.AutoFilter Field:=3, Criteria1:=Target.Value
End If
End Sub
View 9 Replies
View Related
Mar 10, 2009
I'm using Excel 2007 to keep a database of daily settlements for financial futures as well as a variety of studies for these daily values. I want to have one page that I can print every morning with the relevent information for the day. So what i'm trying to figure out is how to make the cell on my "Settlements" page always read the last cell of my data column or find the average of the last five cells, for example.
View 9 Replies
View Related
Sep 3, 2008
I am having trouble writing a code for something I need to do. There is a workbook with a worksheet for each month. At the start of every month a new worksheet is added. The opening data in this new worksheet is coming from the closing data of previous month, and some of this data is modified as the month passes.
So, in a way, some of the data for each month depends on the data of previous months. If there was an error in one of the cells, for say, January, and it was corrected manually, it will affect the cell values for feb, march, and so on. Currently, its all a manual process - from copying previous month's data to a new worksheet, and manually correcting errors in each worksheet, which is error -prone and a time consuming process. Is there any way to link each month's data and create an "update values" button clicking on which will update the values for cells in every month's worksheet following the one which had errors in it and was manually updated. It has to one directional.
View 9 Replies
View Related
Dec 11, 2009
I have two sheets in my workbook. One named singledump and the other individualfileselect.
Within single dump i have daily data loaded with certain cells containing comments added for that day of the week.
E.g. Tuesday 3000
Below average clearances, we will look in to this further throughout the week.
What i would like to do is have either a text box area of just simply one cell updating with a the first comment from the week and to then have two buttons back and forward allowing the user to click and the cells will update for the next comment that week or go back to the comment before.
How would i be best going about this? To be honest i would prefer to keep away from text boxes and would be happy with just the two buttons scrolling through specified ranges to update a cell on individualfileselect sheet.
View 6 Replies
View Related
Jun 19, 2014
I'm using this to update a range of cells after an automatic copy and paste procedure;
Code:
Sub UpdateBtoW()
On Error GoTo HandleError
Application.Calculation = xlCalculationManual
Sheet74.Activate
Dim cell As Range
For Each cell In Range("B1:B50000")
If Not IsEmpty(cell.Value) Then
If cell.Offset(0, 14).Value = "" Then
[code].....
What it is supposed to do is look in column B and find any non-blank cells. If it finds one, it should check the following and update column W as necessary;
1) Column B shows 1, column P is not empty, column W is empty - UPDATE COLUMN W WITH 'Letter 1'
2) Column B shows 2, column P is not empty, column W is empty - UPDATE COLUMN W WITH 'Letter 2'
3) Column B shows any value, (not blank), column P is empty, column W is empty - UPDATE COLUMN W WITH 'N/A'
The issue is that it is updating the cells as required, but it then goes on to fill the entire sheet with 44819 in every single cell.
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 14, 2012
I am using the Format as Table feature in 2010 and I am summing a range of cells (C2, D2 and E2) with the result in F2. I have linked F2 three rows below the table in cell C5.
When I add new data in the 3rd row in the table feature I can get an updated result in F3 but my linked cell does not update as it is now pushed to cell C6.
How can I have the linked cell update with the new total from cell F3 as it moves relative to the table?
View 1 Replies
View Related
Jun 4, 2013
I have in cell A2 a number, and my VBA is as follows;
If Target.Address(0, 0) = "A2" Then
Application.EnableEvents = False
Range("a" & Rows.Count).End(xlUp)(2).Value = Target.Value
Application.EnableEvents = True
End If
This basically adds any new number typed into cell A2 into a list which starts in A3 then continues down through column A.
I also have another part of my VBA which says;
If Target.Column = 1 Then
Application.EnableEvents = False
Cells(Target.Row, 2).Value = Date + Time
Application.EnableEvents = True
End If
This part works fine and adds a date stamp to my blank cells in column B whenever I manually type anything into column A, however, when the first part of the VBA works the date stamp is updated into cell B2 and I want it to update next to the new entry that has just been added into column A by the first VBA doing its job.
I think I need to change;
Cells(Target.Row, 2)
To something that refers to a Range of cells (would be B3-B5000 for example) but my knowledge on how to change that part of the VBA has now ran out!!
View 5 Replies
View Related
Oct 27, 2013
I'm currently using Excel 2007 to get information from closed workbooks and updating my file.
First of all I create a table on selected range through "Insert table" command having something like this:
Then I have I piece of VBA code that tries to update every single cell of a column in this case it's updating %LD10 column:
But like you can see in first capture, the code updates every single cell with last value of variable fichero, having, at the end, the same value in whole column.
View 4 Replies
View Related
May 6, 2007
Cell A1 value is: www.yahoo.com B1 Value should automatically show "Possible"
Cell A1 value is: www.icallindia.net B1 Value should automatically show "Possible"
Cell A1 value is: www.jigarparekh.html B1 Value should automatically show "notvalid"
I need a formula which can automatically see the status of the website address and updated in B column. which means that one dose not need to go to the website page to see if it is correct or not.
I have lot of website in the one sheet and i don't want to go all this website and check if they are correct. I want the status of this website in b column once when i have the website name already in the A column.
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
May 8, 2014
I need to record time next to eg.: W3 cell,
Now I'm using below formula :
=IF(W3<>"",NOW(),"")
but the NOW function keep updating time itself, is there any VBA code or function that I can use with this formula ?
View 4 Replies
View Related
Jul 9, 2014
As i am working on consolidating the Missing numbers manually, whether will it possible for making this automatic.
In Column "A" I have Multiple Duplicate Invoice Numbers and followed by the Different Transcation numbers in Column "B".
In Column "D" I have Each Invoice Numbers and in Column "E" Total Transaction Numbers i.e., no. of times each Invoice have Transactions.
In Column "F" No. of times we received the Transactions.
Here i need to manually need to update each Invoice's Missing Transaction number in Column "H" in single cell with comma separated to each Missing Transaction numbers.
Example:
Before : (Column "A" & "B")
Invoice NumbersTransaction NumbersInvoice NumbersTotal Transaction NumbersReceived Transaction Numbers
A2014000351501 A201400035150 3 2
A2014000351502 A201400035494 13 12
A2014000354941 A201400035881 12 11
A2014000354942 A201400035884 19 17
A2014000354943 A201400035888 14 13
A2014000354944 A201400037613 5 1
A2014000354945
[Code]...
Instead of doing this manually can we have the VBA to auto Update these missing numbers.
Here by i am attaching sample file.
View 1 Replies
View Related
Nov 21, 2009
i am trying to figure out, how to automatically update time, based on specific city's time zone? Lets say, if the city is dallas, time zone is Central GMT-6. In the next cell, the time shows up as Dallas's local time, instead of local computer time. And this time updates itself after every 2 min.
2ndly, if that city's time is later than 8:59 PM local time, that specific city's cell grays out.
Attach: Snapshot of sheet
View 14 Replies
View Related
Feb 21, 2014
I need it to be like:
=B10 2014
(So the first part changes depending on what B10 is but the 2014 always stays)?
View 9 Replies
View Related
May 22, 2014
I am trying to read a value in cell A1 and make say cell B1 read the same. The data in cell A1 is a real time percentage value that gets updated every lets say 10 secs. Every time the data is updated the new value goes into the next cell A2, A3, A4..... and so on. Every time it gets updated (i.e into A1,A2,A3,A4.....and so on), I want to read the new value each time in B1.
View 9 Replies
View Related
Dec 16, 2009
I feel like I am losing my mind here. I have a basic SUM calculating a bunch of cells. I have done the SUM from th autosum feature as well as, just typing it is (=SUM(B1:b16)), It totals up the cells correctly, but, when I change a value in one of the cells, the sum is not updating? I have never seen this happen...Does anyone know what could be causing this?
View 3 Replies
View Related
Mar 18, 2009
In my Excel 2007 I have downloaded an Add-in (Webiress data) which allows me to receive RTD of a particular security which I then use to plot a chart. In a day there are around 15,000 changes in prices of that particular security.
The problem is that the RTD misses many prices when the market is fast and also it frequently (around 20 times per day) stops updating and then I have to clear the cell where the RTD is updated and it returns to normal, updating the prices for few minutes and then stop again.
A friend of mine told me that I have to change one Registry in Excel to tell Excel to check for a RTD every 2 miliseconds. I found this suggestion a bit strange and I have no idea how to do it.
Has anyone ever come across a RTD issue like this?
I contacted the server provided (webiress) and they said there is no problem with the updating of RTD (real time data) and the problem should be on my side.
View 9 Replies
View Related
Nov 7, 2007
Is there a way to insert a cell/row, and add data to one page/tab, and have it automatically insert a cell/row and add the data on another page/tab?
Excel 2003 is the version I am using.
View 14 Replies
View Related
May 23, 2008
I update graphs/charts everyday. I've been looking at ways of updating a lot of charts in 1 action.....
What would the formula be to have the range of the chart (ie =Data!$BS$1188:$BS$1201) to get the row numbers, not the row, from numbers entered elsewhere on a data sheet (ie =Data!$BS$1188:$BS$1201)
As a lot of the time I use the same number range but on different rows and sheets.
View 12 Replies
View Related
