When functions like MATCH, LOOKUP, etc are being used the result can be #N/A. An additional IF statement is required to NOT show the #N/A. The Example =if(ISERROR(match ... very long...),"",match ... very long ...) Read =IF(A is Not OK, "", A) A (very long formula) is used twice If your formula is very long the total formula even get twice as long. Is there a way to shorten this?
View 4 RepliesIf C36, C37, C38 or C39 contain a 0 then put 0 if not continue with the the formulae
I have this but I know its not right as this is a sum: =IF(C36:C39=0,0,ROUNDUP((C36/C37)+(C38/C39),0)).
I have a work sheet which includes a column of numbers representing certain daily events. I am building a user defined function to analyse the trend in the numbers by assisigning values from -2, -1, 0, 1, 2 based on comparison of two days.
Below is the function I built but it is not working, it is resulting in zero values in most conditions. I have attached the sheet which includes the numbers and the function.
I am trying to use the if statement, if a cell = a cell that has a word in it then show content of another cell that has a name in it.
View 5 Replies View RelatedColumn A = program which could be 100%, 90/10 or 80/20
Column B = Dollar amount submitted
Column C = results
I need a formula in C1 that says:
if A1=100% then B1*100%, if A1=90/10 then B1*90%, if A1=80/20 then B1*80%.
With 2 programs my if statement worked fine, but now that there's a 3rd I can't get my if statement to work properly.
i am trying to amend my if statement in the 6 WEEK COLUMN to only show for entries that have 13 in the weeks column, I want the entries that show 2 or 26 to come up with a blank result. I have attached an extract for your information.
View 4 Replies View RelatedI have a Sub test where I think can be setup in an Array or a shorter sub routine, how to shorted this routine. Ths routine was designed to check your Computer and depending if you are one of the selected on will the unprotect the sheet.
View 2 Replies View RelatedI have the following code as part of my macro. What I was wondering is a) Can anyone think of a way to shorten this? and b) I currently have this exact code copied 8 times, I did a find and replace to change 'if year = 2003 then' to the year 2010. What I would like is to have 1 code that will automatically change depending on the year the user inputs in a cell. (I should mention that 'Year' is a cell on my input sheet where the user types in the year they are currently working on and the macro automatically runs)
'2003
Sheets("Cash Crops").Select
If Year = 2003 Then
' This copies the cash crop BPU's.
ActiveSheet.Unprotect
Workbooks.Open Filename:="F:xdataCAISCAIS spreadsheetsBPU's.xls"
Application.Goto reference:="Cash03"
Selection.Copy
Windows(Filename).Activate
ActiveWindow.SmallScroll Down:=21
Application.Goto reference:="CashCrops"
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-30
Range("A2").Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True..............
Example6.xlsx .i just cant remember what it is im meant to be using as i havnt done it since school. basicly on the attached spreadsheet, there are 2 tabs. the common column is Contract number, if the data in the contract number column matches then i need the figures for 20/40/TEU to show against the Special aggreement by code tab.
View 3 Replies View RelatedBasically I want to copy and paste all data in a row if the value in a cell is equal to something
I am currently using the below formula
The Value in red = Set tgt = ThisWorkbook.Sheets(Criterion.Offset(, 1).Value 'Pick the cell next to the cell containing the criterion).
But my issue is now that i will need this to work based on 37 values and having this macro 37 times with the value changed and 37 buttons will be difficult.
So I am looking for a way i can add multiple values each going to separate sheets.
=COUNTIF(Sheet1!$A$1:Sheet1!$A$32515, IndexSheet!B2)
+COUNTIF(Sheet2!$A$1:Sheet2!$A$32515, IndexSheet!B2)
+COUNTIF(Sheet3!$A$1:Sheet3!$A$32515, IndexSheet!B2)....
+ COUNTIF(Sheet20!$A$1:Sheet20!$A$32515, IndexSheet!B2)
Above formula check for the value in IndexSheet!B2 and searches it across all 20 sheets and returns total sum
Is there any shorter version of this?
I have a spreadsheet with some calculations and the part i am interested in is data in R4 : V58
In this R4:R58, S4:S58 and T4:T58 have some rows that have False because of IF calculations that put False in some rows of R and hence in S and Hence in T.
For example, R15="FALSE" so will S15 and T15 be. Col U is based on whether the corresponding row in R is False or a number. If a Rown in R is false, the row in U= 0 else the row in U is the same as in the same row of a different column - Col I
Col R, T and U are numbers. Col S is text
For example:"
R S T U
False False False 0
False False False 0
False False False 0
55 DEF 3 15
46 XYZ 2 67
False False False 0
False False False 0
23 GEF 4 43
I want a table in another worksheet that will sort through this using a formula and print
Col1 Col2 Col3 Col4
55 DEF 3 15
46 XYZ 2 67
23 GEF 4 43
If I can define things like where the table will be placed in the new worksheet etc, that will be a bonus..But It appears I cannot understand how to do this at all..
I am trying to filter an array (the longer one) using the shorter array as the criteria. I am currently doing this using the following method
IF(LOOKUP(lookup cell, array)=lookupcell, lookupcell, "FALSE")
I then copy and paste 'values' and filter out the 'false' to get my final result.
This has worked in the past, but for some reason that I simply can't figure out, the formula isnt working! I've attached the example, and I've highlighted a number in blue (cell E522 and C103), (that should be being found in the 'LOOKUP' function) but is returning a "FALSE". I have looked over the code and simply can't figure out why Excel isn't returning the right value.
This is obviously happening for a quite a few of my numbers, as my filter result is returning an array that is about 1500 shorter than it should be. I have highlighted E522 as the 'example cell' to look at.
I have a list of email aliases for my company, approx 10,000 entries, mapping the alias to an email address. Two columns, one the alias firstname.surname, second column being the full email address. I have a second list, a subset of these aliases, approx 1200 of them, that are users of a particular web app. All i have in this list though, is the firstname.surname bit.
What I need to do is compare the lists, and where the aliases match I need to pull out the corresponding full email address from the long list, so I have 1200 aliases in the shorter list, with the full email address mapped to it. Is this possible in excel? i've tried all sorts of LOOKUP functions, and MATCH functions, but none seem to work. MATCH will give me the corresponding row number in the long list, but then it seems to offset itself every row it goes down, so the values are 1 out for every row. First row is good, second row -1, third row -3, fourth row -4 etc.
I don't want to have to manually go through 1200 entries and match them against a 10,000-entry list. Hopefully there's a quick way in excel to filter the long list down to the entries specified in the short list?
I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
I have an excel 2003 sheet that collects data from Infopath forms. The forms are to record students who have broken school rules, when, where, repercussions etc. One column shows their class and there is a separate column for each rule broken.
I want to create another sheet to show each class down the rows and the columns to show each school rule. Therefore, each cell would show the number of each particular rule broken for each particular class. I have tried to do countif and sumproduct (if on sheet 1, column B the class is KA and on sheet 1, column M, the rule is bullying = how many times this has occurred).
I'm trying to set up an if statement that will recognize that if a cell is FHR it will do something...but if it's PHR it will do something else. I think I found the place where I keep getting an error but I'm not sure how to go about fixing the issue.
View 2 Replies View RelatedI am attempting to use a previously Set variable as part of the next Set statement, pretty unsuccessfully at present.
My purpose is trying to look up
Code:
tb_SelJobID.Value
from a userform in Col Z then look across the row to Cols D,I,N,S & W (different types of work) to see if
Code:
TbSelYr.Value
matches the year selected then insert a formula in the row to the left. Then loop down to the FinalRow.
Currently my Set Found1 statement does not recognise my Found10 value. I know it will be my syntax as it always is. I have cut down the following code to display where the problem areas are, Found1 thru 5.
Code:
Sub CmdGo3_Click()
Dim Row As Range
Dim FinalRow As Long
Dim Found1, Found2, Found3, Found4, Found5, Found10 As Range
Application.ScreenUpdating = False
[code]....
Windows 7 with Excel 2010
I have an Excel Sheet which I use as Database. The database has 11 columns and I insert data with the following function:
Code:
Sub testInsert()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim i As Integer
Dim strTest As String
strTest = "test"
[Code] .......
Now I want to retrieve this data. i.e. I want all F1 where F2 and F3 are 0 AND I want them ordered descending. I'm trying to achieve this with:
Code:
Sub testSelect()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim mrs As New ADODB.Recordset
Dim strTest As String
strTest = "test"
[Code] ....
The result I am getting looks like this:
9
8
7
6
5
4
3
2
15
14
13
12
11
10
1
I assume, that the data is interpreted as String instead of an integer. But I explicitely stated the data as Integer when storing the data into the DB.
I have been given a worksheet which has 5000 rows spread along 13 columns.
There are about 200 cells that I am interested in from this sheet, these cells being the subtotals of the cells above them.
I have extracted the individual cells containing the subtotal values to a new, empty column but they are spread down this column with blank cells in between.
Is there a function / formula to get rid of the 4800 blank cells so that the data I need is compressed into a stack of 200 cells all filled with data?
i am having alittle trouble with this line of coding.
With ActiveWorkbook
.Sheets("Hidden Data").Cells(rngFound.Row, 9) = Sheets("Data").Range("L18")
.Sheets("Hidden Data").Cells(rngFound.Row, 12) = Sheets("Data").Range("l20")
.Sheets("Hidden Data").Cells(rngFound.Row, 15) = Sheets("Data").Range("l22")
.Sheets("Hidden Data").Cells(rngFound.Row, 18) = Sheets("Data").Range("l24")
.Sheets("Hidden Data").Cells(rngFound.Row, 21) = Sheets("Data").Range("l26")
.Sheets("Hidden Data").Cells(rngFound.Row, 24) = Sheets("Data").Range("l28")
End With
with the line "Sheets("Hidden Data").cells(rngfound.row, 9) = sheets("data").range("l18")" the error messages states Object Required, but i am not sure what this is trying to say.
any idea's or thoughts would be greatly appreciated.
at the top of the coding i have listed "Public rngFound"
I am creating a training matrix that will be used in a number of locations. Some only have a few staff members whilst others have lots. I want each location to print a list on a formatted sheet but I want to set it up so that the locations with fewer people don't end up with lots of blank lines.
View 9 Replies View RelatedWhy should I set objects to nothing (ie clear them) just before ending a sub routine? Doesn't excel dump the variables after the sub is finished running?
Example:
Dim ws As worksheet
Then at the end...
Set ws = Nothing
I have two columns E, F in excel. Rows are dynamic. In the sense that sometimes only 2 rows appear or sometimes newer rows appear......
Now I want to lookup the last row and get the required numeric value. I have written the following formula to get the result. =VLOOKUP(CONCATENATE("E",SUM(COUNTA(E2:E16),1)),E2:F16,2). It returns value 60. But when I add 2 new rows.
7 F 70
8 J 90
It should return 90. But it is still showing up 60. when i checked CONCATENATE("E",SUM(COUNTA(E2:E16),1)). it is returning 'E8' correctly. But VLOOKUP unable to return correct value.
I have a worksheet with a header row.the data is from B2:B25.I have to accomplish two things.
i)I want to get only 8 records (viz record nos-5,6,11,12,17,18,23,24)from the entire worksheet.serial numbers are shown only for illustration purpose.i want to eliminate all other records from the worksheet.then i will get the records from seriel numbers 1-8 as shown in E2:E9.
(2)I want the content of row3 to come up in C2 and then row3 should be deleted. now the content of row4 should come up to C3 and then row4 should be deleted.it should continue.the result will be 4 records as shown in G2:H5
See the attached sample file.
HOW TO GET THE REQUIRED RECORDS ONLY.xlsx
I recorded a print macro which prints to my default printer, others will be using this spreadsheet so I really need it to print to the same network printer. The printer address and name is: ironyan-fileBI44
How do I incorporate this into my code?
[Code] .....
I need a loop function (i guess a do while or do until code) so whenever the word 'Non-Current' appears in colum A enter a 1 in colum E until the word 'Total Non-C' is reached at which point the loop must end.
Such as:
A B C D E
NON-CURRENT 6 4 5 1*
ABSA 4 5 2 1*
BARCLAYS 3 2 8 1*
NED 0 8 6 1*
TOTAL NON-C 4 6 7 0
The buttons are located on the PFM Tracker sheet with what I need them to do. In addition I'll give you a breakdown of what is required.
Currently when the workbook is opened the user is forced to activate the macros. One of these macros disables the Cut, Copy Paste features within Excel. I need the first button 'Enable Copy' to request a password for activation and once entered the Cut, Copy, Paste feature to be enabled. Then when finished the 2nd button 'Disable Copy' when clicked to re-activate the Cut, Copy, Paste Disabled macro.
Hope that explains it. I have left the PFM Tracker sheet unprotected for who ever wants to play with it. (Not that protection would stop some)
I am trying to copy formula as follows:
2
3
1
10
=A4*B4*D2
=A4*B4*D3
[Code] ......
I'm trying to copy the formula both down and across but I'm guessing both issues are the same.
sum all my sheet data....I have around 200+ sheets with same format and I'm doing one summary sheet where I can see the sum of all sheets (column L) but not able to get the formula .....
Example: Currently using below formula
=Sum('Sheet 1'!L:L)
How to I change the Sheet 1, Sheet 2 ..etc If I drag the formula I'm not getting the results....
get the right formula to copy all the sheets column L:L sum details...