the reason why my range declaration is so strage is because i was trying to change the dimensions of the range with an offset function because i had many ranges to average but the offset function didnt want to work. this way goes like this....................
I have a formula that needs some tweaking. This formula is to reference the ACCT and find the “best” contact information and return the result to the Merge sheet. If the ACCT does not have a Parent then you reference the ACCT to the abc_Phonelist sheet. If the ACCT does have a Parent then use the Parent ACCT since it has a more desirable account number to reference against the abc_Phonelist. If the ACCT or the Parent ACCT uses Processors then the ACCT from the Processors sheet has the best reliable account number to reference against the abc_Phonelist. In sum, there are three different possible “number tiers” that can be used. The first, the ACCT phone number. This means that the ACCT has neither a Parent nor a Processor. The second, the parent ACCT phone number. This means that the ACCT has a Parent, but neither the Parent ACCT nor the ACCT has a Processor.The third, the Processor phone number. This means that either the ACCT or the Parent ACCT has a Processor phone number.....
I have Cell C63 in Worksheet 1 which is set up to say either "YES" or "NO" depending on whether a value on a different worksheet exceeds a value in an adjacent cell or not (also on that worksheet). The formula I am using is simply:
Essentially I don't want anything displayed if there are no data in the range C4:C54 on Worksheet 1. I thought I need a nested IF function but I couldn't get this to work.
Is there a way I can sum the result of a formula on a range of cells that meet a criteria? For example, I need to sum the difference of only the cells that are >46. (a1-46)+(b1-46)+(c1-46)...+(g1-46).
In this case the result I'm looking for is on row 2:
Is this possible to calculate in 1 cell only (h1)?
I needed to convert PartNumber to a string using Cstr(PartNumber).
The following VB code searches an Excel spreadsheet for a Production Order and then looks for a Part Number in the corresponding row. The Production Order can be listed multiple times, but there is only 1 Production Order/Part Number combo. When searching for the Part Number, it will find letters, numbers with a dash and letter/number combos, but won't find integers.
For example, it will find ABC, 3500-01 and T1000, but not 200.
I can use the FIND function to find the first occurrence of ":" and therefore select everything to the right of it, but how do I locate the second occurrence of ":"?
I want to segregate the 7985 Medicare. What if the data varies, i.e. some rows have one occurrence of ":" and others have two?
7900 Personnel: 7970 Bonus. I always want to segregate the last 4-digit account number and description.
I want to search for each instance of "DOG" in column A, and copy the row below it and then paste that entire row to a new sheet called DOG. I can then autofilter and use a pivot table on the DOG sheet.
I cant just use an autofilter, because the row below can and sometimes does appear elsewhere in the spreadsheet, but refers to something else. It is all because this is an imported text file.
I have attached the spreadsheet to show you what I mean.....
here is the code I have so far, modified from another thread I posted.
Sub dogs() Dim rd As Worksheet, dg As Worksheet, i As Long Dim fRng As Range, Crit
Application. ScreenUpdating = False
Set rd = Sheets("Pets") Set dg = Sheets("Dog")
The attached is a very cut down version of my spreadsheet...
I have a query on retrieving the result from a set of range from other sheet.
Query: In the Sheet1 Column B has to take first two words from the Column A & lookup the matching results from Sheet 2. (The search range should be the first two words of each cell)
Result: The complete sentance in the Cells (First Two words) should get placed in the Sheet1 Column B
Example: The Expected result should be as mentioned below. (Blue in color should be the results)
Sheet1A BC1NameResults2abc def fkfeabc def words3ghi jkl kikgN/A4mno pqr stu lkasmno pqr must work5 Don't give my book don't give up6vwx yzyvwx yzy not7xwv uts rqpxwv uts yes8omn lkjN/A9ihg fed cbaN/A
Sheet2A BC1Lookup Data2abc def words3xxxghi jkl4yes you have to5don't give up6no you will not7mno pqr must work8vwx yzy not9Excel work10xwv uts yes11omn mre lkj12ihg not fed 13not work14
The following code works perfect but the "change" event is only triggered when working directly on intersect range. Tried using the "calculation" event but could not figure it out. This is what I want:
1) To replace the code provided below using the calculation event 2) To only trigger the event for the row(s) where the new value was generated, not for the whole "For Each" statement 3) To use one single code for all worksheets, instead of copying the code in every working worksheet on the workbook, if feasible 4) And I would like a "second alternative", where the user of the workbook can click on a button and trigger the event on every row on the workbook that has a non empty cell within the intersect range, assuming that the intersect range column is the same for all worksheets
Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean Set d = Intersect(Range("I:I"), Target) If d Is Nothing Then Exit Sub For Each c In d Select Case UCase(c) Case "C" fc = 1: fb = True: bc = 4 Case "O" fc = 2: fb = True: bc = 3 Case "D" fc = 2: fb = True: bc = 46 Case "G" fc = 2: fb = True: bc = 5..................
Is there a way of finding all files in a directory which contain a macro? I have script which will loop through all files in a folder but I don't know, and can't find, the appropriate syntax for referencing macros/modules.
I am writing a formatting macro. The step I'm on needs to locate a cell with "Result" in it, and select the adjacent cells to the right until the end of the exported report (which could be a variable number of columns). Here is the code I have so far:
When I drag my VLOOKUP formula down a column in Excel 2010, the return value copies the formula result from the original VLOOKUP formula result. For example, if the first VLOOKUP returns a value of 0.5, I expect to see 0.5 or 1 in the cell below that one. However, I get 0.5 which is not the expected result for the cell below.
When, I click the fx on the cells below, the expected return values appear in the formula result. After I click OK, the expected formula results updates and now appears in the cell.
I'm not sure what is causing this issue. My computer was updated recently from an old machine to a new one. I have never experienced this issue before.
I need a formula that will tell me which 'BPS' a person should receive based off of the count of loans closed (between 'Criteria 2' & 'Criteria 2.1' and/or the volume (total of 'Loan Amount')(between 'Criteria 1' & 'Critera 1.1'), whichever gives the person a the higher tier.
I have provided an example below. In the example John Doe should receive .01750 'BPS' because he closed over 1 million in volume. If, for example, John Doe closed 5 loans for 400,000, then he would receive .01600, since he met the criteria for the 2nd tier based off of # of loans closed.
The formula needs to take into consideration which 'Comp Plan' the person has, as well as the other criteria mentioned above.
I have the simple code below to perform and filter and copy:
Code:
Sub Macro1() Sheets("Data Dump").Select Range("A1:F100000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ "K1:L73"), CopyToRange:=Range("P1:U1"), Unique:=False End Sub
However, the CriteriaRange can change depending on which OptionButton the user chooses on a different tab. My reference the range is in Cell N1. So how can I use Indirect to refer the range in Cell N1 instead of the current CriteriaRange of K1:L73?
I created a simple auto numbering function whereby Cell A7 contained =Row()-6, and Cell A8 contained =(A7+1). I then shift, and pasted the contents of cell A8 until cell A600. My aim is to simply copy the increments of 1 - 600 into another column. However when i copy and paste i'm also copying the initial underlying formula ie: =( A?+1), Is there a way to copy the results, not the formula?
in my workbook i have 3 sheets named "time" "final" "push". in the sheet thats named "final" ihave in cells B2 and C2 2 values that correspond to a type interval (lets say 75-378) in the sheet "time" each row represents 1 sec, starting at one.
what i would like to do is locate the interval defined by the 2 values on "final" sheet and find the min max and value in column S of the "time" sheet.
i.e if B2 and c2 said 78 and 378 it would go on "time" sheet select the range of S78-S378 and find the mean, the max and the average and report them in sheet "final" in cells E2 F2 G2 respectively What would be optimal is that there was a way to perhaps loop so that it can do the same thing with the range defined by the values in B3 and C3.
I need a formula (or formulas) to give me the "coordinates" (Column & Row location) of a certain value in a large rectangular range. Intuitively this should be doable with a smart combination of VLOOKUP, HLOOKUP, INDEX, MATCH, ADDRESS and/or CELL functions (maybe in the form of "array formulas") but I can't seem to find the way...
Here is an example of what I need:
I have a large rectangular range (A10:F500), full of numerical values. Then in cell A1 I have one of the (many) values contained in the range, let's say, 3.14159. I need two formulas that give me the following:
a) A formula in cell B1 that tells me in which COLUMN of the range the value 3.14159 is located a) A formula in cell C1 that tells me in which ROW of the range the value 3.14159 is located
If the range consisted of a single column (or a single row), this would be an easy INDEX and MATCH combination.... but I'm dealing with a *rectangular* range here...
I have 2 spreadsheets. One of them has various lines that sum different lines from the other spreadsheet, e.g.
Sales = 200100 Expenses = -300345 Other Income = 120000 Selling and Admin = 35000
The other spreadsheet has a lot of details that make up this first spreadsheet.
e.g.
Paper 100000 Cardboard 50100 bottles 30000 cups 20000
These add up to the sales number for example.
Is there a way that I can use the solver or something similar to get all the cells that relate to the first spreadsheet in one shot, and so I don't have to first find what the sales number is made up of, and then what the expenses number is made up off..can I look for the relating cells in one go. Is there a code or something that I can use.
The 2nd spreadsheet has a lot of rows, and it's quite difficult, also I find that when I look for the sum of the sales number and try looking for the expenses number, it gives me cells that are related to the sales number as well.
I am new to VBA and have tried to develop the code for finding a specific stock symbol (Column A) from over 4200 symbols from a downloaded csv file, copy the data in in its (specific symbol's) row (in the next 5 columns B to F) to the 3rd blank row in the master workbook (data arranged from Bottom to Top). In the Master workbook each stock symbol has a worksheet with its symbol as the tab e.g. BHP.AX is the tab or sheetname for BHP stock in the Australian Stock Exchange. The downloaded file comes from a subscribed site EODData which provides daily OHLCV (Open/High/Low/Close/Volume) data against each symbol.
My attempt is shown below and it is very primitive. It does not work! I wish to run it from the Master workbook (name:-0PortfolioASXMultipleIB.xlsm) and not from Csv Data file.
VB: Sub Macro0CopyFromCSV() 'Insert a blank row and format it in Master workbook Rows("3:3").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
The purpose of the formula is to locate the 2nd occurrence of an entry where criteria in other columns are met (used to determine whether people can qualify for an item based on attendance and another condition).
I am using this formula but I know it needs some adjustments and can't seem to work it out or maybe am going about it completely wrong.
=IF(COUNTIF(D2:D205,D2)>1,AND(T2="Y",U2="Y")*1,0)
I need the formula to indicate the second occurrence of this cell with the criteria that BOTH first & second occurrences have T2="Y" and U2="Y". My formula only indicates the first occurrence and the first occurrence if T2 and U2 meet the conditions.
I am trying to find the starting effective month for a workyear. The criteria for the selected month is that the data BEFORE the effective month is all zeros. I manage to get the result if the data AFTER the effective month is ALL non- zeroes. If there is any zero, the data fetch will be inaccurate due to the COUNTIF formula. Is there any other way or formula that will enable me to get the result. File attached for testing and reference
I'm looking for a formula, which says if a number is within a range, perform a calculation. I have a table of ranges (0-6000,6001-10000,10000-12000, and so on). My formula looks at the current month cumulative balance, and inserts the value into the line of the range it falls into. However, the formula now contains lots of nested "IF" statements, due to the number of ranges I have. Also, if my cummulative balance is 6005 for example, but was 5005 in the previous month, I would like to see 95 in the 0-6000 row, and 5 in the 6000-10000 row. I making a complete nonsense of trying to explain myself, so I'll attach a quick example of what I'd like to see.
I want to create a Pivot table, and is now working on the data sheet. In this, every salesman have one row each day containing their sales result that day. I have data in 3 columns. In column A the date, column B contains the Name of the salesman and column C the sales result that day. Now I want to update column D with each salesman's best result. I want each row to contain the top result for that particular salesman.
Date | Name | Result | Top result 1/10 | Pete I | 11.111 | 2/10 | Pete I | 19.100 | 3/10 | Pete I | 23.408 | 4/10 | Pete I | 16.774 | 1/10 | Sara G | 10.884 | 2/10 | Sara G | 10.739 | 3/10 | Sara G | 36.469 |...................
creating a .Find code to search a range on a sheet and then display each result seperately in a msgbox. An inputbox will be the value .find searches the range for.
the msgbox needs to be a vbyesno
VB: 'SEARCH CODE Dim myItem As String, myRNG As Range, NewLoc As String Dim Found As Range [code].....
Basically, what I want is to find the earliest EPSI1MN and its associated EPSI1YR and EPSI1NE, within the same period of EPSI1YR and then store the results in a separate sheet. This process is to be repeated for 12 years of data, covering more than 4000 companies.
I have the following formula but would like the result to be blank if it can't find anything. I know I need to use the iserror function but I don't know how to use it. Can anyone help me?
I am using the find method to search column headings, and based on the results copy the column to another worksheet. Everything works fine except if I have a mixed text and numeric string in the cell, for example DT35. In this case the macro doesn't copy this column. I have attached the spreadsheet. CTRL - A will run the macro. The macro calls a form with checkboxes, captioned using the values in the worksheet titled "Set-Up". If a value is found in the column heading the checkbox is set to true, then when the "Copy Selected Columns to Final Sheet" button is selected the columns are copied to the "FinalSheet" worksheet. I tried using xlPart instead of xlWhole, and this works but I need to search for exact strings so xlPart isn't a great work around.
