Automatic Formatting Based On Number Entry
Oct 2, 2008
So the title isn't really totally descriptive but it was all I could come up with. I have a work book that has 30 identical sets of 5 cells for users to enter in information. Some of my users will not need all 30 sets. I'm wondering if there is a way for the user to enter the number of cells they need and have excel then format so they have the desired number, instead of 30. Then have excel change all the instances of this number in formulas so that it matches the new number of cells. I hope I explained this well enough for someone to help.
View 9 Replies
ADVERTISEMENT
May 30, 2007
I want to create a monthly timesheet which contains 9 columns for
(Date, Day, Project no., Activity, Time In, Time Out, Total Hours, OT Hours, Remarks)
I have used IF Function to calculate Total Hours & OT hours automatically. Time IN & Time OUT, Project No., will be entered manually on daily basis.
Weekday function is used in the Day column to return the corresponding day of the date in the Date column.
Name of the Month and Year will be manually entered in the designated cells I3 and I4 respectively.
Now the solution I am looking for is, the dates should be automatically entered in the Date column (in cells A8 to A38) based on the Month & Year entered in cells I3 & I4. Dates of the corresponding month of the year should only be filled in. (If a month is not having 29, 30 or 31st day, the corresponding cells should be left blank. i.e. nothing should be displayed in the corresponding cells). I am looking for some sort of formula to enter in the cells of Date column (A8 to A38) achieve this. I have searched the forum and could not find anything which could at least give me an idea about the kind of function or formula to be used.
Attached here is the time sheet I am trying to create.
View 8 Replies
View Related
Jul 22, 2014
I have a long set of data that I extract a "table" out of based on index matching controlled by 4 different criteria. This part is working as intended. However I need to format the cells with the corresponding "fetched data" based on another criteria. Makes sense? Have a look at a spreadsheet i made representing my problem. It has comments for the problems.
excel tableformatting problem.xlsx‎
View 1 Replies
View Related
Apr 16, 2009
I have got 2 sheets:
- list (list of transactions)
- accepted (accepted transactions).
I want to make macro, which will:
1. select max value from sheet.accepted "B:B"
2. enter max value +1 in sheet.list "B8"
3. automatic fill down from B9 to the end of list with number increasing by 1 (in B9=B8+1, in B10=B9+1 etc.)
I tried with this:
View 5 Replies
View Related
Jul 22, 2008
I have a spreadsheet with 'due date' for returns in column L and 'received date' for returns, in the adjacent column M.
I would like to get the due date to turn red when it is overdue, ie. the date in the cell has arrived and no return was submitted. By trawlling the forum, I managed to accomplish this with the following in conditional formatting:
Cell value is less than or equal to =TODAY()
All's great.....but - I would like the date to turn back to black if I enter a date in the adjacent received column, column M.
In other words, I want to flag a problem if the due date has passed, but once I receive the return and enter the date I receive it, i would like the due date entry to revert back to the original formatting ie. black. So only the outstanding returns are highlighted.
View 14 Replies
View Related
Jan 7, 2014
I have a 12 month budget spreedsheet. Some expenses' are fixed every month. I would like those cells to be automatically filled with that fixed amount. Say on the 5th of every month a particular cell would have $50 automatically entered so i don't have to do it manually.
View 2 Replies
View Related
Jul 2, 2009
In the attached file I'd like to have a formula on cell B3 that would pick the number from the latest entry on "Actual" columns (column K, column M....). This should update the cell each month data is entered in respective month column.
View 4 Replies
View Related
Jul 16, 2008
I have a workbook with several sheets, each of which represents a client. At the bottom of this sheet, I have a call log to record the times that we attempted communication, left a message, etc. Since it's tedious enough as it is, I'd like to save my team 2 seconds and a slight bit of that tedium by having the date an attempt was made automatically recorded in the row where the attempt is entered.
Let's say this all takes place in A47:D47. For example, if I called the client on 7/15/08 and left a message, I would record who was called in B47, the name of the caller (me) in C47, and the result in D47. If possible, I would like to have the date of the attempt (today's date on the given day) entered in A47 upon the entry of information in B47:D47.
View 14 Replies
View Related
Dec 12, 2011
I am attempting to create a formula which will detect of a cell is empty, and if it is empty enter the date by using TODAY(). I also dont want this date to change when opened after it has been entered. This is what I have been working with so far.
Code:
=IF(A1="",TODAY(),"")
Code:
=IF(G9>=TODAY(),A1=1,"")
I am trying to use the second formula to change the value in the cell "A1", but instead it just returns a true/false statement in the cell with this formula.
View 6 Replies
View Related
Jan 12, 2012
How can I get an automatic time & date entry into cell B1 based on a alphanumeric entry in cell A1. The time and date must not re-calculate every time the workbook is opened.
View 4 Replies
View Related
May 10, 2007
know if there is any way of getting a cell produce an X (for example) when it is selected and blank when selected again? Basically a tick the bok response. I know i could use a drop down list but i was hoping for a quicker method as a lot of ticks accross a lot of cells are required.
James
View 9 Replies
View Related
Apr 12, 2006
I've been updating all the charts manually each time I update the entries in the worksheet which charts are created from in the same workbook. Is there any way (eg. by use of macros or VBA) that I can have all the charts updated automatically when data ranges are updated each time?
View 2 Replies
View Related
Jul 30, 2013
I have a userform, UserForm1, which lives in a spreadsheet called 'Data Entry.xls' There is nothing else on the spreadsheet itself, it's just for the use of a userform.
I would like the user to populate textboxs in UserForm1 but have that update cells in a separate spreadsheet 'Training.xls' in the same directory.
I have this code at the moment to find the next empty row and to input data into it, which is working perfectly to enter data to sheet1 in Data Entry.xls:
[CODE]Private Sub CommandButton1_Click()
eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(eRow, 1) = TextBox1.Text
Cells(eRow, 2) = TextBox2.Text
Cells(eRow, 3) = TextBox6.Text
End Sub[CODE]
How can i modify it so it would do the same thing, i.e find the next empty row and then populate with what the user types in the textbox but in Sheet1 of 'Training.xls'
Would 'Training.xls' need to be open?
View 2 Replies
View Related
Mar 14, 2014
Is there a formula that will allow me to look for the existence of any number value in a row of one worksheet and then return a specific number value in a cell on another worksheet? For example, if the formula finds any number value it will always return the number 15 to a cell on another worksheet.
View 3 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 20, 2014
I have a percentage in R3.
If I make an entry in D13 then I want the R3 to be duplicated into C27 otherwise C27 should be 0.
View 4 Replies
View Related
Dec 17, 2013
I have a list of 2000 pieces of inventory. Only the year of purchase is associated with each one, not a specific month or day. I'm trying to apply conditional formatting so that...
Less than 2 years old = green
Between 2 and 4 years old = yellow
Between 4 and 6 years old = orange
More than 6 years old = red
I've been trying =TODAY()-365 (365 replaced by number of days in the above named ranges) but the cell only changes to the color of the most recently applied formatting rule.
View 2 Replies
View Related
Sep 11, 2009
Is there a way to copy the formatting of a cell automatically? For example, if you just do "=B1", then the cell with this formula will automatically update with whatever cell B1 contains whenever it is changed.
But is there a way to do the same thing with formatting? So if cell B1 was bolded, or changed the cell color, or conditional formatting, is there a way the cell that is referenced to B1 would also automatically change to whatever the formatting change is?
View 3 Replies
View Related
Jan 29, 2013
I have been asked to create a clash grid that shows when promotions for specific customers are on for specific products.
I've got all the information collated, and I suck it all up into a cover sheet that splits it by product, by customer, and then shows the weeks each product is on promotion. The issue I have, is I wish to colour code each customer so it is easier to read for those who aren't excel savvy.
I've tried conditionally formatting it, but have stumbled a couple of times. I can write an 'if' statement for it, but can't work out how to get the conditional formatting to follow the statement (I'm not sure this is the best way to do it, but figured it was worth explaining).
Attached is a 'How it is' and 'What I want it to be'.
StarkeyExample.xlsx
View 5 Replies
View Related
Dec 4, 2013
In the attached spreadsheet, I have a limit value which is used to set the color of other cells based on their values.
So if the value is greater than limit, the color is red.
When I change the limit, I do see the changes in color.
However, I also want to show only the red values.
View 5 Replies
View Related
Nov 22, 2011
I am trying to figure-out how to set up conditional formatting of dates in a column (e.g. Date Submitted) and have these dates, and/or cell, change color as time progresses. I have Excel 2007.
For instance I have an application submitted on 11/20/2011 in a cell. I would like to have the date change color (Yellow) after 30 days have passed.
Then do the same except change to Red after 60 days have passed the date.
I searched the forums and did not find this particular problem. I of course may have missed it too...
My guess was to try "$A$1
View 4 Replies
View Related
Sep 1, 2006
When opening a CSV file in Excel, it seems Excel makes an "intelligent" attempt at interpreting the data into a data type.
This causes a text field containing 7300070E-4 to be interpreted as 7.30E+0
View 9 Replies
View Related
Mar 6, 2009
I am having is on the user form I have a default start time test box and a default finish time text box that when the form is opened it looks at the constants work sheet and if there is any data it puts it into the appropriate text box, all ok so far when a user wishes to change the default data in these text boxes I need to test if they are entering the data correctly, in Access 2000 I would set the defeat format of the text box to Time? Data type=Format(time,”h:mm”) or a or an input mask of “h:mm” so if they did not enter the time in the correct way an error message would pop up and tell them to renter the data the correct way.
I have not been able to find this type of settings in Excel 2000 I have tried testing the data using the beforupdate but it also dos not recognise formatting
The other thing I need to do on the user form with this formatting is calculate the time difference between to two time inputs and come up with a total hours.
I have managed to do all of this in the work sheets and preforms well but not on the user form
View 8 Replies
View Related
Aug 21, 2009
I'm trying to figure out how to enter the formula for conditional formatting if I want the selected cell value to be red when it is <0.
View 4 Replies
View Related
Mar 24, 2009
I have a date in H34, 21/03/2009.
In I34 I have the same date again, except formatted as MMMM YYYY so its showing "March 2009".
In G10, I want the cell to read: Number at end of March 2009, with the month and year being taken from the date range. To be honest, I can get the year okay using the Year() function but cant get the month as cant convert it back from a numeric.
View 9 Replies
View Related
Oct 3, 2013
I'm Trying to create a invoice that generates the invoice number automatically every time its being opened
Now, how to do it with a macro but the problem is i need the invoice to be saved as a template. In the end i need the invoice template to move the invoice number to the next one when opened and when im done with the invoice i need a print button that saves the invoice with contents to a specific folder, clears the contents and saves the invoice with the current invoice number as a template so that next time its being opened it can just adjust the counter.I have done the counter adjustment part .
View 1 Replies
View Related
Oct 14, 2008
I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.
View 2 Replies
View Related
Jul 26, 2007
Is it possible to have Column E automatically enter a value based on a 4 digit number inserted into Column C?
I have about 200 different 4 digit numbers that can be entered into Column C and 8 different corresponding values that need to appear in Col E... Would this be possible / difficult?
View 9 Replies
View Related
Dec 19, 2013
I have this problem where the SUM function is returning 0. The context is this- I am simulating values using RAND() and looking up the values corersponding to probabilities from a different tables. I then use the TRIM function to return the value I need, and the final number is of a "general" format.
This is the formula in the column I wish to sum:
And this is dragged down the column.
When I change the cells using =VALUE(cell) the SUM function then returns the required value. Oddly the SUMPRODUCT function seems to work.
Calculation is set to automatic and the cell has been formatted as a number.
View 2 Replies
View Related
Jun 15, 2012
I am trying to work out how to automaticaly fill in the next number in a sequence of bespoke numbers like this in column B 1012-0153-70, next 1012-0153-71, next 1012-0153-72 and so on so when i click on the next open cell in column B it adds the next number in the sequence
I have the following VBA code in my Sheet:
Private Sub Worksheet_SelectionChang()
With Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
If Target.Address = .Address Then
.Offset(-1, 0).AutoFill Destination:=.Offset(-1, 0).Resize(2, 1), Type:=xlFillDefault
End If
End With
but nothing seems to work. i also have the following VBA code on the same sheet:
Private Sub ComboBox1_Change()
End Sub
Private Sub Calendar1_Click()
[Code].....
View 3 Replies
View Related
