I have a simple formula ='info page'!b2&'info page'!b5 in places that on my sheet adds a company prefix to a item number, prefix in b2 and item in b5, we have a client that we cant use prefixes but now when I leave the field blank its giving a zero and thats not good either. I'm pretty sure an IFERROR or something will work but can get the syntax right.
View 5 RepliesI am trying to count the number of times that data is in a field and in any field the date is greaert than today's date. This date field could be blank and that is where I am having the problem.
Example
See Attached
On my data tab I've got a column for "review date." Some of those cells are blank. When you go to the pivot table, the respective cell for that blank.review date cell displays the date 1/29/14. There is no data in the cell on the data tab, so why would it be displaying 1/29/14? I want it to either say "blank" or just be blank. It does this for every review date cell that is blank.
View 3 Replies View RelatedIn the following extract, I would like to have today's date placed into Col K when an entry is made into Col J.
Entry into Col J is from a validation list of 3 options. What I desire is that when one of these options is selected ("Complete"), then I would like today's date to be written into Col K.
Currently Col K has a validation list which comprises dates from 1st of each month from July to June.
I need a fixed date as text preferably, so was thinking the VBA function "Date" on a Change Workbook Sub.
The information is currently part of a Table in Excel 2007 that has about 500 records.
IdeasList
*
J
K
2
Status
Month Completed
3
In Progress
*
4
Complete
Jul-13
5
*
*
6
*
*
Data Validation in Spreadsheet
Cell
Allow
Datas
Input 1
Input 2
J4
List
*
=ValidationList_Status
*
K4
List
*
=ValidationList_Months
*
I have a macro that imports a report. If the Charge Type in column A is BTOREPLX, I need to prefill the field next to it in column B with the text "REPLX", e.g. "REPLXCDROM". I've attached an example of the report.
View 5 Replies View RelatedIn building my pivot table my data that I want to show in the column area is showing up as rows stacked on top of each other. In the column section I'm trying to show Total Budgeted Amount next to Total Actual Amount but on the layout it's showing the two stacked on top of each other is there some kind of hidden key that I'm missing?
View 3 Replies View RelatedPrice - Bulk Price - Total price
And i need to calculate the total price (totalprice = (PRICE OR bulkPRICE * 2.5)
If Bulkprice contains a number i need to use that field but if it's empty i need to use Price.
I have a tab called 'Tab1' in which I have column called 'Col1' and 'Col2'.
I have another tab called 'Tab 2' with columns 'ColA' and 'ColB'. When I enter a text in Col1(Tab1) I would like that text to be compared to data in 'ColA' from Tab 2 and when match found then get the corresponding value from ColB and autopopulate it in Col2 of Tab1.
in coverting these fields into a date field.
Example 91306 to 060913
I have encloed the file.
to run a macro that enters a new row after each set of currencies (which have been sorted). I now need to enter a total balance cell within this empty row which calculates the total amount for each currency.
I need to do this using the SUMIF function below:
SUMIF($J$3:$J$300,"L/C",$I$3:$I$300)-SUMIF($J$3:$J$300,"L/D",$I$3:$I$300)
the above basically lets me calculate the total but as no - and + signs were input it does this by recognising the 'dr' and 'cr' in the column next to the amount.
H2 formula is =OFFSET(A4,MATCH(MAX(D$4:D$1000),D$4:D$1000,0)-1,0)
I2 formula is =MAX(D4:D26)
If either of these result in a ZERO is it possible to leave the field blank?
I tried to read the post and figure it for myself, however close I have not been successful at eliminating this error(#div/0)in a blank fiield or field with a 0.
Below is the formula can someone please help with the IF portion that I can use to correct this statement. =SUM(B12:E12)/F12
=IF(LEN(I3),I3,J3)
at the moment if there is no result it equals 0
I thought this might work..
=IF(ISNA(LEN(I3),I3,J3)),"",LEN(I3),I3,J3))
I have a DSUM function with multiple criteria that are linked to user selections on a different sheet. If the user doesn't set a value for a particular field, the cell is blank and the DSUM should sum records with both blanks and non-blanks in that field.
Unfortunately, simply setting my criteria formula equal to that cell does not accomplish this. I've implemented an if statement to help but am having trouble defining a single criteria for returning everything.
Simply pointing to named range doesn't work for blanks and nonblanks when the cell is empty because it returns a zero: =SelectedValue
Criteria formula #1 I'm using: =if(SelectedValue="",="=*",SelectedValue)
Criteria formula #2 I'm using: =if(SelectedValue="",="<>*",SelectedValue)
When the SelectedValue cell is blank, these two formulas ensure that all records are summed. I would like a criteria that sums both blanks (=*) and non-blanks (<>*) without having to use two separate cells for the criteria. This gets unwieldy when trying to combine it with criteria for other filter fields.
I am trying to enter a blank row everytime the 2ND character of a field changes...
sample cells (ALWAYS COLUMN B)(last row needs to be calculated as it changes daily):
2T2W3D3L4H4N4N4N6C6C
when done:
2T2W3D3L4H4N4N4N6C6C
was trying this:
Code:
Dim chkConfirmRw, LastNameRow As Integer
For chkConfirmRw = LastNameRow To 1 Step -1
'Compare the current cell to the one below it
'If they don't match, insert a row row below the current Row
If Range("B" & chkConfirmRw) Range("B" & chkConfirmRw + 1) Then
Range("B" & chkConfirmRw + 1).EntireRow.Insert Shift:=xlDown
End If
'Decrement the counter and do it again
Next
but it does not work.
I have three search boxes in XL , two are search from and to dates and the other is search be ref.
I can currently search by dates and ref but would like to show all records in the specified dates if the ref field is left blank.
I am using the query writer and referencing back to cells in XL. I don't use the wizard or SQL. I have tried many options and think I should be using a OR statement with a wildcard but can't seem to get the result, does the wildcard just show the blank cells in the actual data?
I created this formula =G2*2.9%+.3
what I am trying to do is take the number in the G column multiply it by 2.9% and add 0.30. For instance if 20.00 is in the G2 cell, the number I want the formula to produce is .88
the formula works for me but what happens is the rest of my sheet that does not have any numbers in the G column gets filled with .30
How do I prevent the formula from calculating if the G column is blank?
I want to concatenate two Cells into a single cell BUT have the first field left justified and the second cell right adjusted.
A1 = "John Williams", A2= "Single"
A3 = "John Williams Single"
I am looking to adapt a piece of code (originally created by Ger Plante) so that it autofilters multiple columns of a table. I have three data validated lists that need to search 3 different columns in the table and filter accordingly, but also show all if no hits are made (hence why Ger Plante's code) was perfect in most respects. I would ideally like to keep the code as a Worksheet_Change event, but can deal with it being run as a normal Macro via a button if this is necessary.
[URL]....
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1")) Is Nothing Then
Range("A5:C5").AutoFilter Field:=1, Criteria1:=IIf(Trim(Range("B1").Text) = "", "<>", "=") & Range("B1").Text
End If
End Sub
How can I change the ....
View 14 Replies View RelatedWhere in this function would I put " " to return a blank field if false is returned?
=IF(D18>=1,(VLOOKUP(C18,C7:E13,2,FALSE))-(VLOOKUP(C18,C7:E13,3,FALSE))+(VLOOKUP(C18,C7:E13,3,FALSE))*D18)
I'm very new to excel. I need a formula to put in a column (I) that returns ONLY a five-digit zip code from the adjacent cell in column J, which is a full address. Nearly every entry is written differently, and many do not contain a zip code. I would like the cell to be blank if the cell in J contains no zip code .
I'm using the formula:
=MID(J5,MATCH(TRUE,ISNUMBER(-MID(SUBSTITUTE(J5,"","#"),ROW(INDIRECT("1:"&LEN(J5)-4)),5)),0),5)
This formula sometimes returns -**** format numbers, considering them to be negative and still five-digit. It also returns #N/A if no zip code is present, and I would like this to be blank. I'm sure I can nest the formula within an IFF, but have not been able to make it work myself.
Here's an example of what i want to do
A_________ B Frank Sinatra C Sammy Davis D Dean Martin E Joey Bishop F 3
I want to put "D"'s value 'Dean Martin' in column A - i know it belongs in A because the "3" in column F indicates its the 3rd answer listed i.e Dean Martin. This format would be the same where there are 4 possible answers on the column to the right dictates which answer is correct.
I have attached the spreadsheet for reference. All other sheets pull information from the WHITE sheet.
I want the columns for unit price, discount and total to be set to two decimal places but I also don't want it to show anything if the field has a 0 value (so if i only have a few items I don't get lots of o's).
To get the field to not display a 0 if it is empty on the WHITE sheet I have been using "0;-0;;@" which I found on the net. If I use this then it negates the two decimal places.
This also includes the WHITE sheet.
I browsed through the forum looking to find a way to solve my problem, but could not find it. Maybe someone here can help me out.
I have lots of data, and it would be impractical to do it manually. Here is an example: ...
I'm sure this will be a very simple one for someone - it leaves me *head scratching* though! I have attached a VERY generic example and it includes what I am trying to SUM for the given data. From the example if we look at Question Z1:
"Total Cost of All types of "Lemons"" = ?? I could simply do
Can I use conditional formatting to check if a field has a value or a formula?
View 4 Replies View RelatedI am having trouble developing a formula which will return a field (specific cells back to me) based on some conditions.
Basically I want a formula which will return the date (G3-T3) to me on each client. On the conditions of there usage.
So I want it to extract the value of the first number to comes across (the value of the number does not matter).
E.g. Gissle (D5) "The formula should go along and provide me with the date (value) "1/7/2013" [Cell I3) in cell [E5).
It just needs to hit the first value it comes across horizontally and provide me with the date (field) which it is located in Column E (E5-E11).
save at work because I had to manually scope out the first value and insert and copy the date (took an hour).
Sheet1 *DEFGHIJKLMNOPQRST2***Usage*************3*
Date*1/5/20131/6/20131/7/20131/8/20131/9/20131/10/20131/11/2013
1/12/20131/13/20131/14/20131/15/20131/16/20131/17/20131/18/20134
Client*************8**5Giselle*********5******6Elaine******4*********7Lidi**4*************
8Eliza****************9Ji***************410Erika****1***********11Maria********5*******12*****************
inserting a formula in PIVOT table field....
View 9 Replies View RelatedI import a database with mostly unique records which has a description field that includes, among other things: Model Name, followed by predominant color. Unfortunately, there are no seperate fields for the Model Name or Color, e.g.:
DESCRIPTION
8070 CALLING WATER RED
508001 EYEDAZZLER LILIANA CARAMEL
502034 EYEDAZZLER LILIANA RED
#402022 DIAMONDS IN THE DARK RED
402024 DIAMONDS IN THE DARK PALOMINO
Is there a way to create a summary table which would count the number of occurences of the Model Name and the number of occurences of the color?
Fortunately, there are no more than 25 Model Names, and no more than 10 colors or color/combinations, so we could compare against two lists.