In range D2:D7 there are formulas with dates where message should return saying please check the date which is overdue but the codes does not work
In range D8:D11, these dates are normal without formulas and works with the code
DueDateFail.xls
If I change the code in Sheet1 to D2:D7 the codes won't work but do work with D8:D11 because the dates in D8:D11 don't have the formulas like D2:D7 has
I made a spread sheet last year that used various COUNTIF formulas that referenced other cells. =COUNTIF(I40:ER40,"A") It worked perfect.
I copied that spreadsheet to use for this year. I cannot get this formula to work. I have reentered in various locations and forms, but to no avail. If I enter the data and then enter the formula, it works.
However, if I enter the formula first and then enter the data (which I will do over the course of the year) it does not work. I even tried to reconstruct all of this in a brand new spread sheet and it still is not working, although last year's spread sheet still works.
what syntax shall I put in for more than one sheet for workbook calling sheets for due date
View 2 Replies View RelatedHere is what I would like to do:
Anytime there is a 1 in column B, I would like for columns G-I and columns L-N to display "N/A". I have tried all kinds of formulas, none of which will work.
I have no clue why these 2 formulas don't work. They show blank instead of reporting actual values: ....
View 11 Replies View Related1st thing id like to know. is there any way to inset a picture (not using the background button coz i dont want to tile the pic) and then be able to work ontop of it?
2nd is there a formula to change the order of a list depending on the number (high to low) in 1 of my other box's
3rd how do i take a result from one page and copy it automaticly to another?
I am inputting rows of data to test my spreadsheet and all of the sudden the formulas stop working??
Can someone take a peak at it and see if they can tell why its no longer working at I19?
I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
View 3 Replies View Relatedformula using if's and vlookup's to populate a calendar with x's for day a person is gone. however the last couple of people that are gone are not being marked on the calendar even though i use the exact same formula (except for cell numbers and such) for previous, working entries. Here is my formula
View 9 Replies View RelatedI am using the find function to establish the row number in a spreadsheet for an edit process.
Code:
wks.Range("B:B").find("something").Row
the cells in Range("B:B") are linked to another worksheet. If I copy paste values on the cells in Range("B:B") the code above works a treat.
Macro to clear cells with numbers but no cells with formulas with in this macro:
Dim i As Long
i = Range("E3")
If i > 0 Then
' Copy range
Range(Cells(6, 10 + i), Cells(500, 17)).Copy
Range(Cells(6, 10), Cells(500, 17)).Select
' Paste special
ActiveSheet.PasteSpecial Format:=2, Link:=1, _
DisplayAsIcon:=False, IconFileName:=False
' Clear i columns on the right
Range(Cells(6, 18 - i), Cells(500, 17)).ClearContents
End If
End Sub
The range is where the cells with numbers need to be cleared but not the ones with formulas.
Sheet 3 column c is where the formula is located - pulling data from b14master (sheet)
Worksheet attached
im working with a number of related cells and am having trouble with entering formulas. at the moment im entering them in manually but im hoping theres a better way.
suppose I have a few groups of data. the first group is A1-C1, second group is D1-F1, etc. the values of these cells have to be output somewhere else. so if A1=dog, B1 = cat, C1=car then AA1 would be dog cat car. AB1 would be the concatenation of the next group.
Now my problem is how do i put a formula into AB1, AC1 etc. I cant drag and drop. If I drag AA1 to AB1, AB1 will be the concatenation of B1-D1 instead of D1-F1.
As an aside one thing I thought of is to just put the output cells further apart. So if the first output was to goto AA1 the next one could be output to AD1. The only reason I didnt do this is this sheet is wide enough anyway I didnt want to make it any wider. I also thought of outputting D1-F1 into AD1 and then copy/pasting those values into AB1 but that wouldnt work as I need these values to be changeable.
I have 100's of cells with various formulas similar to this: =U7*9.57. U7 Being the cell with the number of hours worked and 9.57 being the hourly rate. Fairly simple so far. Now there has been a 5% pay rise so unless i get another formula to add 5% on to all these cells i will have to manualy change each hourly pay to the correct value, something i dont want to do. So my question is can i do something like =U7*9.57 then another formula to add 5% on to the 9.57, then i can copy the formula down all the other cells?????
View 3 Replies View Related=SUM((DATA!$F$2:$F$3000=A3)*(DATA!$N$2:$N$3000="N")*DATA!$J$2:$J$3000)
This formula doesnt work but =SUM((DATA!$F$2:$F$25=A3)* DATA!$N$2:$N$25="N")*DATA!$J$2:$J$25) works. Thats because all the other cells higher than 25 have a reference to another cell. How to solve it? Maybe with SUMPRODUCT / ISBLANK? See attached file.
For certain cells in this spreadsheet Excel's find and replace is not working:
For example, if I type the species "carex_filamentosa" it will say "...cannot find the data you're searching for"
My goal is to get all numbers that are negative in my A column to be positive.
I filter column A to show only records with negative numbers (there are now hidden cells) (simple example, A2=-1 , A3=2, A4=-3, filter so I can now only see A2 and A4). I insert a column (B) and do "=abs(A1)" and drag that down. This works to get the absolute value of column A into column B, for only the visible records. (so I still only see rows 2 and 4; B2=1, B4=3)
Now the hard part. I want to copy these numbers to column A, but only paste to visible cells. I highlight my data range in column B and hit Alt + ; to only select visible cells. Now I hit Ctrl + c to copy. If I then click on the first cell in column A that I want to paste to and hit Ctrl + V, the data is pasted in all cells, no spaces, filling in the hidden cells, which I do not want (in my example the 1 from B2 goes to A2 and the 3 from B4 goes to A3).
If I select my entire 'paste to' data range in column A (A2:A4), hit ALT + ; to select visible only, and hit CTRL + V to paste, it tells me "This command cannot be used on multiple selections".
AOther postings of seen seem to intimate that my first attempt described here should work (select visible only, copy, then paste), but doesn't! i can copy visible only easily enough, but when I paste, it ALWAYS pastes into all rows, hidden or not.
I've worked with Excel for many years and have just begun writing VBA in Excel 2007, so I am really stumped by this:
SR = 1
SC = 1
TR = 40
TC = 2
Worksheets("ChartData").Activate
' Works
ActiveSheet.Range("A1:B40").Select
' All of the following Fail w/1004 Error
ActiveSheet.Range(Cells(1, 1)).Select
ActiveSheet.Range(Cells(1, 1), Cells(40, 2)).Select
ActiveSheet.Range(Cells(SR, SC), Cells(TR, TC)).Select
At first I thought I had a bad install,
but this is happening on two manufacturer installed systems...
And I seem to be following the documentation for the use of Range with Cells.
Am I missing something?
i have the following code that works ok with single cells but wont work with merged cells
what is supposed to happen is the row is highlighted when double clicked anywhere between column 4-52 and reverts back if double clicked again
But this will not work if the cells are merged.
Also can the colours be changed to RGB colours and not standard colours
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count > 1 Then Exit Sub
If Cells(Target.Row, 4).Interior.ColorIndex = xlNone Then
Range(Cells(Target.Row, 4), Cells(Target.Row, 52)).Interior.ColorIndex = 4
Cancel = True
Else
Range(Cells(Target.Row, 4), Cells(Target.Row, 52)).Interior.ColorIndex = xlNone
Cancel = True
End If
End Sub
how i can display different values using the Selection.FormulaArray, ok look at my
Dim mySheet As Worksheet
' On Error GoTo errors
Dim x As Integer
x = TextBox1.Text
Dim y As Integer
y = RefEdit2.value
Set mySheet = Excel.ActiveSheet
mySheet. Range(RefEdit1).Select
Selection.FormulaArray = "=multiplie_value(""" & x & """, """ & y & """)"
MsgBox Selection.FormulaArray
Now the function i call:
Public Function multiplie_value(valor As Integer, multiplo As Integer) As Integer
multiplie_value = Val(valor) * multiplo
End Function
thats an example. so the real question is i create a form, the user enter some values to search to a database, the user select an area(a range) where they want the data to be display, but i dont how to display that info that is coming from the database in the range that the user selected. The info displayed in the range should look like a tabular report, column headers, etc.
the cells"A1" value = DISLCM864244984921MW#Q195FR "
how can splited the cell value just "864244984921" = "B1"
Have a huge data needs to count the 12 digi of numbers.
I try to edit comments in cells of a certain column and nothing happens. I am able to edit other comments throughout the spreadsheet, but when I select "edit comments" for these particular cells, no edit box is displayed for editing. It worked fine a couple of weeks ago.
View 11 Replies View RelatedI have two sheets and need information from Sheet titled Add-Del_chng positions to be moved to the sheet titled Cost Distribution.The issues here are :
1. The values in Cells A through J should be moved only if col Z value is 'hard coded"
2. The values should be moved to the 13th row.
3. This should happen every time the value is input into col Z of sheet titled add_del_chng.
Currently the macro does not run unless I go into the code and press play. I need this to happen automatically.
I often have problems with the cells range reference method. For some reason I get an error and can't tell why. Other times it works fine. Is this just an unstable method to use or is this completely wrong? I'm assuming I am using the wrong syntax. It is definetly the range reference causing the problem.
Dim cnt1 as integer, cnt2 as integer,cnt3 as integer
cnt1 = 2
cnt2 = 50
cnt3 = 2
dim myrange as range
set myrange = sheets("sheet1").range(cells(2,cnt1), cells(cnt2,cnt3))
I have no idea why it won't work. I'm basically searching a columner range of cells.
I have a worksheet where I first filtered, then hid some columns. Used to be that I just highlighted the area I wanted copied, clicked the select visible cells button that I put on my toolbar, ctrl c and ctrl v into a new worksheet and only the visible cells would be copied. Now the button isn't working because when I ctrl v into the new worksheet, it shows the hidden columns also.
View 9 Replies View RelatedI am trying to find a way to match formulas from two cells (not the value but the formula). I am creating a smaller excel test where they can enter a formula (C3) and directly see if it matches the correct answer (F3) All I have found is formulas containing value comparison.
View 4 Replies View RelatedHow do you force excel to recompute for the formulas in the cells. I know excel automatically does it however running the delay in recomputing the formulas is making wrong reference. I need to monitor the location of the cell using the match function.
View 2 Replies View Relatedway to 'insert' a formula into a specific range of cells using VBA only when the Value of $A$7 is changed to a value that does NOT equal "Custom".
Heres what I have so far using Worksheet change event....
Lets suppose i have 5 columns of data. 3 of the cols have "formulas" in them and 2 do not. I want to count the number of formulas that are in a given row. Is there a way to do this?
View 3 Replies View RelatedI am trying to move a group of cell containing formulas from one sheet to another, I have tried copy & paste/ ctrl & alt / paste special but for some reason the initial cell references of the formulas are not updated.
View 3 Replies View Related