we have an Invoice workbook "SampleInvoice.xls" with a drop-down list to select from a list of products, listed within the Data workbook "SampleData.xls" (which includes descriptions, codes, prices related to that product). So far, when you select the product, the macro will locate the product inside the Data Workbook, and return the Description of the product to the same cell in the drop-down cell.
My question is:
1) The cell to the left should bring up the Code for that product automatically
2) The cell to the right should display the Price. But there's a catch - there is a Distributor, Trade and Retail price. Ideally that cell will be a drop-down menu which gives the 3 pricing options. When you select the suitable field, the macro will determine the Code (as identified in point 1) and match the suitable pricing accordingly. Here is the code so far:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Goto errHandler
If Target.Cells.Count > 1 Then Goto exitHandler
If Target.Column = 2 Then
If Target.Value = "" Then Goto exitHandler
Application.EnableEvents = False
Target.Value = Workbooks("SampleData.xls").Worksheets("Stock List").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Workbooks("SampleData.xls").Worksheets("Stock List").Range("B1:B500"), 0), 0)
End If...........................
Combobox2 filters listbox2 which is pulled from sheet2. How do I get the X to return the value to the relevant cell on sheet 2, ie create a relevant listindex for the listbox
Also, one other minor thing, why selecting the last record in the listbox doesnt write to the worksheet?
The database is for a Prison, and an obvious item to search for on a userform is Prison number. I have a serach option that generates an array listing in a listbox, selecting that item then populates the userform - no problem.
The problem occurs when an individual has more than once instance that has been recorded, is it possible to have more than one row generated in a listbox that can be selected, I only want indiviudal rows selected. The coding I am using for one of the single instance listbox is etc etc etc has been used to demonstrate that the coding continues to the length of the array requied
Private Sub FindAll() Dim FirstAddress As String Dim strFind As String 'what to find Dim rSearch As Range 'range to search
Dim fndA, fndB, fndC, fndD, fndE, 'etc etc etc' As String
Dim i As Integer i = 1 Set rSearch = Sheet1.Range("ChargeNo.") strFind = Me.CbAdjFind.Value 'find Charge No With rSearch Set c = .Find(strFind, LookIn:=xlValues)..............
i have a workbook blank template for my colleagues to fill in, they fill in this blank template and save it under the serial number of the item they are creating. However a lot of errors exist when they input the serial number inside the workbook where the serial number is a duplicate of a former serial number.
Anyhow i was wondering is there any way when they input the serial number into the worksheet that i could get it to save in a separate workbook and then have conditional formatting to check those serial numbers in the workbook to see if they match?
I have an existing table with times on the left most colum and diferent areas and events on the next 12 meaning a count of events and outcomes for 6 areas per hour. (X=QTY.throughputs; Y=QTY. Errors) This would be the headers.
Time, XY, XY, XY, XY, XY, XY.
I need a formula to return the max "X" but also return the time it was recorded.
I need the formula for statistical data analysis for a research project i'm helping out with, and this is too complicated for me to know where to even begin searching for the information. The problem is this:
I have a sheet (called Sheet1) containing 9009 rows of information on columns A to AL. I am interested in searching for the starting sequences of codes contained in column AI and returning values on the same row but in a different column. The formula that works so far is this (note: I use Excel 2003 and a swedish version, translated the formulas though)
I havent come up with this formula entirely on my own so dont think my expertise is this high In any case, this formula gives me the value of the A-column in the same row as the first N06 found in the AI column. If N06 isn't found anywhere in AI, it gives me "error". The problem is this: I want it to "continue" searching column AI for strings starting with N06 and returning the values in the A-column for the following N06s found. I basically want to wash out all rows of information that dont contain the string N06 in AI, and get the new information on another sheet.
To make it even clearer: I want to have all values on column A on a new sheet, but only where the AI value on the same row starts with N06. Eventually I want other columns as well, and also search for other codes, but that should be easy to figure out once I get the initial formula.
If column F, G, H says PAP then I want it to return information from column A, B, C, D, E respectively into another worksheet. I have attached a sample
i want is to create a search engine in excel that allows users to input the clients name and the search will return all the info on the client such as the issue and their policy number.
Is this even possible? Using Access is not an option. I know I can use Access but most of my users dont know how to use Access and I want to make things as simple as possible.
Thanks so much in advance who ever succeeds at provding an answer u're a genius cuz I have no clue where to start lol.
I wos wondering if anyone knows how to return the information that is in the cell to a textbox. Ex. i have text in b5 and when i activate my userform and enter in text it erases my text from befor.
In one worksheet there is a code for every customer and their details e.g. Name, medication they are on, what they are alergic to etc then in another new worksheet can we just type in that code and their individual information comes up automatically?
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 to copy a column from another workbook and past in to the column in this workbook. However the column i am copying are all referenced from other cells. Is there a way to just copy the numbers and move them into this workbook to avoid double data entry?
I am attaching the 2 work books.
1. Is MSP commissions Structure that is where the data needs to be pasted 2. Grace - this is an example of what we will need to be copying from.
On Grace - copy Column G, Rows 65-81... The paste those numbers into MSP commissions structure column D, Rows 9-25......
We will have a file similar to Grace for EVERY deal closed....FYI so this process if possible needs to be replicated many times.
I have a workbook which contains information that I would like to transfer to a new spreadsheet.
If column I (in the exsisting workbook) contains the words 'New Business' I would like the entire row (all values) to transfer to the new spreadsheet. I need the information from the entire workbook (containing 12 worksheets) to transfer over to the new spreadsheet if column I states 'New Business'.
I have tried many things, most recently VLOOKUP, but with no luck at all.
I understand that a macro is the best option, however my excel skills are nowhere near advanced enough to know where to begin
I am working with two files, both files have multiple worksheets. File A has a worksheet that is filled out almost entirely by equations. I need to move the information from that worksheet into file B. In file B i will process the information further. Also, I will print it and it has to look exactly the way it does in file A. I want all this to happen automatically so I am looking for some sort of function or script that will perform this task.
I am trying to get this to sort out to a new workbook instead of a worksheet. There are only two components in C column, so there should only be 2 new workbooks created. I am a little unfamiliar with the terminology for the workbooks.
Can someone help me i have a few columns of information i need to move over to a another sheet, but the problem is the information i need to move is the end result of a formula and when i move it the answers are gone how do i fix this,,,,, and both sheets are on the same work book as well is there a vlookup formula to use,,,im not experienced in Vlookup up formulas
I have a Macro with a main goal to download info from another workbook and reconcile it... the information being copied is always from a workbook called book1 so the following code is what i am using:
I have 2 separate workbooks that are linked together and work in tandem. One is the Master Workbook "Master Business Plan Worksheet For Sale.xlsm" and the other is the one containing all the Menu Items for the business "Menu Items Workbook.xlsm"
I have a Master Recipe Sheet that I created in the Master Workbook and made sure it all works. Now I have copied it to the Menu Items workbook and copied all the Userforms and created Macros for it but I need setting up the references to the other sheet. I have included the code from the first sheet for you to look over. The textboxes all need to populate to the Master Workbook and all the info for the combo boxes are also in the Master Workbook.
Code:
Private Sub UserForm_Initialize() 'Populate Measurement combo box. Dim rngMeasurement As Range Dim ws As Worksheet
Is it possible to automatically have columns repeat through all sheets in a workbook? I keep an annual book, broken down by sheets for each month. I always have to add 5 columns to each sheet, and was wondering if there was an easier way..?
We have a Shared workbook on our Server and about 5 users work at the same time every day. I want to understand something, If I open it I can see all the information in it, all the rows and so on, but if the same workbook is opened by another user he will see other information, the rows aren't the same as in my case. Why does this happen ? I would like the second users to see the same information as I do.
Is it possible to VLookUp Data between multiple workbooks (12 different workbooks to be exact) in one master sheet?
I would like the master sheet to find the empty data for the item number. Basically, how can I automatically populate the empty data for each item number in the master sheet. Every item will be different. The 12 workbooks are invoices throughout the year (Jan, Feb, Mar, Apr, etc) and these workbooks have the data needed to fill the master sheet. How is this possible? Or is it not?
Master sheet.xlsx -Contains item number -Purchase Date -Sell Date -Purchase Price -Sell Price -Profit Price
The 12 workbooks are the 12 months out of the year, which are invoices.
- Contain the data needed in the master sheet such as sell date, sold price, and profit.
The reason I would like to keep the 12 invoices as workbooks instead of worksheets in 1 big file, is due to the high capacity of item numbers each monthly invoice would have. For example (approximately 500 items in each workbook)
I have for hours and weeks, actually months, But I haven't found any sources on the internet, although I have seen the VLook Up how to's on youtube between 2 workbooks, but not the 12 I would like.
I'm having a problem with getting a result from another workbook. The purpose is to extract information from a closed workbook representing data from a country (ie US.xlsm, UK.xlsm) into the open one (summary). I want to count the number of results in a column which are above a certain amount (in this case 100).
Which works no problem for getting the number of results in the E column above 100. my issue is that I want to be able to change the name of the country in a cell, and then get the results automatically from the relevant workbook. I've been using CONCATENATE for these purposes and EVAL (from MOREFUNC)... and I get #REF!
So if I split up the above coding into two cells "=" and "SUMPRODUCT(--(('C:address[US.xlsm]Worksheet name'!$E$2:$E$30000)>100))" (just to keep it simple) and then use CONCATENATE to combine them in a cell. Using EVAL on that cell gives me #REF!
I've tested SUMPRODUCT with EVAL and CONCATENATE on formulae like the one above which do not link to other workbooks, and they seem fine. and SUMPRODUCT and linking to a workbook also works fine in the first example.
So the problem is somehow coming from the combination of SUMPRODUCT, EVAL and accessing an external sheet.
I have code that i use to copy a worksheet of information in one workbook to a worksheet in another workbook. All i need is some guidance on how to copy a second worksheet from workbook 1 to a second worksheet in workbook 2. Should be fairl straight forward.
In the below i am copying the sheet Phone_data to a second work book sheet also called Phone_Data, i would like to include in the same proccess a sheet called Sur_Data from workbook 1 copy to a sheet called Sur_Data_R in the second. All the other features like find next empty row also apply.
Create a macro button in 'Spreadsheet 2' that searches 'Spreadsheet 1' for updated information specific to a certain criteria and adds it to a new row in 'Spreadsheet 2.'
There are three sheets
Prospects (where all original data is entered)
Actions -Bob (Bob's new Prospects are added to this sheet)
Actions -Frank (Frank's new Prospects are added to this sheet)
So in this example Row 6 in Prospects (Constituents, Rating, Manager and Solicitor would be added to ACTION - Bob's sheet on Row 5 and Row 8 in Prospects (Constituents, its Rating, Manager and Solicitor would be added to ACTIONS - Frank sheet on Row 5
Here is the example spreadsheet - Prospects and Actions.xlsx
I have created a userform that allows input of information and then deposits the information on a specific sheet. I am looking for a way to have that information not only deposited on the specific sheet it is already set to but also to another sheet based on a selection made from a combo box.
I have a task to rank the stocks from the highest price to the lowest price. I've made a template and managed to get the ranking right. I used the LARGE() function.
But now the difficulty is to get the 'Stock name' related to the ranked price. I may do it with the MATCH() function. But the problem is there is an equal number 15%.
I've tried LOOKUP, HLOOKUP, VLOOKUP, MATCH, SUMIF, ETC... Nothing working correctly. Here's what I need the function/formula to do: Find a # number value in column C, and then whatever text in another column ( range) I specify (like column A) corresponds with that same ROW. For example: VALUE: $40.00
1. Search column C3:C40 to look for $40.00- let's say it finds the value $40.00 in cell C21.
2. In cell A21 there is text "Gena's Sales".
I want the new cell the return a value of "Gena's Sales" because the $40.00 is in the same ROW as "Gena's Sales."
I think the reason I'm having such a problem is because it's numbers and text combined, and also LOOKUP does this "ascending order" thing that screws up the output. I'm getting values with LOOKUP from column A, just not the right ones. I don't need any ascending or descending orders. I just need to find the value in a range, then find the text in another range in the same ROW.
