Not sure if I can do this with a formula or not. looking to change the value of a cell when using a column. Im looking to have the cell to change each time after there is a new value entered in the column but only when a value is entered in new cell of the column.
View 5 Replies
In tab 1, I am trying get column S to change to what any time date in column F, G, H, J, L, N, Q or R get changed to-
View 9 Replies View RelatedI need to have the color of cell A2 change when I enter an asterisk in cell A1 to denote a critical dimension on an inspection form.
I used conditional formatting to change cell A1 orange when an asterisk is entered. Is there a way to tell cell A2 to look and see if there is an asterisk in A1 and if so change it's color?
I need a script that changes a cell to say closed (uses a drop box) when a date is entered into the date column. I have attached a workbook with the basic options, obviously there will be a lot more columns and information in the original workbook and I will adding the code to at least 2 worksheets.
However to give a basic understanding if I have a drop box in column A with the options Active, Inactive and Closed. When I enter a date in Column B (headed Closed Date) I want Column A to change the option to Closed automatically.
I want to incoroprate into my Workbook a sheet with a list of phone numbers, that sheet can be added to and more numbers entered if necessary and will be called "DATA"
The questions is this. If one of those numbers in the "DATA" sheet is entered in any other sheet in the workbook can the cell that number has been written on (not the one on the data sheet) be made to change colour?
I am using Excel 2003 at work.
New cases are entered on to the spreadsheet. The case remains open until a closure date is entered, at which point the case is closed. What I need is the following:
Cell A3 = should say "Open" if there is no value in cell Y3
Cell A3 = should say "Closed" if a date or any value is entered in cell Y3. Ideally it should only say "Closed" if a date is entered in format xx/xx/xx, but am flexible so that it says "Closed" if any data is input.
At the moment Cell A1 has a drop down list consisting of open and closed. I will remove this if it causes complication.
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.
if a macro or private macro is necessary, I would need basic instruction how to use those as well (I mentioned private macro as I understand they automatically run whenever worksheet info is changed, which I would like in this example.)
I'm simply trying to change fonts in cells based on the inputs of other cells. Obviously, using conditional formatting doesn't allow for this.
For my example, assume the word "MrExcel" is in cells D2, F2, H2, J2, L2, N2, P2 and R2. If I type different font names (Times New Roman, Arial, Bookman, etc.) in the cells to the immediate right of each word "MrExcel," -- cells E2, G2, I2, K2, M2, O2, Q2 and S2 -- I would like the "MrExcel" word in each corresponding cell changed to the corresponding font to the right. I would like the ability to do this for up to 15 different fonts. There will be many rows to this worksheet I need to use this process for.
Is this possible? I'm in a bit of a jam and have exhausted my knowledge of formulas in that they won't work in this case ... I'm assuming I need to delve into the unknown land (to me) of macros, private macros, or VBA.
I have an excel file with many worksheets. I want to get a pop up message providing definitions of different subjects when they are entered from a drop down list in a specific range within a column to improve the users understanding of the subjects.
Worksheet 1
Range (where I want the pop up message to be valid): J85:J385
Subjects from drop down list in specified range: "x,y,z"
Pop up message: "Definitions of x,y,z"
Do I have to make a new module, or write the macro in the selected worksheet? What should be the settings of the macro (general, worksheet, declarations etc.)
I'm trying to create a spreadsheet with columns a, b, c, and d. "a" always has a value and I want columns c and d to change to a's color when they are given values. if "b" has a value when the data for c and d is entered, i want them to take b's color instead. The part I don't know how to do is that if i decide to give "b" a value, I don't want "c" or "d" to change colors if they already have values. e.g. if only a has a value when i give one to c, c will take a's color, but then if i give b a value then d, i want d to take b's color and c to retain a's color.
View 14 Replies View RelatedThere are two worksheets: Finances and Summary. On Finances, there is data input for years, quarterly. There is a cell on Summary that depends on which year is input first, in which case the cells in the formula SUM("cell1:cell4"))/4 is currently changed manually by the user by just checking to see which year data is input first on Finances. I need a macro or a formula function where the workbook finds which year is being used on Finances first, then changes the cells in the Summary formula accordingly.
View 6 Replies View RelatedI own 3 restaurants and I use Excel to track my sales statics. I have to enter certain pieces of data on a daily basis and it can become slightly cumbersome when dealing with the sort of washed out look of Excel spreadsheets. I wanted to write a Windows application that would handle the process for me but my knowledge of programming languages is too limited and I don't really have the time to learn. What I'd like is to have a single page on my workbooks with which I can enter the data in on a field type view, and have it go to the appropriate cell on the "Raw" data sheets in the same workbook. For example:
Let's assume this is the Raw data sheet. I currently enter the data that I need here, and Excel does the rest for me. This gets hard to look at everyday when dealing with hundreds of numbers.
Net Sales
Taxes
Prior Year Sales
Percent Increase/Decrease
[Code].....
Entering the data here would automatically fill in the data on the Raw sheet, and would know which cells to put the data into based on the date, which would of course be gotten from the =Today() formula.
I have a SS of a WW1 casualty list and one of the columns is Dates of Birth but the data has been entered in three different formats eg 21/2/1898 or 21.2.1898 or 21 Feb 1898. I have tried to Format Cells and change the date format to the slash but after highlighting a number of fields and right clicking they will not re format. Though i use Excel quite a bit i am a general user and dont understand it in depth.
article using =DATEVALUE(
am using Office 2007.
I want to enter any thing in column (A) and then the date & time automaticaly put in column (B)
enter any thing in (A1), the date & time entered in (B1) automaticaly
enter any thing in (A2), the date & time entered in (B2) automaticaly
and so on ...
I have the following code that fills a range of cells (starting at the active cell) with the date a user selects from a calendar control.
Private Sub Calendar1_Click()
ActiveCell.Value = Calendar1.Value
Selection.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Selection.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Selection.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)....................
How can I leave Column "A" blank if any data what so ever is entered in Column "B"?
View 3 Replies View RelatedI have a spreadsheet with two colums were new values are entered every day. On the bottom of theese colums I want to be able to see the sum of the last to values entered. How can I make this go automaticly? My teori is to make the last sum entered in each column appear in two cells, and then sum theese to cells. But I can't seem to find a function that works for this. I've tried the "IF" function, but I can only make it work on two cells on a row (=IF(A9;A9;A8)) (I want it to go all the way up to A1)
I have a two columns that I would like to use to calculate/identify the value of the most recent entry.
Column A has dates in ascending order, historical, current and future (say, A3:A300. Column B has sporadic entries (many blank cells) from B3:B300.
I would like a formula in B1 to display the value of the most recent entry in Column B (highest row number), and a bonus would be if cell A1 was able to identify the date of that most recent entry.
I need to just highlight the maximum value in a column. This will be for several excel sheets within powerpoint, and preferably automatically updated?
Something along the lines of:
VB:
If
Ax = Max(A1:A3)
Then Ax = red
Else = white
End
I want to copy a changing value from a cell (A3) to the next blank cell in the column creating a list of numbers for a total.
View 3 Replies View RelatedI have a Piece of code that works when a cell changes its value. I want to try and get it to work when only cells in coloumn B change. I thought the best way would be with using an IF statement but i am unsure on the code to write and get it to work. This is the code that i have at the moment.
View 4 Replies View RelatedI have a massive excel sheet with 300,000 rows and 100+ columns. When reading back through my formulas, it can get very confusing. Example: "=(AZ9*5 + 1)+BH9/2 +(AP9*0.75)" I then have to figure out what each column letter is representing. Instead of this, could I rename a column so I can reference the variable name? Example:
Instead of =CONCATENATE(A4, " ", B4)
have
=CONCATENATE(FirstName4, " ", LastName4)
I am using this formula
=IF($A5="Select","",MAX(INDIRECT(SUBSTITUTE($A5,"/","")&"m1440"&"!d:d")))
It looks for the sheet based on A5
It finds the max number from the sheet in Column D
This is what i would like to do please....
On this found sheet - Column A has dates in this format yyyy.mm.dd
I would like to enter a date in a cell (say for example AA5 on main sheet)
Can the formula above be adapted to:
Look for sheet based on A5
then
Look at date entered in AA5
then
use Vllookup to find row containing date in AA5 (making table range the ENTIRE SHEET found based on A5)
then
Return value from Column D
Is there a macro that I can use, that if N is entered in to column A then the whole row will be hidden and if Y is entered in, it will reappear.
Can this be done automatically without having to click a button.....
I want to pick the last number entered in column and put it in a formula. Is there an easy way to do this, or do I have to stack a lot of IFs in a string.
View 8 Replies View RelatedI need in some way to make the range variable, because it can change. I need only the first range to be different.
Code:
x = .Range("a4:a27").CurrentRegion.Value
A4 have to be instead the cell where Denmark typed. Column A
And
A27 have to be the lasted used cell in column B
But after the row where it have found Denmark.
Please have a look.
Below code is from AB33, But this is not a cross post.
Different question
Code:
Sub copyp()
' Denmark
Application.ScreenUpdating = 0
Dim cell As Range, i&, dic
With Worksheets("Middle Result")
x = .Range("a4:a27").CurrentRegion.Value
[Code] .......
I have a spreadsheet with names and start / finish dates columns down the left hand side then a row of week commencing dates along the top.
for each name row I would like to change teh fill colour of a cell to green to represent the week in which they started and to red for the week in which they finished. I therefore need to cross reference the start and finish dates for each name with the relevant week commencing dates at the top. Somehow! I presume there is some kind of vlookup type formula that I need to use in conditional formatting, but I am not sure what.
I have some code that runs through about 5000 rows of data looking at duplicates for column A. instead of deleting the duplicate rows, i change the font.colorindex of the cell in column a so that all cells containing xx are one color, and all cells containing xxx are colorindex+1.
Since i have about 5000 rows, colorindex limits itself to 122 (i msgbox'd each time and this is the last number i got before the subscript out of range).
Is there anyway around this, or is there a better way to pickout duplicates. remember, i can't delete duplicates, i need a way to manipulate the duplicates data once i pick them out.
The attached sample workbook has a listbox of areas in a userform. The Textbox on the user form is so that the user can edit the Area names in the listbox. The userform functions fine as long as the column that contains the area name is not hidden. What I want to be able to do is edit the Area Names using the Textbox with the area name column hidden.
View 2 Replies View RelatedThis should be simple but this is not working like I want it too. It should change the value in the cell adjacent to the cell in column B if the value of the cell in column B = "X".
Dim Well As Range
For Each Well In Range("B:B")
If Well.Value = "A2" Then Cells(Well, Well - 1).Value = "SamplePlate2"
Next Well
