I have a column of text strings in a2.a??? this is my data. Each text string has a persons name somewhere in it with other text around it.
In column c2.c??? i have text names of people names im searching for. eg. bob,jane,harry
what would be a pratical use of the vlookup function to return the name in column b2.b??? if the name from C was located in the string in A this is a non case sensertive requirement.
I need a macro to find the last cell in the column, then copy the formula to the next blank cell. Then, it goes back to the last cell (above) and paste's values. Then, go to the next column and repeat the process. I can do this but have to call each cell separatly...however, I would like to do it in a loop to simplify things. It would be great to even be able to just set the start and ending columns. Here is my current code:
Dim rng As Range, aCell As Range
Set rng = Range("C8, D8, E8, F8, G8, H8, J8, K8, L8, M8, N8, O8, P8, Q8, R8, S8, T8, U8")
For Each aCell In rng
Selection.End(xlDown).Select
Application.CutCopyMode = False
[Code] .......
It does not go to the next column, instead it stays in the same column and repeats the process.
I get new worksheets on a regular basis which are of the same format (same columns with headers) and I have to perform both vlookups and sumifs on them. Their are about 12 different tabs I perform the vlookup on based a vendor name in A2 and its corresponding tab. I am tired of typing the same thing over and over again.
What I'd like to write is a Control Panel with all the equations so I can just copy/paste them in when I get a new sheet.
How can I write a vlookup which would search for the value which is one cell to the right of where I put the formula in?
Similar to:
=vlookup(current cell over one, 'vendor1sheet'!A:C,3,0).
I would then want to copy it down the worksheet. Currently type in column E everytime I get a new sheet:
=VLOOKUP(F2,'Vendor1'!A:C,3,0)
The catch
Column A has the vendor name, there are about 12 vendors, each with a different tab with related data which I am searching through.
Ultimately, I'd like to have an equation that would do the vlookup (and sumif) as described above and also use the vendor name in column A to look on a particular tab.
Something like:
=vlookup(one cell to the right,'VendorTabFromVendorNameInColumnA'!A:C,3,0)
I was going to use nested if statements on a control panel sheet with a column for the vendor name and a column for the vlookupformula and than paste a single formula all the way down column E:
In column E, every row:
If(A2=vendor1, 'controlpanel'!vendor1equation), if(A2=vendor2, 'controlpanel'!vendor2equation), etc.
Again, the vlookups would all check for one cell to the right of where this nested if statement was (located in column E, looking in F).
Would like to be able to use a vlookup formula that incorporates multiple search criteria. In C10, I have a specific rank listed. In C11, I have the supervisor listed. The formula needs to list the name of the agent in C12 based on the criteria in C10 and C11.
agent rank score supervisor
A B C D
1 leo 1 99 jim
2 bob 3 68 jim
3 sam 1 73 ann
4 tim 2 84 adam
5 alf 2 55 ann
6 dan 3 42 adam
7 will 2 79 jim
8 sara 1 91 adam
9
10 Rank: 1
11 Supervisor: adam
12 Agent: " "
I am trying to do a vlookup within a macro.
However this always shows the first result in the first row but the rest of the rows just show #REF.
I want it to look up 100's or rows and shows either the result or N/A
Once I create the macro this is what the VBA is telling me: [Code] ......
Code such as the one below carries formulas in columns 3 and 10 down to new rows when inserted. However, I need to allow a user to overwrite the formula in col 3 with any text value. But if the user overwrites the formula on the last row, and then manually enters a new record below, then the code in the last row of col 3 does not get copied down. Is there a way to search upward in columns 3 and 10 until reaching the last cells with formulas, and apply the formulas to the new cells in cols 3 and 10 on the new row? Or, is there a way to store the formulas:
=IF(A21<>"",B21,"") in column 3
=IF(B21<>0,IF(C21<>B21,A21&", "&LEFT(C21,2),A21&", "&LEFT(B21,1)),"") in column 10
Storing these somehow would be good if the user overwrites the formulas in the very top row of data (row 2).
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
On Error Goto CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
.Offset(0, 3).FormulaR1C1 = "=RC[-1]=R2C3"
.Offset(0, 10).FormulaR1C1 = "=RC[-1]=R2C10"
End If
End With
CleanUp:
Application.EnableEvents = True
I have one workbook (workbook A) with data in it (the data is never in the same place i.e. same cells) and one I want to send data to (workbook B)
In workbook A the data is displayed as follows:.....
I have 10 worksheets. They are labelled 'FEB', 'MAR', 'APR', etc...to 'NOV' (NB: JAN & DEC not needed). In column "G" on all these sheets I have the data as "CURRENT" or "OVERDUE". The first 3 rows on every sheet are headers, so the data starts at 'G4' on every sheet.
I then have an "OUTSTANDING" sheet which will display all of the "OVERDUE" items from the various months. I have an UPDATE_Click() event which when actioned needs to search all the column Gs in the month sheets and then copy the data from column 'B' to 'F' of the corressponding row to an "OVERDUE" result. The data from B-F then needs to be pasted in the next available row on the "OUTSTANDING" worksheet (starting at row 4, as first 3 rows are headers with merged and unmerged cells).
Nothing fancy, when the search go does the column and hits a blank cell that means its the end of the list and can move on to the next month.
What i have so far is:
-3 Sheets (Sheet1 - Search, Sheet2 - Data, Sheet3 - Result)
-Sheet1 has a userform ready to link up - this opens up when workbook and worksheet is activated.
-Sheet2 has some example data to search - the search is for column 1 (see attachment)
-Sheet3 has a template ready for the data to be pasted to.
I would like the search result is column 1 to copy the data from columns 1 to 7 to sheet3.
I want the template/layout to remain after the pasting of results. (everything protected from editing, if possible).
Once finished (ie go back to sheet1, i want the data from sheet3 to be cleared.)
Sheet 1
Sheet 2
UPC
Sku
[Code].....
I would like to find the value from Sheet2 Column1 in sheet1 Column1 and return value from Sheet1 Column2 and Column3 into Sheet2 Column2 and Column3
And if it doesn't find anything just return Not Found
The problem that a Vlookup is not working for me is because I want it to be the exact text from sheet2 column1 but in sheet1 column 1 it should not be exact as it might have some extra text as seen in the illustration above
I would like to search Column C for an instance of the text "Std. Residual", then cycle through the non blank cells to the right and run some formatting code:
VB:
'this line will be modified so that i cycle through the non blank cells that i have found
For row_cycle = 1 To 7
'not signifcant
If Abs(ActiveCell) < 1.96 Then
Selection.Interior.Color = 255
[Code] .....
I then want to search for the next instance of "Std. Residual" in Column C, do the same again, and so on for the entire Worksheet.
How do I keep repeating
If Range("E22").Value Range("G3").Value Then
Rows("22:22").Select
Selection.Delete Shift:=xlUp
Until E22 does = G3
Then I need it to go to Row 23 and do the same.
My goal is to leave only rows (between rows 22 and 50) that have the same value in its column E that match cell G3.
I have a 34-page workbook. On Every Row (starting on line 4), and on Every Page (except the first "Summary" page) is repeated, this same formula....over and over again in column "N":
=IF(A4<>"A",(IF(AND(A4<>"A",OR(J4="",J4="NT")),"OK","WARNING: EXECUTION NOT EXPECTED OF LOW PRIORITY TEST CASE!")),IF((LEN(J4)=B4),"OK","WARNING: STATUS DOES NOT EQUAL TC COUNT!"))
So, since this keeps repeating over and over again, is there some way (either by formula, macro, or whatever), that I can prevent the constant repeating on every line, to bring the file size back down? [Note: This formula alone actually added almost 5MB to the file size!]
Two things to know:
- The formula above is seen on row 4 (that's why you see "4" all over the place). On Row 5, it would be "5" instead, and so on...
- The formula above always starts on row 4, and goes down to the "last used" row, in column "N" (so there's other information in that last used row as well).
....hopefully this is a very easy thing to do?
(PS: Thanks PeterSS for the help creating the above formula in the past...hopefully it can now be made less repetitive to conserve ~5MB of file space!)
I'm trying to create a formula (if possible) that will repeat column headers down a row and repeat every nth time. I have text values in cells E6:AL6 and would like to use a formula that will make it so that E6 will show up B2, F6 will show up in B3, G6 will show up in B4, etc. And then when it gets to B35 (AL6), B36 will then revert back to E6, and the loop will continue - B37 will be F6, B38 will be G6, etc.
My thought is to create an offset/address of some sort that will read as OFFSET(E6,0,x+1) and if x>35, start back at 0. However, I don't think you can create such a formula using an offset?
I have a formula that is working, but I want to repeat it every seven columns. I know I can drag it and release it (or drag a few and release them in the appropriate column), but at this point I have so many columns that it's quite cumbersome to do that. Is it possible to create a macro that will automatically repeat the formula in every cell seven columns to the right (the same row for all)?
My formula is in cell K7 is:
=IF(AND(K24<=0%,K24>=-2%,OR(J32="T",J32="F")),1,"FALSE")
Now I want that formula to repeat (with the proper adjustments) for cell R7, i.e.:
=IF(AND(R24<=0%,R24>=-2%,OR(Q32="T",Q32="F")),1,"FALSE")
and then to automatically continue like that for cells Y7, AF7, AM7, and so on.
how to get it to fill down. Basically depending on the contents of column D, a formula (which also needs to fill with the rows) is copied from sheet 2 in to column K.
Code:
Sub Copy()
With Sheets("sheet1")
typ = .Cells(Rows, Count, "D").End(x1Up).Row
[Code]...
That's what I have. I am totally stuck. the formula needs to relate to column G and fill down too. this is the formula
HTML Code:
=IF($G2=2,$G2*90,IF(AND($G2<4,$G2>2),"$270.00",IF($G2>6,360+(($G2-6)*50),IF(AND($G2>=4,$G2
I'm looking for is to repeat this formula within the cell, so that it can pull the information of not only the first match, but all matches proceeding it. Right now it only pulls the first match it finds, but I would like it to add every time a row matches the criteria of the formula.
=(LOOKUP(2,1/(('Paste SFHS'!$A$2:$A$9=A2)*('Paste SFHS'!$B$2:$B$9="08-Wave/Stu. Goals")),'Paste SFHS'!$C$2:$C$9))
I am in the process of developing a budget template for next year and here is what I want to accomplish through Macro to avoid the tedious mannual manipulation process (hope that's achievable). BTW, I am only an entry-level Macro user who has no background in coding.
I have attached an Excel sample but let me explain:
In each of the expenses tabs (1, 2, and so on (only 1, 2 given in the sample)), I want to be able to select a range of the data cells (A19:Q34 in "Expense 1" and A10:Q28 in "Expense 2") and run this Macro so that:
1. 10 blank rows be inserted between selected data rows
2. A sum created for each month from the four rows below) with different color
3. A Year to Date Actual and To Year End Forecast created based on the VLOOKUP of the "P&L" matching the account code by month
4. Finally grouping of the first 9 rows.
the above is what I have manually created for the first account code in the "expense 1" tab. I have 7-8 expenses groups and about 200 account codes. So mannually creating the above for each expense groups is not that terrible if this will make life easier for Macro.
I would think this is a very typical Macro issue but can't really come up with anything.
The other issue I ran into the VLOOKUP for the YTD Actual and TYE Forecast. It is to look up accont code in the "P&L" tab and match the #s. But the column numbers in the VLOOKUP do not change automatically when I copy across. Thus I have to change mannually, which is really a pain in the butt. I also know I can't copy down because that will change the account code that I want it to match. Because to be able to copy the VLOOKUP formula across the same account code, I need to use the $ to fix the account code. But to copy down I think I need that to be without the $ sign. Any solution on that?
I am having trouble getting my IF statement to test if the cell contains the text "sale" return "X" if not "Y". I need it to search through the text string in that cell and find a certain word, and if it finds that word, retrn a value. I am really having difficulty with is what symbol or function do I use for the logical test? (i.e. =, <>, MATCH, INDEX?)
View 4 Replies View RelatedI wish to create a spreadsheet which has formulas which would enable me to see if a patient has had more than one referral created within the next 30-90 days [the actual number of days not important - the ability to modify the formula to reflect which time period required would be cool] and also to see if a patient has been referred for the same medical condition again. Thus in my mind at least two different formulas - has the patient been referred again within a time period of days and has the patient been referred again for the same medical condition.
Attached is an example referral spreadsheet I have created
how to make conditional formatting for border line (top & bottom) after repeat 5 data/text ....
it's possible with CF formula and do not vba?
see my excel file attached..
how I can have a formula repeat down a column five times before it changes to another formula? For example. Say on tab 1 I have a list of products. On tab two I have five codes that repeat down the page over and over again. On tab two next to the repeating codes I need to repeat product one 5 times and then skip to product 2 on the 6th row and repeat five times and then skip to product 3 on the 11th row and repeat 5 times and so on?
View 10 Replies View RelatedI need to repeat the following rows and formula with column a starting at 9150 and going through 15,000 in 50 point increments. Need formula I can copy and paste easly. see the attached example.
View 4 Replies View RelatedI can get only so far, then stumped:3 columns (Hours, Rate, Amount). "Hours" is a 'List' with data from from another sheet. Data is named 'Worked'. 'Worked' is all 2 decimal numeric (represents total time worked), except first item called "Live In"
IF 'Hours' is "Live In", I can use: =IF(A1="Live In", SUM(B1*1))
How can I make it conditional so that if it's not "Live In", then it will SUM(A1*B1)?. SideNote: 'Worked' LIST has total time with minutes expressed as 1/4 of hour (i.e., 1.25 = 1 hour, 15 minutes).
Keep getting compile error.
Sub CreatingCT()
'
' CreatingCT Macro
Dim ConsolidatedTrend As String
'Path to File
ConsolidatedTrend = "(d:2013"&(IIF((MONTH(NOW())+1>9),"(1","(0")&(MONTH(NOW())+1)&") "&TEXT(DATESerial(YEAR(NOW()),(MONTH(NOW())+1),1),"MMM")&"Consolidated Trend - "&YEAR(NOW())&" "&MONTH(NOW())+1&" + "&(12-(MONTH(NOW())+1))).xlsb)"
ActiveWorkbook.SaveAs Filename:=ConsolidatedTrend, FileFormat:=xlExcel12, CreateBackup:=True
End Sub
I would like to repeat a formula and make its dynamic depending on the request.
The formular that I would like to repeat is:
=IF(ISERROR(INDEX(Data!$A$2:$G$14997,SMALL(IF(Data!$A$2:$A$14997=$D$2,ROW(Data!$A$2:$A$14997)),ROW(985:985))-1,3)),"",INDEX(Data!$A$2:$G$14997,SMALL(IF(Data!$A$2:$A$14997=$D$2,ROW(Data!$A$2:$A$14997)),ROW(985:985))-1,3))
I'll try to explain this the best I can. What I have here is a time study. Teachers (which are the control numbers) fill out bubble sheets, then I run them through a scanner. After dissecting the data from the scanner and formatting it to my liking i get this below.
Each teacher/staff member fills out three sheets per quarter. Each letter (bubble) counts as a 0:15 min period of time. Only K thru Q counts as billable time, which I've created a formula to count those letters (column 3). But to be countable each control number has to have three cycles 201,202,203.
So I need something that can take each control number that has three cycles and add their # of 0:15 together.
The italic row below only has one cycle for that control number, so that needs to be deleted or ignored.
Ultimately I would like the results on a separate sheet.
This is only a part of the file, there are over 1000 different control numbers.
I have a sheet using VLookup to find EMail and Web addresses. I can get the address to show up but not as an active URL address. Is it possible to have the address "active" so I can click on it and activate the EMail or Web Site?
View 5 Replies View RelatedI need one formula to copy and paste the checkcard data changing it from this "CHECKCARD XXXXXX5623" to "CHECKCARD 5623", but I need one formula to look for multiple known checkcard numbers. I will have more then one typically in the bank download so I need to search and copy at least two known numbers as shown below.
I am providing the formula that I am using right now, "=IF(ISNUMBER(SEARCH("5623",B3)),RIGHT(B3,41),"")" but it only works for one checkcard number? Also I don't really need to capture the DEBIT or CREDIT that will always be at the end, it would be redundant. If it's easier to write the formula including that data that is no problem. What I want to copy and move into an adjacent cell is in red.
Here is the source data:
Row 2 - SONIC DRIVE IN#1531 AUSTIN TXDEBIT FOR CHECKCARD XXXXXX5623 DEBIT
Row 3 - JASON'S DELI # 125 Q64 AUSTIN TXDEBIT FOR CHECKCARD XXXXXX9718 DEBIT
I have spent the last week searching this forum to find the solutions to my various problems, I'm in mental meltdown
I don't think that I can explain here what it is I need so I have attached a sample workbook/spreadsheets.
I have NO experience of Excel and have had a little help developing the attachment, my assistant has now gone AWOL