So i've been trying to do this invoice/inventory/client , I couldn't find a way to somehow register the whole invoice as it is, so i can later print/visualize it if i lost the copy or something.
It would be best if it just stayed stored in a variable and not always visible, and only appeared if i wanted to check/print it.
The whole invoice would be from A1 to F30
Excel 2010 btw
I have two conditions for a cell. One is that it counts the time elapsed in cell M4, (=now()-J4). The other is whether or not we are still waiting for a response (in cell L4, Y or N). What I would like to do is make it so that when we are still waiting for a response the M4 cell is highlighted yellow if more than 3 days and red if more than 6 days, but if cell L4 is changed to "N", then the formatting goes away. II use Excel 2010.
View 1 Replies View RelatedI have a workbook containing two worksheets of staff training records.
The first work sheet contains a list of names and the dates they completed various training courses. I have used some simple date based formulas and conditional formatting to colour-code their name depending on whether their earliest retraining due date has passed, is coming up in the next few weeks, or is a long way off. The data is set out alphabetically, one person per row of data.
The second sheet contains the same list of names, but each person's data is split across two consecutive rows. The cells in column A which contain the staff names are merged in pairs so that the name heads both rows of data.
I want the colour coding of the merged name cells in sheet 2 to automatically copy the colour coding applied to the single name cell in sheet 1, but don't know how.
I'm using excel 2010.
I'm using windows xp and excel 2010. I have a very large sheet that has a bunch of highlighted cells. There all highlighted the same color. I need to remove the highlighting colors and change the font color of the highlighted cells to red.
View 3 Replies View RelatedI'm using Excel 2010.
I have created a spreadsheet with 3 month inspection schedules on using the formula below.
=DATE(YEAR(C50),MONTH(C50)+3,DAY(C50))
I then copy this in to a cell where I want the date to appear... and then in to another cell where I want a further 3 month date to appear. Obviously changing the cell reference.
I would like the formula to ignore the very first cell "C50" if there is no date inputted in this cell.
How to sort out a way to count cells whose fill colors have been set via conditional formatting.
I'm sure that I was once able to use Chip Pearson's CountOfCF function back in the days of Excel 2003, as it worked around the 3 condition limit and used .ColorIndex which assumed that you'd only ever be formatting using the .ColorIndex values of 1 to 56, but I can't seem to be able to get it to work in 2010.
how to apply conditional formatting via VBA to a range of cells based on input from another range of cells. Obviously this would be easy in Excel 2010, but I'm still using 2003 at the office and it needs to stay in this format to be readable by other users:
For cells M8:EK8, my conditional formatting
condition 2: Formula Is =AND($E$8>=M2,(($E$8-$D$8)>=(N2-$M$2))), color index is 40
condition 3: Formula Is =AND($F$8<=M2,$G$8>=M2), color index is 39
I want to add:
condition 4: Formula Is =AND($H$8<=M2,$I$8>=M2), color index is 40
condition 5: Formula Is =AND($J$8<=M2,$K$8>=M2), color index is 39
and so on
The cells in the range M8:EK8 are blank, they only get colored based on input added to D8 to K8. If there is no input, then the cells should be uncolored.
resource tracking ex.jpg
I am using Excel 2010 and basically i am trying to fill a range of cell with a green color if any value was enter in a specific cells. Example: I would like to fill range: A10:c13 with a green color (regardless of the cells content in this range) if a value was entered in cell C10 or C11 or C12 or C13.
I've tried conditional formatting but unfortunately I'll have to apply formatting for every cell and for a range of over hundred cells is not efficient.
Working in Excel 2007. I am using excel for a data log (basically) and want it to format all empty cells in a row yellow if there is data in column A
Basically, If i have a value in A2, I want any empty cell between B2-G2 to be filled in yellow (as an idicator to the inputter that the cell needs to be completed).
there is already conditional formatting on these cells, which i want to maintain for the non-empty cells. I also have "0" as a value, so I couldn't use the basic conditional formatting setting it =0, it highlighted cells with $0.00, which i do not want.
am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....
I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....
I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...
I am working on a excel file that has a sheet that i want to convert to .csv. The sheet has formula's for the first three column that are warped an if statement that returns "" (blank) if certain conditions are not meet. However when i got to save the file as a csv i get a file with ",,,,,,," where there is no data. I would like to save csv and have only commas where this data.
Example
When i convert a sheet like
Firstname Lastname Password
Tim Cats Bo3n9x
it saves as
Firstname Lastname Password
Tim Cats Bo3n9x
,,,
,,,
,,,
In the .csv file.
Files i am current working on are attached.
user of excel 2010.
I have a spread sheet with 2 columns that I have applied conditional formatting to to find duplicate values. So the duplicate cells are highlighted in red now.
Problem - I want to copy only those red cells to a new column in a new tab, and am having a devil of a time figuring out. Best case would be some sort of formula I could use on the new tab&column to do this.
I'm using Microsoft 2010 and as like many others here I cannot find a way to paste into visible cells only i.e. the data I want to add to my variables is only available for every five years, but my data ranges 1990-2010 each year, so I have filtered my data to the 1990,1995,2000,2005,2010 so that I can paste the new data in, but of course it pastes into 1991 1992 1993 etc instead of the filtered results.
I've tried the find and select visible cells only but it says when you try and paste into them that it is not possible for more than one cell at a time.
The concept is to have a user open a sheet that breaks down that current day into 15 minute blocks of time. Later, I will work to append that to an Access DB or to a master Excel sheet as I will have multiple engineers inputing times for multiple days. There are multiple paths for this....We don't have Access for everyone yet or I would look at that path now.
Right now I am trying to make the initial input screen easy to use so that they actually use it.
I want to allow the user to select multiple 15 minute blocks and then click a button that adds a specific value to each of those cells. For instance, they might choose 8 15 min blocks that are not necessarily blocked together (C5, C7:C10, C15, C20:C22) They would hit the "Project 1" button and "Project 1" would be inserted into each of those cells.
I have about (5) categories so I would simply have (5) buttons with the different inptu strings.
But how can I have a macro set the value for multiple selected cells? Ideally, they would not have to be blocked together but, if there is no other way, if I could have blocks of cells filled in all at once.
I am using Excel 2010.
only a few of us have Access but I will be the only one accessing the collected data. Besides, I would make Excel query the Access DB for more general use. I
I have a sequence of 40 columns of data, representing 10 weeks worth of events with 4 values per week
I need to average the first pair for each week, ignoring zeros and blanks, and also separately the 2nd pair of cells, across the whole 40 columns
I am trying to come up with a neater solution which takes into account the zeros, at the moment I have the following which does not, for row 34
=IF($AR34>0,ROUND(AVERAGE(D34:E34,H34:I34,L34:M34,P34:Q34,T34:U34,
X34:Y34,AB34:AC34,AF34:AG34,AJ34:AK34,AN34:AO34)*2,2),0)
I could use SUM, COUNT COUNTIF etc but its gonna get messy
Using EXCEL 2010
I will get right to it. I am using the following:
=AND(LEN(Y2)=10,MID(Y2,3,1)="/",MID(Y2,6,1)="/",ISNUMBER(SUBSTITUTE(Y2,"/","")+0))
To create a custom date validation formula. But it doesn't work.
I need to insure the user has to put in the format "xx/xx/xxxx" where x is a 0 to 9. I am using Excel 2010 and when I put in a year greater than 1899, my error message pops up. I'd also like to restrict the month to up to 12 and yes the days up to 31. I used the "date" utility but it allows stuff like 4/25 and then puts in this year for you. It also accepted three digits such as 786 and I don't know what that means.
But for the life of me, I can't get it to do what I need it to do. Excel 2010 user, and I've attached my file with the information, and descriptions.
View 5 Replies View RelatedI'd like to apply multiple formula to a set of cells on a summary page. My summary page also contains 3 variable dropdowns, and I'd like to display data based on the text selected in those dropdowns (pulling data from 2nd tab "Variables")
The following formula works in the first instance:
=IF(AND(H4="Product Type A1", H6="External", H8="Existing"), Variables!C4, 0)
What I'm struggling to do is add additional formula to the same cell in order to deal with the remaining eventualities of the drop down variables:
Variable 1:
Product Type A1
Product Type A2
Product Type B1
Product Type B2
Variable 2:
Internal
External
Variable 3:
Existing
New
Or am I better using a VLOOKUP or something?
I am using excel 2010. When I click on cells on one column, something like text box will pop up with information related to that column. How can I get rid of this text box. How to disable this text box which I think it has a link between the text box and cells on that column.
View 1 Replies View Related how to display Sparkline from non-consecutive cells.
I want to display it from two apart ranges: A1:C1 and F1: J1
If it can't be done within the worksheet - is there a VBA code to workaround - such as filling-in an Array from the non-consecutive and using that Array as the Sparkline source ?
I have an excel sheet (version 2010) that has a few sections that you can add in names (via drop down box). I also have a spot where i want these names to populate - on a second sheet.
Ex
Truck #1:
1. _____
2. _____
3. _____
4. _____
5.______
Truck #2:
1. _____
2. _____
3. _____
4. _____
5.______
Truck #3
1. _____
2. _____
3. _____
4. _____
5.______
Truck #4
1. _____
2. _____
3. _____
4. _____
5.______
6.______
7.______
8.______
My problem is when I refer to the first worksheet from the second worksheet it shows blanks where there are no names. For example i want the second sheet [ LOC ] to show the names that appear under Truck #1, #2, #3, #4 - but truck #1 may not always have names under it?
Sample Excel file attached - Help.xlsx
I am using MS Excel 2010 want what to change a number of cells if a value is true e.g.
Row one has the headings Prz, Stat, Val, andPts
Row two has the following values :-
(Prz) a2 =2, (Stat) b2=1, (Val) c2=$30, (Pts) d2=10
How do I do the following in Excel :-
IF B2=1 THEN A2+1 AND C2+30 AND D2+5
so that the above cells are changed to
(Prz) a2 =3, (Val) c2=$30, (Pts) d2=10
For some reason I don't seem to be able to insert any columns/rows/cells in to my excel spreadsheet. This is a problem in both basis sheets and more advanced ones.
View 1 Replies View RelatedI am trying to use the IF formula or a similar VBA/Macro to color certain cells. We deal with 16 different sand types that come in on rail cars. I want to put in the sand type in a cell; EX: C11 has text "30/50BH" if this statement is true to color cells A11-D11 yellow. If it is not a true statement to check for the next sand type, "20/40BH" and go on from there. If I can get an example of what to do I can build it for the 16 sands we have.
The formula I have in mind would be something like
=IF("30/50BH",[colorA11:D11,Yellow],[IF("20/40BH",[colorA11:D11, Purple], .......
I'm sure there is a less brute force method of doing this, but my knowledge of programming and excel is limited. I am using the 2010 version of Excel.
I need to copy two adjacent cells down every 7 rows in the same column.
Would do it manually but have some 4000 rows.
I need Macro to Format all cells in column according to value. The cutoffs are >20, between 21 and 50 (>21;<50) and greater than 51 (<51).
I have macro to consolidate cells from multiple files to single workbook. Now I have to 'color code' the value in all cells in column 'B', all as part of one large macro.
I'm trying to create a macro to insert a formula into a specific cell. The formula is meant to check if a cell has text, and then if it does, search for the text on another page.
I had a go at the code, but keep getting Runtime error 13.
I'm using Excel 2010
Code:
Sub new_entry()
'
' NEW_ENTRY Macro
Dim rowNo As Integer
[Code]....
I am trying to lock out cells if a value is entered in another cell in Excel 2010. For instance, when entering a value into N7 (merged Columns N7,O7,P7 into one - not sure if that matters) I want Q7 (same thing, merged Q7,R7,S7 into one) and T7 (merged T7,U7,V7 into one) to be locked out.
So, if N7 has anything typed into it, I would like Q7 and T7 (merged) to be locked.
If that's possible, I would also like for it to happen from rows N7 - N21 ...
Is this hard to do? My understanding is that it's only possible through VBA ... am I accessing the script correctly? (right click the 'sheet 1' tab [named Issues log], select 'view code').
I have created a comparison sheet and compares my old one from last week to the new one I create for this week.
It populates cells with the differences and shows me the data from the new sheet. But since my they are so massive A5:AZ20,000 I want to create an auto summary or something.
Column A has each properties Unique Identifier And the rest is various info for the property. I would like it to return my unique ID and then the columns with new data that has showed up from the comparison some are text and some are numbers.
Also I am using Excel 2010.
In C3 i have a number (1 for this example)
I want this number to dictate that the cells in row 8 return the number up and 1 (c3) over.
What formula could I use to do this?
Excel 2010
B
C
D
E
F
G
H
[Code] ...........