Attached is a worksheet to show...
1 - D2- because the supplier has no address it shows up as "0" can you guys help me fix that to show nothing?
2 - Can you guys help me build a formula for E8? Right now I have it as $C8*$D8 it works fine until C or D is empty it gives me the #VALUE because I have a formula in D. Because of the #VALUE my formula in E14,15,16 does not work.
I've got a couple of formulas for doing in single step:
B A
A B
F D
A F
A #NUM!
D #NUM!
F #NUM!
D #NUM!
but I'm unable to avoid those #NUM!
I have a perfect formula
=IF(A2>1,A2/1.16,"")
It does exactly what I need , it does not calculate on empty rows , but it calculates on heading rows , how can I modify to avoid this.
See file attached , I don't want the value on b6 when I drag downwards.
07 09 value avoiding.xls
I have a sheet that requires me to press F9 each time I open it to re-calculate all cells. Why do I need to do this on this 1 sheet? A few months back it was fine and didn't require the extra attention.
View 2 Replies View RelatedI am multiplying cells and copy down the column to make it easy.
Cell D17, has "=A17*B17" in it. This is fine when there are numbers to compute, but when there aren't, D17 will display "0". How can I have cell D17 remain empty if nothing is put into A17 and B17?
I am trying to avoid double counting two separate rows with the same columns. For example:
StockFund AFund BFund CFund DFund EFund FFund GCountDesired Count
LKOH111100044
LKOH001111042
If I have two stock codes the same in column A (not necessarily one below the other), I only want to count one of the occurrences per fund. So I would like to avoid counting the duplicate records for funds C and D. The desired outcome for the total holdings of LKOH should be 6 and not 8.
Let's say I have a sheet called Category. Column A contains code; column B contains description. Now I have another sheet in which either column F contains a category code or column G contains a category description. I want to look up the code in column F if the description is given or the description in column G if the code is given. I thought I might be able to do something like:
in F2: IF($G2<>"",INDEX(Category!A:A,MATCH($G2,Category!$B:$B,0)),"")
in G2: IF($F2<>"",INDEX(Category!B:B,MATCH($F2,Category!$A:$A,0)),"")
(and then copy the formulae down the rest of the columns of course). In other words, if the cells were both empty then there would be no common dependency; it would just set them both to empty. However, if I provided either value then it would overwrite the formula in that cell so there would be only one formula left and nothing to be circular dependent about.
However, it must be looking at the condition itself and seeing the two columns in common because it is coming up with a circular dependency. I thought I might be able to trick it by having IF(INDEX($G:$G,ROW())<>"",... but it still doesn't work.
This sort of situation must crop up from time to time. How do we get around it?
I have the formula =today()-g3 which works fine to let me know how many days a loan has been out. However when there is no date in column g i get #value! errors. Can anyone reccomend an alteration to the formula to avoid these errors showing up as they dont look great.
View 2 Replies View RelatedHow do I avoid using arrays? I am reaching Excel 2003's limit.
Our workbook has many worksheets, each with tables generated from queries that have multiple columns. On the first sheet we have arrays that refer to the other worksheets. Such as:
{=SUM(IF(('Prime Change Orders'!G$2:G$10=C17)*('Prime Change Orders'!D$2:D$10
I have a worksheet of Users with columns pertaining to first, middle, and last names. I want to allow duplicates in each column but not a completely duplicated record.
View 9 Replies View RelatedI have a simple spreadsheet that will be used by others for records management (rehabilitation case management). I have entered the code for most of the 'stuff' that I need to make the workbook easy and simple to use, and get the data back to head office each month.
Because the users will be emailing their records and I haven't yet worked out a way of sending just the "open cases", I need to keep the workbook/worksheets small (and to maintain good programming).
In column L, I have copied this formula from L2:L501:
=IF(ISBLANK($K2),"",VLOOKUP($K2,LOCSTAT!$A$1:$N$299,2,FALSE))
Column K is where the case-managed person works and so column L returns the geographical location value. Obviously it's blank until the case is opened by starting a new record and entering each field with data. For example, the user can select " Finance" from a validated drop-down list and the adjacent cell (to the right) will look up the value and return "Parramatta".
How do I translate the cell formula to some VBA?
I suspect that the static VLOOKUP range in the formula (LOCSTAT!$A$1:$N$299) will need to be dynamic range as well - I can follow the instructions for a single column dynamic range, but I am also getting lost working out how I can create a dynamic range for all data on a dedicated worksheet.
I'll be able to translate a solution to other cells as well
I am tring to find a more efficient way of avoiding a double Vlookup function.
=IF(ISNA(VLOOKUP(A6,Sunday!$A$72:$AM$86,38,FALSE)),"",VLOOKUP(A6,Sunday!$A$72:$AM$86,38,FALSE))
I have read over this forum but still haven't understood any solutions.
A6 is a name.
The name may not be on the worksheet the function is looking up.
I have a workbook that multiple users access (read only) to do quick estimates.
This workbook has a "Print & Record" button/macro that does just that... it prints two copies of the estimate, then records/copies the basic data to another workbook (Retail Estimates Data.xls). The macro opens, pastes to, and closes this file in a fairly quick time frame, but it is conceivable that two (or more) users could be trying to do this at the same time.
What I need is some code to check if the Data file is open by another (read only status?), and if so give a message to that effect, and ask to try again, by which time the other user instance should have finished with the file.
Is it possible to inset a bank into a cell, and use that cell in a subsequent addition formula?
For example;
A1 contains 1
B1 contains 2
C1 contains =IF(A1=1,"",3)
D1 contains =A1+B1+C1
In the above case the cell D1 contains #VALUE and not 3, whereas if C1 were actually blank D1 would contain 3.
The reason I ask is that a blank is not the same value mathematically as 0. The latter is the number zero, whereas the former is the lack of any number.
I have a range, A1:A10 that I want to sum, min, and max. I want to ignore any text or #DIV/0! values in this range. I know how write the formulas except how to ignore the text and cell errors. Can someone steer me in the right direction?
View 5 Replies View RelatedI am combining the information from multiple files--one per state, for several states--into a single large table so it's more easily reviewed. Each file has multiple worksheets, named for categories. The category/worksheet names are standardized, but not all worksheets are found in every file.
There is some overlap of items from state to state, so my macro copies key fields from each worksheet and pastes them into the new table and adds a column on the left with the name of the category/worksheet each item came from. It then removes any duplicates to create a list of unique items. Across to the right, the table has two columns for each state, one to show if the item exists there, and the other the date it was added.
In the final step I am using lookup formulas to populate the states columns, using the category/worksheet name from the left-hand column to identify which worksheet to pull from. When a worksheet doesn't exist in a source file, however, this creates an invalid reference.
Is there any way to use VBA to identify which worksheets are in a file so I can use the results in an IF/THEN statement to bypass any lines that would create the invalid references?
I have some short code that imports data and account numbers that is "|" delimited. The problem I am having is that during the import, excel converts some of the account numbers to exponential numbers.
e.g. it converts 542690E24 to 5.4269E+29.
Is there a way to avoid this from occuring during the import? I tried to format the destination cells as '000000000' (all accounts are 9 digits) but it didn't work.
I have in my sheet from C13 to C350 many serial numbers which I manually type them in. These serials are 5 digit numbers, and I was just wondering if there's a function or formula that will not allow duplicate numbers in that column.
I have a problem with an array formula where I want to sum the values in every 7th row. The problem I have is I am receiving #VALUE! in my returing cell of the formula when there is text in the array. Is there a way I can avoid including the text in the array or maybe error checking using ISERROR and ISNUMBER.
The array formula I am entering is: [Code] .........
How can I avoid that an empty series shows up in the legend of a graph?
View 9 Replies View RelatedI have searched all over and read many solutions for selecting only visible cells and copy pasting them or formatting them by doing Ctrl + G and selecting visible cells only. However the problem is once I select visible cells only, it seems that every time I do Ctrl + C on filtered Range it only selects visible cells. I want a way to toggle this setting in Excel. I'm using Office 2007. For example: if I want to select the entire range, both hidden and visible cells within the selected table array, is there anyway to reverse or toggle the setting that causes Excel to refresh the "visible cells only" setting back to default or all cells?
It would be a much faster way than to remove all filters, select & copy entire range, and then re-apply all filters again.
I have a formula I've been using for a long time which uses VLOOKUP to find results based on various reference cells, and then adds them up. To avoid errors caused by VLOOKUP not finding anything for one of the references I have also used ISERROR. The formula returns a blank if the calculation returns a zero.
I now need to bring further references in to the calculation but, using the format I have been, the formula is now too long. Here is my extensive formula:
I have a worksheet in which i am asking a user to enter manually a number in cell E3. Suppose the user starts with 100, then the next time he is entering in E3 he shoudnt be able to enter 100 nor any number less than 100. I dont have a range for the numbers that the user is going to enter.Therefore countif function does not work. Is there any way that i an store the number taht the user enters first in cell E3 and then use that database to avoid the user from entering the same or a number lesser than that number.????
View 2 Replies View RelatedOk, I for some reason just cant wrap my head around this. I need to to get the average time per call of two rows, but they are based on how many calls taken.
so in one column i have 50168 calls taken at 4:21 seconds per call. and in the next row i have 597 calls taken at 5:20 per call. I need to see what the new average will be with them combined, and I need it to display in m:ss form.
Error Check Marco.xlsm
see attached example. I am trying to write an error detection routine that iterates through worksheets that have numeric values for names (ignore text names or alphanumeric). Macro checks range on each numeric worksheet E3:E33 and is supposed to report back on the SummarySheet if any value other than 1 or 0 is found in range E3:E33 on any numeric-name worksheet. Code as follows:
[Code] ....
Problem is that it just reports EVERY worksheet as having an error when clearly most don't (none do I think in the attached example).
Try changing some of ranges E3:E33 to values other than 1 or 0, it still reports all sheets. Why the macro does not evaluate the range E3:E33 properly and just reports every worksheet as having an error?
See the attached sheet. I am trying to add together two figs which are linked to calculations which have formula built in to stop error messages when there is a 0 / 0 = #value type error. However when these two cells are added, if the cells are blank I get an error message. And if only one cell has a value, I get "" with my existing formula. what I need to do to get a result of 7 if for example cell A4 = "" + B4 =7. At the moment my formula shows "" in the sum total of these cells
View 5 Replies View RelatedOK, so I have a userform with some text boxes that I have specially formatted to accept only date values in the form of mm/dd/yy. By default they are blank. I have a check in one of my codes that looks like this
Code:
If DateBox vbNullString And DateValue(DateBox) > checkdate Then
M1 = "NEOPRENE" & Chr(13)
Else
M1 = "" & Chr(13)
End If
Where DateBox is this specially formatted TextBox and checkdate is a future date being checked against.So if DateBox has a value in it AND that value is greater than the date being checked against the returend string is Neoprene, otherwise it is blank.
Well the problem I have is when the first condition returns FALSE, i.e. when DateBox is empty, the DateValue half still gets evaluated and returns a type missmatch error or something like that because DateValue("") returns an error. I have line of code 8 times, one for Neoprene, squeegee, etc. So the name of the text boxes are each unique and I am using M1, M2, M3, etc.
For other reasons, use of "On Error Resume Next" doesn't work for this situation because it causes a result opposite to what I want to happen.
the if stattement works perfectly and does exactly what i want except when it comes to the else part. if there is no error the statements are run perfectly but if there is an error (in this case the error is generated when a match cannot be found in the spreadsheet) the else statement doesnt kick in and post the msgbox.
the code just crashes. and returns an error 1004 on the line i have highlighted in yellow
res = WorksheetFunction.Match(invvar, Columns(1), 0)
If Not IsError(res) Then
Me.txtClientID.Value = ws13. Cells(res, 7)
Me.txtNumber.Value = ws13.Cells(res, 7)
Me.txtDate.Value = ws13.Cells(res, 8)
'save client id as a variable
'Print to invoice------------------------------------------------------------------.....................
I am trying to run create a simple macro that copies and paste special values - something I have done 100's of times but for some reason I keep getting an error message - even though I recorded the macro and didnt write it by hand - see below:
Sub Macro6()
Cells.Select
selection.Copy
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
For which I get 'Compile Error - Expected Function or Variable'
I have some code that, although works fine in Excel 2003, does not in Excel 1997. I receive this error when I try running it:
COMPILE ERROR:
NAMED ARGUMENT NOT FOUND
Sub HPVAL()
Dim r As Range, myStr As String
myStr = "HP"
Set r = Cells. Find(What:=myStr, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not r Is Nothing Then
r = r.Value
While Not r Is Nothing
Set r = Cells.FindNext(r)
If Not r Is Nothing Then
r = r.Value
End If
Wend
End If
End Sub
It looks like Excel is getting hung up on the "SearchFormat:=" portion of the code.