I have 12 different sheets named April-06-sales through to march-07-sales. I need is to be able to search all of the sheets and check if the invoiced paid column is blank and if it is copy that row into a new sheet. All of the sheets have a different number of rows.
View 2 RepliesDoes excel have a formula to check to see if every row in a specific column is blank and if it is then set the value?
View 10 Replies View RelatedI have an issue on manipulating the data in my worksheet below. I need to update column 'remark' when the customer has pay fully. Example customer 1003 and 1004 both pay completely so I need to update done in column remark. For customer 1002, because not fully pay, so cannot update done.
customer inv no invoice payment remark
1002 A12 100 100
1002 B45 120 0
1003 W12 90 90 done
1004 F12 30 30 done
1004 F17 45 45 done
May I know what function or coding can achieve this?
I know I started a thread that contained this question before, but, I am not sure why I didn't get any idea's how to fix it, I'm not sure why. I would really appreciate anyone's help and or suggestion.
What I am trying to do is - From a command button, prompt an input box, What you enter in that input box becomes what is searched for.
This is done however, what I want is the characters I enter in the input box I want it to look anywhere within that cell for that string...
Example - If I type "Empty" in the input box, the macro will go through and look through my range of cells and find all occurances of "Empty" - copy that row, and paste it to another worksheet --->which is good except I need it to also find "Empty Jug" or "Huse Empty", and include those as well...I can't figure it out.
Basically I have two groups of data
Group 1 is machine info and has
Serial Number Build Month Commission Month
Group 2 is warranty info and has
Serial Number Service Claim Value of Service Claim
What I need to do is for all machines built in a given month count the number of claims within a given number of months after commissioning and the total value of the claims.
Eg
If I have a machine serial number abc123 built in jan 07 and commissioned mar 07 I need to search the table of data in group 2 for the number of claims between mar 07 and may 07 and also the total value of those claims
We have agreed maximum rates with suppliers for certain services and I'd like to check that the rates they have invoiced fall below the agreed maximum.
I've attached an example of the data I'm working with. What I'm after is a formula that will cross check the details and rate charged on the 'Invoiced' tab against the three 'rate card' tabs and generate an output that flags any discrepancies. I've added a column called 'Validate' on the 'Invoiced' tab where I'd like this formula to go.
All the data should match with the exception of the shift which is listed as a description on the rate card but is a concatenation of the job role and an abbreviation of the shift (D = Days, N = Nights/ Saturday and O = Sunday/ Bank Hols).
I'm trying to calculate the amount of money invoiced per company per month. In column B I have the date the invoice was issued. In column E I have the Company name. In column G I have the amount invoiced.
View 6 Replies View RelatedI have a number of rows that contains data (normally numeric):
0 | 0 | 0 | 0 | ... |True
1 | 0 | 0 | 0 | ... |False
0 | 1 | 0 | 1 | ... |False
What's the formula that gives the True/False answers? I could go IF(AND(A1=0,B1=0,etc...),TRUE,FALSE) that gets the results, but can it be done in a smarter method or can it done via array method?
IF A1 on sheet one matches the value in A1:A500 on sheet two, to display "Yes" in the cell on sheet one column B.
View 2 Replies View Relatedcode to get to know about the sheet in a worksheet is blank and then delete that.
View 5 Replies View RelatedI'm trying to scan a column of data, example column H, for empty cells, if it's empty then skip it. This doesn't work, because cell.Value = " ":
If cell.Value 0 Then
'Extra coding
Else
End If
When a file is open, if cell A1 has a text "Hello there", then I want to make this cell add a blank space as "Hello there ". Let's save this file and close. So now if I re-open it, I want cell A1 to remove the added blank space and revert to be "Hello there". This take turns to change back and forward.
View 8 Replies View RelatedHow can I write code to test if a cell is blank? My code is using "isempty" but I keep type mismatch error because my cells are filled with formulas that make the cells blank. It only works if the cells are truly empty with no formulas.
View 8 Replies View Relatedi attach my sample excel file here, book1.xls. sheet1, "input". it check the column "C" row by row and if it detect the different content data in next column, it will auto insert a new row. it will ignore the space. below is my coding:
View 3 Replies View RelatedI have a vlookup formula I would like to add another If statement to.
=IF(ISNA(VLOOKUP($O2,Table!$A$2:$I$72,9,0))=TRUE,"Not In Table",VLOOKUP($O2,Table!$A$2:$I$72,9,0))
This works fine, but if the field that it is returning (9 in the case above) is blank, I get a zero in the destination cell.
Can I add another check in this formula to see if the destination cell is zero, but display a blank?
See attached spreadsheet for example of the sheet I am using.
Problem 1.
Columns C and D show me figures when the number in col A is > 2500 or < 550 when these criteria are not met a 0 is displayed in the cell, is it possible to alter my formula so that nothing (a blank cell) is displayed rather than a 0 - I am wondering if there is something like null which I can use?
=IF(A4>2500,A4,)
=IF(A4<550,A4,)
Problem 2.
In column E (normal days) I want to display the value of A if it is > 550 but < 2500 - is there a way of doing this in a single formula? Again I would just like a blank to be displayed if the value of A does not meet the above criteria.
I have X amount of cells (formulasheet) , these are used to input data, which is then summarized into a sheet on the same file. I'm trying to find a code that forces the user to fill out all the fields. If the user do not, the "submit button" will not proceed, at least not work. Almost like a forum registrating, where it is required to fill it all some mandatory fields in order to proceed.
The cells datatypes are mixed, some are Integer and some are String.
Is there anyway that are code can be written "if the cell(s) is FALSE (nothing in the cell) then display message.
I am building a table for staffing purposes, and I basically only need to controls to "validate"/approve the table.
1. The number of staff put in must equal the required amount of staff (E column)
2. The same staff cannot be put on twice for the same time period (which I have done by the OR(F7=G7,F7=H7,G7=H7) as part of the validation)
My problem is that sometimes I need 0 staff (when its closed), but when I leave it blank obviously it wont approve the table because of rule #2 above, since it thinks the blanks are the same staff member.
I have tried with some ISBLANK statements, but it wont work.
In Worksheet 1, Cell B63 I would like to create a drop down menu, with two options for the user to select - 0.05 and 0.01. I would like each selection to then control the formula in the cells C63:L63, for example;
Where 0.05 selected it computes for cell C63:
=IF( COUNTA(C4:C54)=0,"",IF('WORKSHEET4'!N55>'WORKSHEET4'!L55,"YES","NO")
then cell D63
=IF(COUNTA(C4:C54)=0,"",IF('WORKSHEET4'!AC55>'WORKSHEET4'!AA55,"YES","NO")
and so on
Then if 0.01 selected it would compute for Cell C63
=IF(COUNTA(C4:C54)=0,"",IF('WORKSHEET4'!N55>'WORKSHEET4'!K55,"YES","NO")
then cell D63
=IF(COUNTA(C4:C54)=0,"",IF('WORKSHEET4'!AC55>'WORKSHEET4'!Z55,"YES","NO")
I would like a macro to do this...If a cell in column G is blank and the cell in the same row in column C is NOT blank, highlight the blank cell in column G Red.
I need the search to stop ONLY when it gets down to the bottom-most row of data in column A.
Note: Any row headers will always be in row 1 only
Current...
Got a table where I've placed Data validation - List on them.
Placed a macro on a button so that the data on the table where the validation list is placed is transfered to another sheet (same workbook).
What I need is a to modify the macro assigned to the button so that when the button is clicked to activate the macro:
a.) Can identify if there are any blanks on specified cells with validation list
b.) Stops the macro from copying the fields to the other sheet
c.) Alert the user of the blanks (an alert stating there is a blank would suffice)
d.) Alert should preferably be similar to a Stop alert from Data Validation.
Can anyone help? Been reading through several threads but don't seem to find anything that could help me - tried reading a Visual basic thread and now i've got a headache....
with some basic code. I want to check the activecell, if it's blank then copy the contents from the cell above, if it's not blank, leave it & go down to the next cell & repeat until last row.
View 7 Replies View RelatedI have two excel sheets. The first lists all orders submitted. The second lists all orders paid, or pending payment. I would like a column in the first sheet that would show if that order was paid, by pulling the info from the second sheet.
View 14 Replies View RelatedI am trying to sum rows based on unusual criteria: i.e. A table of date c2:t9 contains details of expenditure for 16 periods for insurance and motor expenses. i want to convert this to a cashflow format and reflect when the item is actually paid, so if it is paid in february, march, etc (given in the original table) I want to add the totals in the months preceding the set date. also, if the criteria is a number <12 then i want to add up the preceding amounts if the period number is a factor of that number e.g - criteria is 3, then i want to add rows 1-3 and put in period 3, 4-6 and put in period 6.
View 6 Replies View RelatedThe below piece of code carries out a vlookup on a defined cells value and produces a result in sheet one, however if the column index number in sheet 2 (Database) is empty the result 00/01/1900 is produced.
I'm not sure how to say leave the result blank if the column index number is blank.
Excel 2010
Userform = Tab 1
Database = Tab 2
Just need to delete some hyperlinks in column A on 50+ worksheets. Thought a loop through all the worksheets would do it. Only works on active sheet. Forgive my ignorance, don't really even know where it goes, once it works - module or workbook?
View 2 Replies View RelatedI am using a Uderform in order to check if some cells in several columns are higher or lower than values I set in my textboxes.
However, I think I am having a mistake in my code (it runs but doesnt return the correct informations) as I have blank cells in certain columns.
Concretely I am giving 1 point if the criteria is valid (if for example the value in the cell is <= 1) but some of these criteria are 1 when they should be 0.
As I said it runs but I need to add a fix for empty cells / blank cells and if possible N/A error cells in this so that the code gives 0 to the criteria and moves to the next column (next select case)..
I have attempted to alter this example to simply check if the cells are not blank by replacing the "X" with "<>", but I get errors in the SMALL function--it looks like the IF statement is just returning an array with all of the values set to FALSE, and none of the values in the array call out a location of the next filled cell.
How could you alter your fixed XLSX file to check for non-blank cells instead of "X" specifically?
I want to create a spreadsheet that I can export my transactions from my credit card onto -- is there a way to make it so that the transactions that haven't been covered by my most recent payment(s) are red, while the ones that are paid are green without manually going through & doing it? I know there's the IF, TRUE, FALSE formulas, but I'm confused on how to use them.
Basically, if I spend $1,000 between 5 transactions and make a $400 payment, I want the oldest transactions totalling up to $400 to turn green, while the remaining are stay red until a new payment is posted.
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")