I understand that if I want to do sumifs with an "or" condition, I do something like
=SUM(SUMIFS(A:A,B:B, {"Condition1", Condition2"}))
I have a lot of conditions, so I'm wondering whether there's any way I can read the the conditions from cells on a worksheet, so I don't have to type them in manually. In other words, I'd like to have the things inside the "{ }" be cell refrences.
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
View 2 Replies View RelatedI recently added formulas to the attached worksheet where a cell is linked to a "true" and a "false" which are linked to check boxes. When "true" is activated the cell with the formula will = "HOLD" and when "false" is activated the same cell will ="RELEASE". Now, when i saved this worksheet and tried to reopen it a dialog box popped up that said "the content is not readable.... excel will try to recover the content" (or something like that) so when i click "yes" to the dialog box then the sheet opens fine , however, when i save the sheet the same box pops up? Maybe there is something wrong with my formula because before i inserted the formula to the sheet the sheet worked fine.
See attached TEST1.xlsx
I have a worksheet with 7 columns and I would like to read this into a 2d array. The worksheet can have as much as 50k rows.
1) is it possible to read 50k rows into a 2d array?
2) is it more efficient to read data into an array for access/manipulation as opposed to a worksheet?
3) can someone show me how to read in a worksheet with 7 cols to a 2d array?
I have setup a userform to record fuel issued to vehicles. I have a text box which I would like to use to display the last meter reading from my fuel pump. This data is stored in the last cell in column F of "Sheet1" workbook. Is it possible to read this data into my userform so when it starts it displays the last pump reading?
View 2 Replies View RelatedI'm trying to access/read data from a hidden WorkSheet in Excel 2003 using:
Application.Worksheets("Hidden Sheet").Activate
With ActiveSheet
**** Data ****
End With
The accessed **** Data **** refers to another ('unhidden') WorkSheet. How can I read the data without making the WorkSheet visible to the User?
I want to read data from Notepad into Excel. I found this code on the net and have been trying to modify it for my needs:
Sub ImportText(FileName As String)
Dim X As Long
Dim FileNum As Long
Dim TotalFile As String
Dim Lines() As String
Const DataRowStart As Long = 1
Const DataColStart As Long = 1
FileNum = FreeFile
Open "C:...data.txt" For Binary As #FileNum.................
This pastes the contents of each line into 1 cell but I want to paste each value into a seperate cell. So in the attached data.txt there are 5 records each of 2 lines. For example in the 1st record I want to paste 05-693-1900 into 1 cell then 0040 in the cell to the right of that, Town A into another cell, 000000 into another cell....and so on. For the 2nd line it should be 000000000033 into one cell, AA28816 into the adjacent cell...and each remaining number into a seperate cell. I also want to leave 3 blank lines before going to the next record.data.txt
I'm trying to open a file on a network drive...but I'm getting the following error message when it opens: "This file may be read-only, or you may be trying to access a read-only location. Or the server the document is stored on may not be responding." Now, the file itself has no rights restrictions and is not read only. It doesn't appear to be locked.
Now, there are other Excel files in the same directory which I could open fine; however, the Excel documents having the above problem all have a little black icon "appears to be a padlock" (image attached) at the bottom left hand side of the Excel file icon. I tried the following:
- Renaming
- Converting to a different file format (didn't work, it won't let me)
- Opening in notepad...etc doesn't work.
This file is dated back in 2004...do you think it's corrupt? Is there anything i can do to open or recover this?
I've been trying to use IF OR and CONCATENATE to read 4 cells and give me a list of parts needed based on the data loaded in the 4 cells. I attached an example showing.
View 2 Replies View RelatedExcel 2003 > I am developing a comprehensive VBA function that needs to read the value of many cells in a worksheet. I don't want to Name all those cells because then I will need to pass too many variables to the function. I can use the following set of objects and properties ...
View 6 Replies View RelatedIs it possible to create an individual variable time clock that is only useable by a specific workbook?
I have set up a calendar which has all days of the week in column 1, with start times in column 2 and end times in column 3. These times are adjustable on a daily basis.
Is it possible to create a macro that reads through the sheet and tells the workbook to disregard the system clock and use only the start and end times for each date stipulated in the table.
ie 7/9/06 has a start time of 6am and a finish of 6pm, not 0000 and 2400 as it would with the system clock?
What I'm trying to do is determine what the content is in a particular cell. In the spreadsheet, some of the cells are merged, some not. I'm using ActiveCell to read the content of the normal cells, but its not working for merged cells.
View 9 Replies View RelatedI have a combobox "cboRCAname" that is populated with a list of names from a dynamic range "RCA_Name" in a worksheet.
If a user selects an item in the combobox from RCA_Name, I want to display the a code that corresponds to that name. The codes are stored in a coulmn adjacent to "RCA_Name"
'Populate the combo box with the dynamic RCA list
Dim rngRCA As Range
Dim ws As Worksheet
Set ws = Worksheets("RCA_Data")
[Code] .....
I made the formula work great, however I want to cut it down and simplify it and make it easier to manipulate... as opposed to having the filename written every so many times.. replace with a loop instead.... so:
Private Sub Weeklytestt_Click()
Dim varCurDate As String
If DateYesterday.Value = True Then Answer = "1"
If DateToday.Value = True Then Answer = "2"
If DateTyped.Value = True Then Answer = "3"
Select Case Answer
Case Is = "1"
varCurDate = Format(Date, "yyyymmdd")
varCurDate = varCurDate - 1
Case Is = "2"
varCurDate = Format(Date, "yyyymmdd")..........
I had attached the excel sheet below. I need to have a macro which will be able to help me to deliver the result ( as attached) as per the raw data (as attached ).I have been cracking my head as there are more than 1000 item for me to be sorted by me daily and several reports with almost the same issue.
description-i will need worksheets to be created for each of the available status on the report and have all the details to be pasted accordingly to the status.this is meant for excel 2003.
I am trying to rank a series of data. I have most of this figured out. However, the data that is being ranked is based on a sum of a range of cells. I have lines that are waiting for new information to be put in related to the values to be ranked and the value is reference a sum of blank cells currently since there is no data there and it is showing up as zero and therefore being ranked number 1 in my list. I basically want the ranking to rank the values 1-11 while putting all values that are zero to be put at the bottom of the ranking. Any way to do this without macros. I have been using the VLOOKUP and RANK functions for my ranking purposes.
I have tried using an if statement that made the cells #N/A if they were zero, however they remained at the top of my ranking.
I have a macro that copies time cells from another sheet and puts them in cells in another sheet, I also have another macro that takes 30 mins away from that time if a certain condition is reached but it wont work unless i retype over the copied cell.
View 14 Replies View RelatedI have three workbooks that contain various types of information. I have an Overtime workbook that lists employees and calculates the straight time and overtime worked for the day. This is the main log that information will be pulled from.
I need writing a macro that will copy over information to an Absent Log workbook and a Production Model workbook.
The Absent Log workbook:
This workbook contains worksheets from each month. I need excel to do a lookup of the name in the Overtime workbook and copy the information from the "OT" column but if that column is empty I need it to pull the information from the "Personal DT" column.
The Production Model workbook:
I need this workbook to automatically pull the total straight hours and overtime hours to the production model for the correct day. This will also pull from multiple sheets.
I am going to be writing this in Excel 2010 but it will be primarily used in Excel 2003.
I uploaded the workbooks to filefactory.
OT Workbook: [URL] ......
Absent "Log" workbook: [URL] .....
Production Model: [URL] .....
I’m trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. I’ve searched and found similar forums here, but since I’m ignorant in VBA code, I couldn’t manage to make adjustments. So if you can help me with the code, I would really appreciate it!
Condition and Results required would be:
IF column C = “S” than on Column B = color cell light blue with white border
IF column C = “P” than on Column B = color cell Green with white border
IF column C = “A” than on Column B = color cell Yellow with white border
IF column C = “L” than on Column B = color cell Red with white border
IF column C = “C” than on Column B = color cell Dark Blue with white border
IF column C = “ ” than on Column B = color cell white
IF column C = “V” and has white background than on Column B = color cell white
IF column C = “V” and has a Dark Grey background than on Column B = color cell Dark Grey
I don’t know if the last two are feasible.
Display Alert On Closing If Cell Not Filled In
How do I alter this code so that it acts like an INDIRECT function so will pick up a cell reference in another cell?
I can't just use A1 as I want this to change dynamically.
I am trying to query a names sheet where each row may contain more than one occurence of a member ID. There are no duplicate rows ( records), because the dates are different for all rows.
1. I want to retrieve all of the rows from the names sheet that match a unique member ID. So far I only get one row.
2. And if mistype the number, it gives me a wrong record .I would like to get an error message that if I don't get a match, that it appears in the somewhere like dialog box or even entry in one of the cells that no name exits.
I have four worksheets in my project and I would like to protect one of the sheets so changes can't be saved. In other words, I would like to make that specific worksheet "read-only". The other three sheets I want changes to be saved with the document.
View 4 Replies View RelatedWith a range of cells (a1:ao41) I want to color several cells depending on the value of that particular cell and another color when that particular and a corresponding cell have both the same value. It's some sort of sociogram The first row and column both contains names of students. Let say the students have to choose the three most populair ones to work with and the three most unpopulair ones. Positive ones get the color blue, negative ones get the color yellow. That's easy to do with conditional formatting. But now comes the tricky part. If for instance Student 3 chooses Student 7 and Student 7 chooses Student 3 the color of both cells have to be green if both positive and red if both negative. That's also do-able with conditional formatting, but it most be much easier to do with VBA. I just don't know how .
View 5 Replies View RelatedHow do you highlight a row when conditions in two cells of that row are met? For example, I have a spreadsheet with a STATUS column ($A) and a TYPE column ($E). When the status is "open" and the type is "1", I want the row to be green. But when the status changes to "closed" I want the row to be a different color or no color at all. How do I do this?
View 3 Replies View RelatedI have found several forums with this question but none of them seem work/fit my needs
What I need to do is look in Sheet1 at column 'B' begining at row '15' (row 14 is table header) and if the cell contains "Yes" then copy 3 cell directly to the right (C15:E15 in the case of the first row) into Sheet2 into cells B15:D15 then do the same for row 16 and so on up to row 600
Creating a cut down copy of the Sheet1 which does not contain the row which determines if it should be copied.
I have a spreadsheet which I need more than 3 conditions to the formatting - is there anyway of doing this in VBA!!! Its for a holiday chart that i'm doing - some of the items they can enter into one cell is: B (Bank Holiday) / 1 (1 days holiday) / TR ( Training day) / SL (Study Leave) / 0.5 (half day hols) and more.
View 2 Replies View RelatedI have a Macro that when run checks for the lowest value in a line & highlights that line. It works just as it should, infact it works better than I had planded. When I update a cell value and move to the next cell it changes the highlight.
Option Explicit
Sub SELECT_LOW_PRICE()
Range("a4:i126").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=and(a4=min($a4:$i4),not(isblank(a4)))"
Selection.FormatConditions(1).Interior.ColorIndex = 6
Cells(1, 1) = "Updated " & Now
Cells(1, 1).Select
End Sub
I am getting a "Run-time error:'13' Type Mismatch" when we try to paste a value (in this case "Y" or "N") in multiple cells at once. I did look up this error on the microsoft site and found this. http://support.microsoft.com/kb/821292. which didn't seem to be the exact issue I was having because I am not using a macro.
Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
Call ChangeRowColor
If Not Intersect(Target, Range("$A:$E")) Is Nothing Then
Select Case Target
Case "y", "Y"
icolor = 4
Case "n", "N"
icolor = 3
Case "?"
icolor = 6
Case "n/a", "N/A"
icolor = 13................
I have a VBA code which checks the value of two cells in "Sheet2", and when these values are in the fourth column of "Sheet4", then dictionary is saved and after that VBA delete Entire Rows in "Sheet4" which consists these values.
Please find the code below:
VB:
Sub dictionary1()
Application.ScreenUpdating = False
Dim dico1 As Scripting.Dictionary
Set dico1 = New Dictionary
[Code] .....
Now I wonder how to add more conditions like for example: "Delete all rows in "Sheet4" which have for instance in the 6th column text "Hello" AND which have in the 7th column value greater than 10". How can I change the existing code?
This is of course just an example, but the underlying question is how can I add to these dictionaries more conditions. Of course, we can still use "Sheet2" to add some value which we want dictionary to store.
I want to use for this only dictionaries because spreadsheet is large and filtering doesn't work at all...
So I need highlighting certain cells. I have a sheet that keeps track of people's points over a series of races to determine who will make the "team". The team is selected one at a time based on the most points, starting with the leftmost column and working right. What I would like to do is highlight the highest score in each column (which I know how to do) but then tell the sheet to ignore all the other numbers in that same row. That way if the person with the most points in column one (M in my sheet) also has the most in column two (N in my sheet) Excel will ignore that number in the second column and highlight the next highest number.
I'll attach my file so there is a visual reference : ODN Points.xlsx‎