I have a formula in a cell....let's say column B.
It basically says "If column A is empty,then = 24:00, otherwise = column A".
It works fine and displays as 24:00 in the proper cells, but if you try to add all the 24:00 together it doesn't work. I guess the simple question is, how do I hard code a proper time value from an If Statement.
e.g. =IF(A1="",24:00,A1)
How can I make a cell display 0 or remain blank instead of #N/A?
I have several cells that are either VLOOKUP or just normal sum functions but they all display #N/A.
Is there a way to make a cell display the text difference between two cells?
For example of cell E2 reads "Thank Goodness it is Friday" and cell F2 reads "Thank Goodness" can I put a formula in H2 that will show the text that is in E2 but not F2 ( "it is Friday")?
I have several hyperlinks that may or may not contain data, the linked cell will display "0" if there is no data. When I print I dont want the "0"s printing.
How do I make the cell display nothing when a hyperlink cell is nothing?
i have a formula that will return both negative and positive numbers. If it's negative it obviously shows a "-" sign. How do I force it to display a "+" if the result is positive?
View 9 Replies View RelatedI'm working on a project, and I need a cell on a worksheet to produce the name of the worksheet, such that if the worksheet changes, or is copied [Sheet1 renamed to Sheet(1), for example] the cell will automatically update [now displaying Sheet(1) in cell A1].
View 2 Replies View RelatedI've recently discovered that I enjoy making Excel spreadsheets as a tool to solve recreational puzzles. Mostly variations on crosswords and such. Honestly it probably takes much longer to organize everything into a sheet than it would to just solve on paper, but I seem to enjoy it this way more.
The Actual Question (for TL;DR Types):
Anyway, I am trying to find a way so that a group of cells can all copy the value of the other cells in the group, regardless of which cell the value is entered into. So I don't want there to be one "master cell" that all the cells are linked to. I want them all linked to each other.
Specifically, right now I am making a spreadsheet for a non-traditional style crossword puzzle. My intention was to make fields for the answers to each clue, with individual cells for each letter, as well as the crossword grid itself. Then I would link the cell in the clue answer to the cell in the grid, so that when the answer was filled it, it would be inserted in the proper place. But since this is a crossword, there is more than one clue with a letter that links to each cell. I could make it so that the cell in the crossword grid is the "master" and make the other two equal to it, but I would much rather be able to enter the letter into any of the three cells and have all three fill automatically.
I realize I am making this way more difficult than it really needs to be, but that is kind of what I enjoy about it. Finding creative ways to make the puzzles fit into a spreadsheet and making them more streamlined.
I am using a VBA macro (Using mouse clicks as inputs for neuroscience experiment?).
What the macro does is it inputs a timestamp every time a keyboard combination is used (alt+right arrow in this case).
The part of the macro which inputs the timestamp is like this:
Sub AltRight_Sub()
On Error Resume Next
Cancel = True
Cells(Rows.Count, 2).End(xlUp).Offset(1) = Format(Now, "HH:MM:SS")
End Sub
I would like the timestamp to be displayed as HH:MM:SS.00, i.e. show the milliseconds. I tried playing around with the timestamp format (changing it to = Format(Now, "HH:MM:SS.00" or = Format(Now, "HH:MM:SS.0;@")) or = Format(Now, "MM:SS.000") and I tried using = Evaluate("=NOW()") instead of = Format(Now, "HH:MM:SS".
I want to check if numbers are smaller than a specific number, but my problem is that the numbers I want to check is formatted as text. Is it possible to rewrite this formula so it works:
Code:
=SUMIFS(myTable[Amount]; myTable[Number]; "
I tried formatting the numbers to "text" and still got undesirable results.
Below is the data I have. The numbers on the left are part numbers and the numbers on the right are the times they're used. I would like the part numbers to be on the x axis and the times they're used (73, 42, etc.) to be on the y axis. Pretty simple right? Change the part numbers to text, highlight both columns and insert a column chart, piece of cake.
Instead what is actually showing is a series of "1 - 9" on the x axis and a range from 0 - 50,000 on the y axis which it thinks the part numbers are even though I have them formatted as text.
490125
73
221261
42
490189
42
[Code] ........
How do I make this formula treat "0's" as a blank
=IFERROR(VLOOKUP(A11,'Workings Support'!A5:E50,2,FALSE),"")
My macro sorts fine and includes everything I want but I have extra cells for adding new data that contain a formula. When my macro sorts it places all empty cells at the top of the list then the data. When I delete the hidden formulas the sort places the empty cells at the bottom of my list like I want. Is there any way to code my macro to treat all cells that contain only the formula(not the data) as empty? The only thing the formula is, is a reference to another sheet ie. ='Sheet6'!$A$6.
i'm looking for a formula that gives the greatest number of a range
problem is that there are positive and negative numbers in the range
but I only want to look at the value (+/- signs are not important)
The problem i have is with the sum offset function. The formula i use is to sum up the totals for each page of an accounts document. At the top of each page there is a column with " £ p " in it and i use that as the basis of the formula. This formula is entered in the bottom of every page to calculate the total for each page.
=SUM(OFFSET(F211,-(ROW(F211)-MATCH("£*",$F$1:F210,1)-1),0,ROW(F211)-MATCH("£*",$F$1:F210,1)-1,1))
This works all the time but when text is entered into column F (the column i'm adding up) it messes up with the formula. the formula will instead find the £ p but for a page 2/3 pages before the one i'm calculating on. This formula only faults when text is entered. Another thing; this excel sheet i'm working on is an output from a different computer program. it is outputted as csv file i think.
Like i said the formula works a treat except when text is entered. The obvious way around this not to enter text but that is not an ideal option. I cannot think of another way of calculating the total for each page although i'm sure another exists.
Example:....
i m wanting Cell B1 to display true if any of the values in column C match otherwise I want to display false.
A 1
B 0
C 0
D 0
But I can't determine how to get this done.
I created a vb macro to open a text file then process the file then close the file. Here is my problem:
Problem: THe text file has rows of data in it as follows
5155111111551511111111111511111111111111111
This row of text gets converted to
5.16E+42
because excel treats the row of text as a number but i dont want it to do this transition.
When i save the file and then reopen it using say NOTEPAD i see 5.16E+42 and not the long string of text.
In column J, Cad Hrs. How to make to display (0), instead of (-0,02) when I just do other work than Cad Work? I need to separate type of work according to price hour, overtime, and so on.
View 2 Replies View RelatedI've got two columns of data. I'm trying to test if either cell has a value greater than 0 to return a output. Say, in this example "has dividend". My problem is with #NA cells. Is there a way to ignore null values?
I've also got some cells in the data that contain "#VALUE!". What is the basic difference between #VALUE and #NA?
I have a spreadsheet on sheet 1 with a list of customers and their information. So on column A I have the customer number (i.e. k968, e37, p528,...), on column B i have the customer's name, on column C the street's name, on Column D the house number, on column E the zip code and finally the city on column F.
Right now there are around 600 customers in this list.
I have made a userform with a combobox in which I want to select an existing customer (pulled from the spreadsheet). On the same userform I have textboxes (customer number, name, street, number, zip, city). When I select a customer in the combobox, I want this customer's info to show up in the textboxes. I want to be able to change the info and hit Next to store the changes in the spreadsheet. When I do not select a customer from the combobox, I want to add new info in the textboxes and hit Next to store this info as a new customer. The userform also has a delete button. Then I select a customer in the combobox, this customer (and it's info) should be deleted from the spreadsheet when i hit Delete. So the spreadsheet is variable in length.
I need to figure a way to make to cells with dates equal each other if the
day,month and year are the same but are placed into a cell at different times during the day. "Making Date Now () = (06/29/09) In another cell". Therefore, A1= Now() and E11 = 06/29/09
I am trying to write a macro that will make the bottom cell in a column equal to the top cell. The top cell will not always be in the same row and there won't always be the same number of cells.
View 1 Replies View Relatedhow to make a cell a color depending on a number in a different cell? Example...if A2 is 92.6 how would I make D2 blue? But if A2 were 91.9 how would I make D2 red?
View 5 Replies View RelatedIs there a way to create a multiple dropdown (Similar to Windows Start menu) so that the user can select the item and its properties then the selections be placed into multiple cells? For example, i select wood from drop down list and i get another drop down list asking for the type of wood, I select MDF and another drop down ask for thickness. when I finished my selections, then the selected Items copy themselves into 3 different cells.
I have a drop down in (A1) cell and I select an item from it. I want (A2) cell to check the name in (A1) then call up another drop down i already made in (A2).
I want to make a cell blank if nothing is written in another cell. I have used this formula in C4: =IF(ISNUMBER(C3),IF(C3<180,C3+180,C3-180),"")
View 1 Replies View RelatedHow can I make a cell mandatory, if another Cell states XX?
For example:
If column B has "agency" then column C must be completed stating which agency was used?
However sometimes column B could say "no agency" therefore column c doesn't need filling!
I have a monthly forecast document that is updated by an operator monthly. At the end of the month, the operator presses a "Copy Accrual" macro that copies accruals for the month and stores them on a seperate tab. After sending the document back to the Finance team (necessary step due to software complications), Finance will send the operator back an updated file at beginning of next month. The operator than presses a "Paste Reversal" macro that is supposed to input the negative of the accrual stored in the seperate tab. The problem is that it is posting as the same value (not negative of value). I tried recording a Macro to fix this, but it did not work. An example of the code that came up with is:
Worksheets("Weekly Forecast").Select
Range("B9").FormulaR1C1 = "=-Accruals!RC[-4]"
Range("H9").Select
Selection.Copy
Range("H10:H33").Select
ActiveSheet.Paste
Thoughts on what to do? I think the two things that are throwing me off is the negative, and moving between the different sheets.
I have 2 problems relating to LOOKUP.
Not sure if Excel can perform these calculations as they could get to complex.
Problem 1
Can it be possible to have excel look at data from one cell reference another cell then display the results from the cell next to it in another cell, sort of example:
Tab 1 (Never changes)
AB
Bob1
Jon2
Fred3
Tab 2 (Dynamic, changes each week)
AB
Jon
Fred
Bob
So it would work as follows.
Tab2 column B will take Tab2 column A’s data check Tab1 column A and display Tab1 column B’s result.
Problem 2
Weekly league rank table that shows position movements week by week
Example.
Week1
1Jon
2Bob
3Fred
Week2
1FredUp 2
2BobNot Moved
3JonDown 2
Can Excel calculate/show the actual movements of league positions?
I am using a “borrowed” formula from a coworker that I do not really understand however it is working for me, sort of.
=+IF(ISERROR(FIND("F",UPPER(CONCATENATE($V17,$W17,$X17,$Y17,$Z17,$AB17,$AC17,$AD17,$AO17,$AZ17)))),I F(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER($R$2),"NA",""),"FAIL",""),"PASS",""),4)="FFFFFFFFFFFFF FFFFF","FAIL","PASS"),"FAIL *")
What this does is read the range of cells and immediately fails a quality check if it is in a critical cell, or fails if multiple non critical items fail.
I want to use an if/then to make cell blank if there is no data in one particular cell (which means no quality inspection performed). Here is what I tried:
IF(V18="","",+IF(ISERROR(FIND("F",UPPER(CONCATENATE($V18,$W18,$X18,$Y18,$Z18,$AB18,$AC18,$AD18,$AO18 ,$AZ18)))),IF(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER($R$2),"NA",""),"FAIL",""),"PASS",""),4)="F FFFFFFFFFFFFFFFFF","FAIL","PASS"),"FAIL *"))
The leaving the cell blank part of the formula works however it allows the remainder of the formula to only work if “fail” appears in the first cell it reads (in this case V18). If the “fail” appears in any of the other critical cells, it does not work at all.
Also as a side project not sure what the substituting to $R$2 does so if anyone knows how to clean this up, let me know.
How do I make a cell value update to equal the furthest value to the right in the same row?
This cell just needs to look along the right in its own row and grab the value furthest away to the right. It will be a letter value, not a number. eg, AB or AC or AD or AE. It will not (in this case) need to look further than about 150 columns or cells.
When I click a cell (say A1) I want a specific message to appear in another cell (say B1); when I click A2, a different message should appear in B1 (basically B1 will be my display zone). The reasons I need it as such are that the sheet is a calendar and so putting all of the messages in would look ridiculous, and I really need a static position (B1) for the messages.
View 9 Replies View Related