Update A Formula Based On A Newly Inputed Number?
Oct 10, 2008
What i would like to happen is after inputing a number into say column B have that number update a formula in cell D2.... so for example... lets say i have
B1 = 1000
B2 = 2000
B3 = 1400
B4 = ???
D2 = (B3-B2)/B2
is there a way to make it so when i enter a new number in B4 it will automatically update the formula to display the value for (B4-B3)/B3 and then continue on to repeat this process when i enter values into B5, B6, B7 ect?
View 4 Replies
ADVERTISEMENT
May 20, 2008
I want to have a variable range.
ie. This value M4 needs to change based on the week.
Range("M4").Select
If it's week 1, then it can be say M4, but week two will need data to go to N4.
I have found out how to get the week number from the user. ie.
' Select Week Number
Dim NumSheets As Integer
Dim Prompt As String
Dim Caption As String
Dim DefValue As Integer
Prompt = "What week do you want to get data for?"
Caption = "Week Number"
DefValue = 1
NumSheets = Val(InputBox(Prompt, Caption, DefValue))
If NumSheets > 52 Then MsgBox "Week Number too high"
If NumSheets < 1 Then MsgBox "Week Number too low"
I tried then linking this value saved as NumSheets by:
Dim Rng As Range
If NumSheets = 1 Then Rng = Range("D3")
If NumSheets = 2 Then Rng = Range("E3")
View 3 Replies
View Related
Feb 13, 2007
This is a lottery challenge that I am facing at work. 6 numbers are chosen from 45 on a weekly basis. Using about 20 years worth of information, I have code that provides a dynamic frequency list in descending order.
I'd like to now have the user pick a number from the above list and see what other numbers have come up with these frequencies. I guess ultimately I'd like 2 or 3 "favorites" with the corresponding frequencies. Trouble is I've been going around in circles with nothing working
View 2 Replies
View Related
Dec 11, 2008
I have a basic formula =C17+'Asset Depreciation 2008 Onwards'!C24, and I want to copy it down just using the drag function. Problem is that the second reference range of cells are in rows and hence when I copy it down it doesn’t automatically update the cell references because it want to update them by column number instead of row number. IE I want it to display =C17+'Asset Depreciation 2008 Onwards'!
D24, instead of C25. Do you know if there is any way of telling Excel that I want it to increase the column number by 1 every time, instead of the row number for this part of the formula?
View 5 Replies
View Related
Apr 11, 2014
Here attached is my sample workbook:
Attachment 310920
My button inserts a new row into the table.
What I want is that everytime a new blank row is created, the formula in the Days in Situ column is there as well (but obviously the cells update depending with which ever row it's in.. eg below formula is row 10.).
View 4 Replies
View Related
Dec 8, 2012
I have 2 excel files A & B. In cell A1 of file B, I use an index formula to refer to a row in file A. "INDEX('[A.xlsb]A'!$10:$10,1,2)" is the formula used (referring row 10 in file A).
If I update the row number in another cell of file B, is it possible for this formula to refer to that cell to get the row number from file A?
View 1 Replies
View Related
Apr 30, 2009
I have uploaded a sample amortization schedule.
1. I require the table to adjust itself based on the loan period and number of payments per year entered in D14 and D15 respectively.
2. Also, if a value is entered in column E, then i require the whole table to update as well.
View 7 Replies
View Related
Mar 28, 2014
I have a column C with different text in cells (item's title). Column D - relevant description for each of the items. 100+ rows.
Now, unfortunately, often a spreadsheet with items is updated with many new items. So I get a new spreadsheet with old and new items mixed. I need, somehow, to import descriptions of the old items (Column D of the old spreadsheet) to the new spreadsheet from old spreadsheet. So I want excel to look for old items in column A of the new spreadsheet and, once found, insert a description in the column B from old spreadsheet.
See attachment : Example for forum.xlsx
View 3 Replies
View Related
Mar 14, 2014
I need a formula that will calucalte the monthly total based on the following conditions:
Col A = Yearly Cost
Col B = The number of the month when the costs are starting (1=Jan etc)
Col C = The duration or the number of months for which the costs are to spread
Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.
Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx
View 3 Replies
View Related
Dec 29, 2008
X-Axis = Week1:Week13
Y-Axis =Revenue for each week.
I have a line graph. If "Week9:Week13" have no value, my graphs line drops to 0. How do I make my line graph show no value when no value is inputed for "Week9:Week13"? I think I need to incorporate the =NA() in the original formula but I'm not sure how to do this.
View 9 Replies
View Related
May 29, 2009
was looking if you could change a row of cells background colour if certain word is inputted elsewhere. in b2:f2 is data- was looking if, could these cells background colour change when yes is entered in G2? does not matter which is used, VBA or con formatting just cant work it out.
View 9 Replies
View Related
May 15, 2009
i am going to be making other columns with data. i want it to highlight the cell from the new columns if the value is higher than the standard.
not quite sure how to do this? do i make an if statement and put it in the cell, then go and input my data over it?
View 3 Replies
View Related
Mar 5, 2009
I have a userform which has a textbox for employee id. If an employee enters less than 5 digits, it should prompt the user via msg box that he needs to enter 5 digits for employee id. However my coding doesnt work...can someone help me correct it?
View 6 Replies
View Related
Jul 28, 2014
I am creating a fantasy football draft board using excel. To keep it short and sweet, each individual team (one per column) has a $200 budget to draft players. Each team must fill 14 roster positions (one per row), but here's the catch; you must spend at least $1 on all of the 14 players on your roster. So you have a maximum bid amount which is your remaining budget subtracted by $1 for every remaining spot you have left to fill.
I have already created a "remaining budget" field which subtracts each drafted player's inserted dollar amounts from the $200... easy. Beneath this, I'd like to create another cell per team that tells you your "maximum bid," which is essentially 200, -1 for every cell that remains blank. I have played around with the conditional IF formulas but cannot seem to have it account for multiple cells.
As a workaround, I have made columns hidden beneath the board which contain the conditional IF, ISBLANK fields which are -1 if true and 0 if false. Then creating the "remaining budget" field that subtracts these values from 200. This works, but it's not perfect, as when you have multiple players you still need to draft and you've exhausted your budget only saving $1 for each, it overstates your "maximum bid" by $1, or when you have $X left and you need to only fill one more spot, it ill tell you your "maximum bid" is $X-1.
View 5 Replies
View Related
Aug 10, 2014
I am looking to get a formula based on my spreadsheet attached
I want F collumn to add 5 days onto whatever date you put in there then correspond it to the matched date period in I2-I6 then apply the pay period from H2-H6 and put it in the G Collumn next to the date that has been input?
View 1 Replies
View Related
Jan 14, 2014
Is there any formula that could calculate total average number of recruitment days (i'ts one number), but only for "Diana" and "Skipper" (excluding "Jennifer")?
Please see an example:
Recruitment daysResponsible
12Diana
13Diana
19Diana
16Skipper
23Jennifer
[Code] .....
I guess it has something to do with AVG and IF formulas.
View 2 Replies
View Related
Dec 6, 2012
I'm looking to conditional format a cell/cells based on whether a formula result returns a whole number or not.
I don't want to include the formula in the sheet itself, just have that as the formula in the condition.
The formula will be along the lines of:
=IF(SUM(BB10/BA10)"a whole number",TRUE,FALSE)
My problem is, is that I don't know who to refer to "a whole number" in Excel formula language.
View 4 Replies
View Related
May 14, 2014
I have a form in work which acts as a tick sheet for tasks complete on a construction site. When a task is complete I copy & paste from a key of dates i.e. week ending 11.05.15, this allows me to see what week a task was complete.
Generally I only marked off tasks which were 100% complete but my manager wants me to enter the % of the task complete also, i.e. 80% in the box.
So my question is how can I have the formula assume that any box with a date & shading is 100% and any cell with a number i.e. 80 is only 80% complete. I need it to monitor around 150 cells and give me the overall % complete based on what i have discussed.
View 5 Replies
View Related
Jul 8, 2009
I need to show the size of a project in a cell based on it's number of hours, e.g.
Between 5-80 hours = SMALL
Between 80-200 hours = MEDIUM
Over 200 hours = LARGE
How can I do this? I can't get the IF function to work like this (unless I'm doing something wrong) and I thought about using a pivot table but think there must be an easy formula to use?
View 3 Replies
View Related
Mar 13, 2014
I'm using the VBA code below for a piece of code.
[Code] .....
However, I want to use this same function in another place, without the limit set on the cells that will be cleared.
What I would like to do is send the formula the cell to start at (E14), offset that by one column, and then send a number of rows. With that, create the range to be cleared.
Something like:
[Code] .....
View 3 Replies
View Related
Sep 14, 2005
The following are a few of generally accepted values: ...
View 8 Replies
View Related
Jan 25, 2013
I am wondering if I could have a formula be used against a certain row/cell containing a certain "phrase" or "number" for instance
I want E1 to read something like this = (row containing item "FF32105") (the given row from the previous statement) ( the input column ex. B) (the input value ex. *6)
Essential I want to be able to copy and past a sales forecast and have the formulas automatically (listed below the pasted forecast) calculate purchase needs. The issue is that if our forecast one month has a certain item and then the next does not then they will end up on different lines after the copy/paste. We have over 500 items so a manual adjustment would be time consuming..
Is there a possible way for the formulas to "find" the correct line to start calculating data?
View 6 Replies
View Related
Jun 22, 2009
Following is a summarized example of my data and what I am trying to accomplish.
[Column A] contains a list of account numbers. [Column B] contains current balances, [column C] contains balances from one month ago, [column D] contains balances from two months ago. Within the same spreadsheet I want the ability to type in the account number in one cell and then the column number in another cell. For example, If I type in the account number 1234 and the column number 3, I would get the balance from [column c]...if I typed in the column number 4, I would get the balance from [column D].
My first thought was to use a simple SUMIF formula that would compare the account number I type with the account numbers found in [column A]. The problem is getting a formula that can translate the number 3 to [column C] or the number 4 to [column D]. Note: the actual spreadsheet I am using extends out to column BI.
This is simuilar to the Column Index Number used in a VLOOKUP formula.
View 9 Replies
View Related
Feb 18, 2014
I have the following data:
Column A = Date
Column B = Reservations made per day
For ex:
A B
1 3/1/2011 5
2 4/5/2011 10
3 3/8/2011 15
Then I have a look up table where based on the date ranges it assigns a week number.
WeekDATE Range 1Date Range 2
718-Feb-1124-Feb-11
825-Feb-1103-Mar-11
904-Mar-1110-Mar-11
1011-Mar-1117-Mar-11
1118-Mar-1124-Mar-11
1225-Mar-1131-Mar-11
1301-Apr-1107-Apr-11
1408-Apr-1114-Apr-11
1515-Apr-1121-Apr-11
1622-Apr-1128-Apr-11
I am looking for a fomula that would assign a week to the corresponding dates on column A and tha would then add all of the reservations booked for each week.
View 11 Replies
View Related
Jun 24, 2009
I'm trying to create a formula in cell f13 of my attached spreadsheet "Sample 1" that will search the 2nd attached spreadsheet "Sample 2" and return the correct serial number based on both the matching PO # (located in cell E10 on Sample Sheet 1 and in Column 5 on Sample Sheet 2) and Product # (cell A13 on my Sample Sheet 1). My current formula is not returning the correct result and I'm not sure why.
View 4 Replies
View Related
Jul 21, 2009
I originally posted this in the "Excel New Users forum" - i guess that was an error, but I'm very new (second post) and very new to VBA in general - so please be gentle!!
I've created a macro which opens a workbook, creates and renames a new worksheet, and moves it to the end of the workbook.
I then need to paste into this new worksheet a selection from another workbook.
How would I specify in the code that the selection needs to be pasted into the newly created tab?
View 12 Replies
View Related
Jul 31, 2009
I'm needing a formula that will determine the number of days that fall in a specific month based on a date range. For example, if I have a date range of 10/15/2009 to 01/13/2009, I need the formula to determine the number of days in each month within the range (October has 15 days in the date range; November has 30, December has 31, and January has 13.) I have a large spreadsheet that would be so much easier to manage with such a formula. Currently, my spreadsheet is setup as follows. I need the forumla automatically fill in the number of days under each month.
Stard Date End Date Oct-09 Nov-09 Jan-10 Feb-10
10/15/2009 01/13/2009
I'm using Excel 2007.
View 9 Replies
View Related
Jul 21, 2009
I've created a macro which opens a workbook, creates and renames a new worksheet, and moves it to the end of the workbook.
I then need to paste into this new worksheet a selection from another workbook.
How would I specify in the code that the selection needs to be pasted into the newly created tab?
The code I have so far is as follows:
Worksheets.Add.Name = Workbooks("SHEET1").Worksheets("monthly report").[p1]
ActiveSheet.Move _
After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
Windows("SHEET1.xls").Activate
Range("A1:N23").Select
Selection.Copy
However, I got a subscript out of range error pointing at the line of
Worksheets.Add.Name = .Worksheets("monthly report").[p1]
View 9 Replies
View Related
Aug 22, 2009
I am new to VBA and was just wondering if there is logic behind this or am I doing something wrong....
ie,
1 Cells(1,1).Select
2 Activecell.Copy
3 Cells(2,1).Select
4 Activecell.Paste
The above doesn't work but if I use the same above 3 lines with
4 Activesheet.Paste
it does work
What is logic here as to why it can't paste into a newly selected 'activecell'?
View 9 Replies
View Related
Aug 31, 2007
I'm wondering if there's a way to automatically create a worksheet based on a new day. I have a workbook with a worksheet for each day that I'm working on a job. I have to copy the last day's worksheet and rename it to the next day, for example, "Day 1" becomes "Day 2". What I"m wanting to accomplish is to have a new worksheet automatically created at 12am each day that I'm on the job, and the worksheet name increment for the next day. I guess it would need to constantly monitor the computer clock for 12am.
Also, some of the functions are dependant on the previous day's values, so they would have to update as well. I'm assuming I'd need some VBA code to do this, and I'm ok with visual basic. Is there a way to do this that isn't too complicated?
View 3 Replies
View Related
