Put Manually Formula In Specific Cell?
Jun 5, 2014
I would like to be able to put manually a formula in a specific cell (like a "requested date" cell) and then have a table that use this date for the headers and calculate +1 year for each columns.
For exemple:
=2012-01-01
=2013-01-01
=2014-01-01
=2015-01-01
requested date:
2012-01-01
Would you know a way to easily do it? when I try to do it manually (+365) it doesn't consider leap years. I thought about a =DATE formula but the requested date have to be manually entered...
View 3 Replies
ADVERTISEMENT
Jul 16, 2014
I need to use the Worksheet Change event in a particular sheet in a specified column which works fine if the data is already there in the sheet and then changed however,the data is in this sheet is actually a Sub-Set of a Main sheet i.e certain filtered records are being copied from Main Sheet and then copied to this IBSL Sheet.
After the data is copied I have to check each record manually and then categorize each record as Fresh , Rebooked , Cancelled , Tranch or On-Hold.....These 4 criterias are added in the Column 38 and the same thing has to be repeated in the column 40 , so when i change the data in the column 38 the same category has to be updated in the same row in the column 40..
But the problem is that the data is first copied from the Main Data Sheet into the IBSL Sheet using a Macro so then this even t gets fired and goes in the DEBUG MODE...
I need this to happen when i change the category manually..I am adding data validation at the same time while copying the data in to the TEMP sheet.
So what can be done to achieve..
View 7 Replies
View Related
Jun 9, 2009
I'd like to be able to add 10% to column D and have the prices in A, B, C change accordingly. Is there a formula for this?
A B C D
2009 Distributor CASE Price2010 Distributor CASE Price2010 Distributor EACH PriceVariance from 2009 price135.00148.000.0592108.25100.000.040062.8875.006.250066.6096.008.0000
PS: Right now I have it set up working the opposite way, the prices are entered and my formula tells the user by what % the price has changed. The user wants to be able to tell the formula what % is desired and have the prices change accordingly.
The formula I am using for the way column currently calculates is:
=IF((G2=0),"",(((G2-F2)/F2)*1))
View 9 Replies
View Related
Aug 14, 2009
let say in cell A6 i have a list
SnG
Cash
MTT
I can pick one of these. If i pick Cash then i want a specific cell to run a specific formula. depending on the item chosen.
So in cell A6 is the list.
in cell B6 an amount will be place and in C6 another amount will be place. the number for c6 will range from a -number to a +number.
But D6 looks to see if B6 meet the requirements for MTT, SnG or Cash depending on which item was selected from the list. and uses the formula below with $c$3 changing from to either $D$3 or $F$3 and comparing the values for the chosen item. the formula changes would look this
=IF(ISBLANK(C6),IF(B6>$C$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))
=IF(ISBLANK(C6),IF(B6>$D$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))
=IF(ISBLANK(C6),IF(B6>$F$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))
View 12 Replies
View Related
Mar 29, 2014
I have a spreadsheet with 6 hidden columns (B to G). Rows 1 to 14 are frozen and have data that comes from another work book and these rows will be protected. The user will enter data from H14 to U14 and then H15 to U15 for the next row and so on down the sheet. The number of rows of data over a week is variable, a minimum of 21 rows (3 per day) but no maximum.
When the user starts entering new data in the column range H to U I would like to automatically populate the 6 hidden cells on that row with information from particular cells in protected rows 1 to 14. For example B14 would populate with the value from I4, C14 would populate with the value from I5, D14 from I6, E14 from I7 and so on whenever someone enters a value anywhere from H14 through to U14.
I can understand that a worksheet change event would be useful to do this but I guess then it wouldn't want to run every time each cell is populated so I think if it had to activate on a single cell change then the cell in column L would be best. Also, as I understand it, there can be only one block of code per sheet that operates on a worksheet change event, have I got that right? If that is the case then I assume all the code to populate the hidden cells on each row will need to be in this block of code.
View 8 Replies
View Related
Nov 24, 2013
let's say cell A1 is colored blue manually, how can i format cell b1 to be colored red? is it possible in conditional formatting?
View 7 Replies
View Related
Aug 22, 2009
I have an array, let’s say B1:D20. I would like to be able to manually select any cell within this array and then run a macro which will place the contents of the selected cell into the cell in column A in the same row as my selection.
View 3 Replies
View Related
Dec 3, 2011
I have the below code that changes the colour of a cell to green if the figure increases, and to red if it decreases.
The issue is that it works fine if you manually change the figure in A1 or A2 by entering the figure and hitting enter. If the cell figure changes in A1 by any other means such as RTD or say a value in C1 "=C1" then the colour change is not triggered.
Code:
Public oldval, myval
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A2")) Is Nothing Then
oldval = myval
If Target.Value > oldval Then
Range("A" & Target.Row).Interior.ColorIndex = 4
[Code] .......
View 9 Replies
View Related
Jan 3, 2014
How can I get a cell to change color if a user were to modify or change the value of the cell contents?
View 4 Replies
View Related
Jan 6, 2013
I have a vba script that matches a date on two sheets then copies a formula based on the location of the date, the problem I am having is how can I refer to the activecell and active column without typing them manually as the range will change each day. See vba script below:
VB:
Sub find_date()
Dim FindString As String
Dim Rng As Range
[Code]......
View 2 Replies
View Related
May 27, 2014
I have been using Excel for several years and I'm fairly proficient at its basic functionality. I've recently learned and made extensive use of pivot tables. However, I have not taken advantage of such features as Data Validation, VBA, etc.
View 3 Replies
View Related
Nov 13, 2008
I have a range: AC2:AG1400 that are filled with numbers....
Someone manually highlighted, in bright yellow, some of the numbers.
I need a macro that could reverse the signs of only those that are coloured in that yellow.
View 9 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
Oct 25, 2012
VBA macro to change the color of a cell automatically for a specific period of time -say 5 minutes, based on the value the subject cell holds at that time. The cell value is not manually entered but comes from a sub.
There will be hundreds of such cells so that the macro must be able to be repeated for other cells utilizing their individual cell values as well.
View 9 Replies
View Related
Jun 22, 2014
I have a cell in my spreadsheet that I use to control the size of a column. I formatted the cell as a number and added data validation to make sure the number is greater than zero. Then I realized that I need to also allow for the option of auto sizing the column as well. This would require a data validation list type with something like True and False for the choices.
What I want to be able to do is have it both ways. I want a drop down list in that cell with the options Auto Size or Custom. If Auto Size is selected the cell value gets set to that. If Custom is selected I then want to be able to type in a number.
ddl.png
[URL]
Is there a way to setup a cell such that the value is either an item from the drop down list or, entered directly (depending on the list selection)?
I know I can do it with dependent lists but that would require two cells, one for the auto size mode (true/false) and the other for manual width value.
View 1 Replies
View Related
Apr 12, 2013
I have a column of dates that are not being recognized as dates unless if I manually select each cell and press enter. For example the cell value is "Jul/13' and isn't recognized as "01/06/2013" until I select it and hit enter. How can I get around this?
View 8 Replies
View Related
Dec 21, 2013
I paste new data into a sheet of a monthly report I prepare. For this sheet, the # of data rows change (and is unpredictable) every month. I need the value inside a specific cell that dynamcially moves up and down based on the # of rows for that month (because it's below the rows of data).
So I made a formula to identify the exact cell # every month.
Example:
This month the exact cell is F255 in the "Refi" sheet.
So my formula in the "Summary" sheet cell A1 first finds the cell row # only (255) and since it's always column F, in B1 I have
VB:
="F" & (A1)
This outputs "F255" in B1, successfully identifying the target cell.
Now how do I write a formula in C1 to grab the value from whatever cell is named in B1. (For this month, the value in cell F255 from the "Refi" sheet)
View 7 Replies
View Related
Feb 5, 2014
I have a number of worksheets and need to move data from the same cells of each work sheet to a summary worksheet. I manually entered the cell id's for the first sheet.
Example =Jan!M6, =Jan!N7, =Jan!O9 etc.
I know I should be able to automatically enter these same cells for the remainder of my worksheets with out manually going in and typing each one (there are a lot of entries.) I just can not remember how to do it. The next sheet is named "Feb" and "Mar" etc.
View 5 Replies
View Related
Feb 17, 2014
Can I get a formula for the below specification.
if a cell contains character A THEN multiply by cell B * 0.02,if a cell contains character B THEN multiply by cell B * 0.05,if a cell contains character C THEN multiply by cell B * 0.010
View 1 Replies
View Related
Apr 25, 2007
I have a problem to solve
I have 2 worksheets named 'Products' and 'Order'
At the 'Order' i use 3 cells
Code
Pallets
Cases
When i entered a code to the cell 'code', it reads the code from the sheet 'Products' and then transfers to the active sheet 'Order' some items.
What i want is:
Because the user has to enter the quantity either to the cell 'Pallets' or
'Cases' i want to be locked the empty cell each time and to be used for calculations.
1st Eg.
The "45870"
Pallets to order : 50
because the user used the pallets the cell "Cases" must be locked and it must calculate the 50 pallets*100 so
Cases : 5000
2nd Eg.
The "45870"
Pallets to order : null
Cases : 5000
because the user didnt use any pallets the cell "Pallets" must be locked and it must calculate the 5000 cases/100 so
Pallets to order : 50
View 9 Replies
View Related
Aug 24, 2009
I have a spreadsheet that I am using to track competitor retail pricing
The problem is that I have a list of values and I want to be able to show how many of these values are either higher, lower or the same to a specific cell value. However one complexity that I have is that some of the competitors do not have values for all records (i.e. some are blank) and I want to discount these from the counting.
View 12 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
Dec 2, 2011
I have data in several cells, and I want to extract only the information in the cell up to a /. For example "joesmith/shipping" I would like to have only joesmith in the cell next to it.
View 2 Replies
View Related
Mar 13, 2009
I was wondering if there is a formula that will search a cell for a word or other specific criteria then if it finds it, puts the requested data / word in the formula cell
Example:
If I have a list of vehicle descriptions all in different formats:
CAR1 1.4 SRI 3 door Hatch
CAR2 5 door saloon GSI 2.0 V8
2.2 CDX 5 door CAR3 Estate
CAR3 Estate 5 Door CDX
Say I want to know which ones are CDX varient I need the formula to look in the cell and return "CDX" or "YES"
View 4 Replies
View Related
Apr 6, 2012
I am looking for a formula that will enable me to determine which one out of five thresholds a percentage falls within and the retuns a specific value for the corresponding threshold. Here is an example of the data (only showing three thresholds):
_____A________B_________C____
1 ___0.0%_____50%_____$5.00
2 ___51%______75%_____$15.00
3 ___76%_____100% ____$25.00
Where the values in A1 and B1 are the low and high ends of one range. I would like the formula to determine which range the value falls within and return the value in collumn C (C1 for the A1:B1 range).
I can do this using multiple 'if' statements, but am looking for a much more streamlined way to determine the proper value in column C.
View 2 Replies
View Related
Oct 24, 2013
I am looking for a formula which will allow me to return a value, 1 or True, if any of a number of words or phrases in a list exists in a specific cell. Per the below example, any formula which will achieve the result in column A
For Example:
Sheet 1
Column A
Apples
Oranges
Banana
Fruit Salad
Fruit Smoothie
[code].....
View 6 Replies
View Related
May 20, 2014
I need a formula to extract data after "<li>Color: " & before"</li>"
A
B
1
ORIGINAL
RESULTS
2
blah blah blah<li>Color: White</li>some informations
White
3
blah blah blah<li>Color: Red</li>some informations
Red
4
blah blah blah<li>Color: Black</li>some informations
Black
5
blah blah blah<li>Color: Red</li>some informations
Red
I have already use this formula but I get the "#VALUE!" error even if the cell contains the "<li>Color: </li>" data.
[Code] ....
Sometimes, some cells does not contain the "<li>Color: </li>" data, I don't want the "#VALUE!" error, I just want a blank cell there.
This is an example with the real values I want to extract Example.xlsx
This is the formula that works
[Code] ......
View 8 Replies
View Related
Mar 6, 2007
formula that can automatically use the current month date to extract information from other cells on my spreadsheet.
For example: This formula should be located in cell C6. Cells D6, F6, H6, J6, L6, N6, P6, R6, T6, V6, X6, and Z6 (these cells correspond to the months of the year, January to December respectively) contain the values I need. The month names are labeled above in row five.
Let's say the current month is March, I need the formula in C6 to automatically know that it is the month of March and to pull the information from cell H6 (which is the March cell). Then when April comes along, it will know that it is April and to only pull the information from cell J6; and so on until the end of the year.
View 3 Replies
View Related
Nov 23, 2009
See attached worksheet for reference. Is it possible (while utilizing the same spreadsheet on a weekly basis) to zero a spreadsheet subsequent to its use. Importantly however, all relevant formulas must remain perfectly intact and will re-establish themselves once relevant data is placed inside an individual cell? In this case, as soon as a “Name” (or even a letter) is referenced inside the “Name” column: H10:H19?
In other words, the entire sheet is blank bar the top date and respective headings. Once any text is placed inside cells H10:H19, the formulas from the associated Row re-applies itself to the “Week-Start” dates, “Week-End” dates and references a default “Phone” amount for ‘$10’? The Data Validation formulas I’m sure would remain undamaged? This would prevent ‘text clutter’ (such as dates extending to the bottom with no apparent referencing or connecting information?
View 2 Replies
View Related
Apr 4, 2013
I have a sheet with a country in it in A1 (validation list).
Depending on the country in A1 a country specific IF command has to be executed on a column1 with first cel = A3. This IF command also relies on the values in column 2 and 3 on the same row. (B3 and C3 are in the IF cmd)
I taught to do it like this : =IF($A$1="Spain";$A$5;0) with A5 being the country specific IF command which should be executed when spain is selected. however when I do this, this only works for the first cell ( I cannot drag this formule down, since it will always give the value calculated in column 2 and 3 on row1.
I'm not sure if the best way to 'select' the country specific IF cmd is with another IF command..
View 5 Replies
View Related
