Here's what I'm trying to do hopefully you can help me.
1) Given value in Sheet 1: Column A, search for that value in Sheet 2: Columns A:C. Then output the value in Sheet 2: Column D of the same row, to Sheet 1: Column B
2) Same setup as above, but the same given value is found multiple times, then have say 1st value output to column B, second to C, third to D, and so on.
I have a spreadsheet (called “output”) with a single list of unique account numbers in column A. In columns B, C, D (column B = Invoice, column C = Sales, column D = class), I need to add information from a data file based upon certain search criteria which is as follows:
For each account number I need to list an invoice number and the corresponding sales amount and class. I need to list the invoice with the “highest” sales amount. There is only one catch to the issue I am encountering and this is I need to first look for the highest amount in Class A sales, if no Class A sales, then look for the highest in Class B sales….and then Class C….and then Class D. If there are no sales in Class A, B, C, or D, then return a value of “no match found”.
I have a formula in column J that is the following:
=IF(I541="",IF(ISNUMBER(SEARCH(LEFT(G541,4)&"*"&RIGHT(G541,4),H541)),G541,""),"")
See screenshot:
I need to replace "G542" in the formula above. For example, in cell J541 shown above, I need it to output "1996-1999|2000-2001" instead of "1996-2001" I need the output to include LEFT(G541,4)&"*"&RIGHT(G541,4)
Trying to look for formula for searching through Range and search through Data to achieve expected res
Data Output ExpectedRange
Pune 123 is city Pune 123 Mumbai 999
Maharashtra 345 is state Maharashtra 345Pune 123
India 678 is Country India 678 Nagpur 666
Maharashtra 666 is state OthersPune 555
Below is the query i used for manual search however need formula to see through Range and achieve output expected
=IF(ISNUMBER(SEARCH("Pune 123",A5)),C5,
IF(ISNUMBER(SEARCH("Maharashtra 345",A6)),C6,
IF(ISNUMBER(SEARCH("India 678",A7)),C7,"Others")))
Im am trying to create a search marco button that allows me to search in multiple worksheets in one work book. I came across this CODE the first part of it works. It pops open user input box and ask for the word that i would like to search but the this error message pops up Runtime error1004 Method 'range" of object'_Global'failed and i dont know what to do
Private Sub SearchButton_Click()
SearchString = InputBox("Enter Search String", "Search")
If SearchString = "" Then Exit Sub
For Each c In Range(myRange)
If InStr(LCase(CStr(c)), LCase(SearchString)) Then
[Code]....
I have a workbook with many many sheets in it. The first sheet contains a single column with about 10,000 different values. I'd like to use each of these as search criteria against ALL data in the other sheets (of which there are a good 50 or so). If matches are found (they don't have to be exact case), then I'd like two things to happen:
1. The rows containing the matched search criteria in the first sheet are highlighted.
2. In the cells adjacent to the search criteria in the first sheet, hyperlinks to the matched data are created and named after the sheet upon which this matched data appears.
I've attached a sample file to this post with ideal sample 'answers' to queries made of the first 2 terms.
Using the search macro code below, could someone please help to add in more codes what I'm currently using, and also where to insert it. The Search function works well for what I need and it helps me to locate data. When using the search function somehow it search all sheets within the workbook but I only want it to search an array of sheets when using this macro that is needed to complete the task for what I'm after.
Macro
Public Sub FindText()
'Run from standard module, like: Module1.
Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer
myText = InputBox("Enter the text that you want to search for:", "Start Search!")
If myText = "" Then Exit Sub...................
I'm from Bavaria, Germany. Right now, I am doing an internship for my studies. my problem: I need a search procedure which shows an Input Box where you can enter a word to search for. It should work like the original Excel search (Ctrl-F), but with a simpler design, like with my own Text "Enter your Query" and a Button "Submit Query" / "Quit search". Is there the possibility to Highlight the Search Target? The problem hereby is that this highlighting should not be permanent. That means the user sees the target for which he searched for, the cell highlighted in a different color, etc. But as soon as the user clicks onto another cell, etc., the highlighting vanishes. If there is no fitting match, there should be a MessageBox like "Sorry the Target xyz cannot be found"
View 9 Replies View RelatedWhat 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.)
I' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells.
I can get it to work to search one cell (as below):
=IF(ISNUMBER(SEARCH("Same",G10)),"No Change",'Aug 09 Matrix'!F10)
(In this case the respone is No Change as Cell G10 contains "Same")
However cannot get it to work for several cells. I have pasted below and highlighted the function I would like it to perform:
=IF(ISNUMBER(SEARCH("Same",G10:R10)),"No Change",'Aug 09 Matrix'!F10)
Essentially the other cells in that row (G10:R10) all contain "Same", however for some reason it is identifying this as FALSE and putting in the data from the 'Aug 09 Matrix' sheet.
I 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 seem cannot get the code right, see below.
Cell: A3= Week 5
=VLOOKUP(RIGHT($A$3,LEN($A$3)-5),Lane!$A$9:$AD$20,3)
It won't work....
However, if I tried as follows,
=VLOOKUP(5,Lane!$A$9:$AD$20,3) OR
=RIGHT($A$3,LEN($A$3)-5)
Both works fine!!!
But I do rather something like as it is shown on the above in RED TEXT
Also I am worried if I have Week 10 the output will look as 0 instead of 10 so.
I am using the following function quite frequently: (INDEX(DJ:DJ,MATCH($GJ3,DC:DC,1),1). Because of the way the data is organized the function is commonly undefined (output="#N/A"). I would like the program to print a constant (718) when the function is undefined. Below is the function that seemed intuitive to me, but doesn't do what I want.
=IF(INDEX(DJ:DJ,MATCH($GJ3,DC:DC,1),1)="#N/A", 718, INDEX(DJ:DJ,MATCH($GJ3,DC:DC,1),1))
I am using one file, in that out put is coming but some times it is showing out put as -1.
Please find the attachment.
In the Sheet 1 Column X some out puts are coming as -1, what is the error in my formula.
Platform Open tickets on 29.01.2014.xlsx‎
I'm trying to create an If then statement that will will allow multiple criteria and output the corresponding data if possible.
I've attached a sample data file to provide insight. I've also used a random number generator formula to create the numbers for the output. When I press F9 I'd like the data to appear in the dream house lottery section with the information of the house that corresponds the Neighborhood and street. What I have in mind below.
If C21=B2,B8,B13 and C22=B3,B4,B5,B9,B10,B11,B14,B15,B16, Then values of E,F,G,H,I appear in E,E,G,H,I appear in the lottery dream house section.
So =IF(C21=B8),IF(C22=B11), ????
IF(C21=2) and (C22=3), Then This is where my problem occurs. I'd like my output to be that of E through I11 to appear in E through I21.
Is that possible and does that make sense. I have a long winded version but it requires the formula to be in each and every cell and it will only allow for one neighborhood at a time.
in cell d20 i have a contract date
in cell d21 i have a lead time
this will always be in the format eg(2-3 weeks)
in cell d22 i would like it to tell me d20 plus the first number of weeks ( before the hyphen)
and in d23 the second number of the weeksafter the hyphen
I am running a macro in Excel that when runs opens a message box that asks you to enter a start date, then and end date - this works perfectly.
However, what I now need to look at is adding a message box BEFORE these two fields that asks for a Sales Account Refrence number.
Once this number has been entered it then runs the date range boxes against that number entered.
Below is my code that currently works for the date boxes.
It is running a SQL string and the Sales Account Rerefence number is a field called org.sales_acc_ref
Sub GetData()
Dim create_timestamp As Date
Dim response As String[code].....
I have a function which either looks up a value on a spreadsheet or runs a sub. If it runs the sub, it should generate a list of figures which are output to a spreadsheet.
My function keeps crashing but I have found by stepping through it that is correctly decides whether to lookup a value or run the sub. It starts the sub correctly but when it comes to outputting information to the spreadsheet it crashes. I have lx_calc set as an array and I am using the following to output to a spreadsheet (lx_calc(20) is defined as 10000 and ImpFac and qx have already been calculated):
For i = 21 To 120
lx_calc(i) = lx_calc(i - 1) * (1 - (1 - ImpFac(i - 1)) * qx(i - 21))
Workbooks("Macro Ann Addin.xls").Worksheets("Mbr").Cells(i + 1, 1) = lx_calc(i)
Next
I can't see why this doesn't work unless it is just that it doesn't like the fact it is looking at an array.
I am making a report that puts out a CSV file my trouble is that some of the numbers are converted to date when they are opened in excel. This report is sent out company wide and I need a way to keep the numbers from changing to date. I have added a tic(') mark to the front of the number but it comes over as '12308. What can I do.
View 9 Replies View Relatedattached spreadsheet. Notice cells A11 through B23.
Is there any third-party extensions, Macros or other functions that can output the dates and corresponding text (in column A) to a nice looking calendar, or even better yet to Outlook, or both?
I tried creating a pie chart or graph function, but non contain a calendar scheme.
Notice cell B7 is an input cell. This would determine the number of days the calendar would show in full. The begin date of the calendar would be in B11, and the last date on the calendar would be in B23. It could be a 30 day calendar, or more or less depending on the input.
For those "visual" people, a calendar format would be amazing, but I can't write a single peice of VB code, so I wouldn't know how to write a Macro.
I'm wondering if it's possible to use a button in excel where I can output a cvs file (from another sheet in the file) with just a click on that button.
View 3 Replies View RelatedI have a userform that employees use to enter production data every day. Originally I had this storing the data in excel, but now I would like it to put the data in tables in access.
Here is some code I am trying, but it's giving me an error: "Run-time Error '3251' Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype."
VB:
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cnt As New ADODB.Connection
[Code].....
i have the formula below,
currently the output is comming as a number but i need the output to come has a percentage what adjustments do i need to make to do this?
=CHAR(149)&" "&"The overall YTD"&" "&"performance is"&('Red Activity & Performance'!R45)*100&IF(INDEX('Red Activity & Performance'!R45:R55,MATCH("Red 1 8min",'Red Activity & Performance'!$A$45:$A$55,0),MATCH("YTD",'Red Activity & Performance'!$R$44,0))<INDEX('Red Activity & Performance'!$C$45:$C$55,MATCH("Red 1 8min",'Red Activity & Performance'!$A$45:$A$55,0)),"Is below the target (75%) by: "&TEXT(INDEX('Red Activity & Performance'!$A$45:$A$55,MATCH("Red 1 8min",'Red Activity & Performance'!$A$45:$A$55,0))-INDEX('Red Activity & Performance'!$R$44,MATCH("Red 1 8min",'Red Activity & Performance'!$A$45:$A$55,0),MATCH("YTD",'Red Activity & Performance'!$R$44,0)),"0%"),"has achieved the national target (75%)")
I'm trying to accomplish the attached. This is just an example of what I want to do. There are no formulas in this spreadsheet.
Spreadsheet 1: Search by Number
Column A is a list of names
Column B is a list of numbers
Column C is a list of the names that correspond to the number "1" from Column B. Note that there are no blank rows between the names.
Spreadsheet 2: Search by Name
Column A is a list of names
Column B is a list of offices
Column C is a list of the names that correspond to office "Williams" from Column B. Note that there are no blank rows between the names.
I have used the index & match formulas to do this WITH duplicates or blank rows, but I would like to produce this without duplicates. I am willing to get as complicated as need be to make this happen.
how to consolidate file01 to file02 merging them, resulting the expected output sheet. Currently im doing this manually and it really take up a lot of my time this is just a sample scenario. My real world use of this is composed of over 2000+ accounts with different product purchases and billing numbers.
View 14 Replies View RelatedI want to compare and output the differences in text in 2 cells.
ie. Cell A1 has a long string of text as does Cell A2 but slightly different, I want to see exactly what the differences are.
Is there a formula I can use?
I need the output in B1 to be in multiples of the value in A1, rounded up.
example:
Value in A1 is 6
random formula in B1: =IF(AND(N2>$AA$1,S2>0),ROUND((30/$AA$1)*AA2-Z2,0),IF(AND(N2>$AA$1,S2=""),ROUND((30/$AA$1)*AA2-Z2,0),IF(AND(N2<$AD$1,S2=""),ROUND((30/$AD$1)*AD2-Z2,0),IF(AND(N2<$AC$1,S2=""),ROUND((30/$AC$1)*AC2-Z2,0),IF(AND(N2<$AB$1,S2=""),ROUND((30/$AB$1)*AB2-Z2,0),IF(AND(N2=M2,N2<$AA$1,S2>0),ROUND(30*(AA2/N2)-Z2,0),IF(AND(N2<>M2,N2<$AA$1,S2>0),ROUND(AA2*(30/$AA$1)-Z2,0))))))))
So if the resulting value of my B1 formula is 2.33, I'd like to have the output be 6. If the resulting value is 14.55, I'd like the output to be 18.
I have a formula in sheet code to get a total of the following columns. The formula in these columns is not working. I'm not getting any error. The code is working fine without a require output.
Attached File : Total.xlsm‎
I want to superscript the text with red color. But now only a active cell is change to superscript with red color after running the macro.
View 2 Replies View RelatedHow do you write a code that will calculate a formula and output the result in a cell?
For example:
A1 = 1 (Named "Cell1")
A2 = 2 (Named "Cell2")
A3 = Results of macro for Cell1+Cell2 = 3
Is there a way to write it if I also want B3 = B1 + B2 and C3 = C1 + C2, etc.