Count all A's that are also Matt and add up the corresponding figs in J. If use the formula above to 179 (where there are values in the cells) then I get the answer 170 which is correct. Currently with the it set to review all cells from 6 to 300 which includes blanks then it returns #value therfore I assume it is not coping with the blank cells.
I have a spreadsheet that shows a dash in some blank cells and I can't get rid of it. Cells are formatted as general, there's no event code, no conditional formatting, no data validation, no auto-formatting, no nothing. I can copy a cell from outside the range with no dash in it and paste it into one of the cells with the dash and the dash remains. I can't figure it out.
I have a row of 2900 single letter (middle initals) however 222 users have no middle inital. this is a password scheme and need 7 digits, without the middle inital i only have 6. so I want to replace all 222 cell that are blank with a dash can this be done without doing each by hand?
I want to make all the cells that are outputted as blank by my formula a certain color to distinguish them from non blank cells. my current formula: [B1=IF(A1=0,"",A1)]. my guess would be to use an [AND] function but I do not know how to command a cell to change color without using the toolbar.
I need a formula that will search the range D8-D100 to confirm that all cells within that range are either 11 characters in length or blank. I will use it inside of an error message that will look something like this:
=IF(****formula that checks to make sure all of the cells in that range are blank or 11 characters****=TRUE, "", "Please make sure that all cells are 11 digits or blank")
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.
I am looking for a formula that will work with my current formula, and check that cells a1 and b1 contains information. If they do contain info then the original formula should carry on as usual and leave the answer in cell c1. I also need it so when no information has been entered in cells A1 and B1, C1 will be blank instead of containing a zero.
This is the formula I have at the moment: =IF(SUM(A1=0,B1>0),B1-A1,"No Charge?")
I have 2 worksheets, i have one sheet that has a form with lists, when nothing is selected the cells are blank. On the other sheet I have a few cells which = some of the sheets on the other worksheet. when nothing is selected, the cell displays 0 even though the cell its supposed to be displaying is blank.
I have a spreadsheet that i want to create a part number that contains 4 parts. Ex 1rl9. The cell i want this to be in is blank. What formula do i use to make the second character in the blank cell = r?
Any way of shortening the attached code. The first part 'Sub Loop1()' works great but I can't figure out how to shorten the second part. As you can see the distance between columns is always consistent i.e. add 5 columns to find the next outcome to work out the next result. Ideally I wanted to somehow loop through the next 5 columns again and again until the cell is blank.
First, I can get the balance formula, and when I drag the square at the bottom right, I am able to drag the entire formula down...I want to do that. However, when I drag it down, the number keeps popping up. I was wondering if there is a way to be able to drag the entire thing down and be able to make it so that it is hidden (the cell is blank) unless the debit or credit cell in the corresponding row is filled. Also, if possible, I want to be able to drag it down to infinity, so it can go on forever, so that there is no need to always drag it down when I add more things in it. I am attaching what I have till now.
In excel if you press F5Special, you will be given to select cells with some criteria but there is no criteria to select cells with zero value in a selected range. Is any such option is available in excel or a macro is needed for the same?
I have a column of contacts. Each contact has data spread out over many rows, most of which are blank. I would like all of the data spread out through the rows to be in one cell, delineated by commas (so I can import into another program).
Concatenating the cells works except that I end up with 20 commas for two strings of info.
Just so you understand I may have this: Mary red blue yellow purple Rob blue purple Trey yellow
and I want it to look like this
Mary red, blue, yellow, purple Rob blue, purple Trey yellow
What I would like to do is to fill in B1:B3 with A0001 and B6:B9 with A0002. The number of rows in each set of data will vary, but the identifier (A000X) will always be located in the row in column B adjacent to the last entry for that set of data in column A and there will always be empty cells between data sets. The number of data sets will also vary.
I have a small challenge, where I am trying to fill blank cells with the data from the last populated cell above.
However, I need it to be able to automatically recognise when a manual entry has beed actioned down the column and recalculate for below that cell with the new data.
The data in the cell is selected from a drop down. I want it to populate all the cells below, until the next drop down is selected. And automatically repopulate them all when it is selected.
E.g. Cell C8 has the value "DATA" selected, and everything below is autofilled with "DATA", but when I select cell C12 with different drop down list value "VALUE", all the cells below stay as "DATA" because they are obviously no longer blank.
What I need if C9 - C11 to remain filled with 'DATA" and from C13 to auto fill with the newly selected value "VALUE".
Need this to happen for as many rows and data selections from the drop down as needed (endless).
I have used:
Code: Sub FillEmpty() Application.ScreenUpdating = False Application.Calculation = xlManual Dim cell As Range For Each cell In Intersect(Selection, _ ActiveSheet.UsedRange) If Trim(cell) = "" And cell.Row > 1 Then cell.NumberFormat = cell.Offset(-1, 0).NumberFormat cell.Value = cell.Offset(-1, 0).Value End If Next cell Application.Calculation = xlAutomatic 'xlCalculationAutomatic Application.ScreenUpdating = False End Sub
which does the auto fill, but won't change the cells below once one of them has been updated.
Drop down list has 31 possible selections at the moment
Starts at Cell C8
Needs to be flexible enough to add an indeterminate number of rows, and additional items to the drop down selection.
I'm currently using some pretty extensive conditional formatting macros on a worksheet (this functionality is working fine when data is directly entered into the sheet). But when i paste data from another worksheet into my "formatted" sheet, the formatting does not take affect. is there any way to "refresh" a macro?
I'm using code based on the following post: Add Borders To Cells Of Spreadsheet From Access. That code was developed for a relatively small recordest. I am using the same coding style to transfer a recordset that has roughly 2000 records in it. Since this is all taking place over a network there is a period of time (about 15-20 seconds) where Excel is open and the user is watching the cells get populated from Access. If, during that period of time when Excel is open, the user clicks in a cell during the population of data, it stops the process and causes an error on the Access end (Error 50290: Application-defined or object-defined error). Is there a way I can prevent user input (perhaps prevent signals from the keyboard and mouse) until after the data has been transferred? I know this is a long way to do a TransferSpreadsheet command but I like the way the column headers get formatted as well as the column sizes. I also think it's pretty cool to watch the data populate as it goes.