When I type in a ticker into TextBox2 the form is prepopulated. Sometimes the info that is prepopulated is incorrect and the user should just be able to click in the textboxes and make the changes. The problem is that when the user does click on the boxes and makes the changes and hits submit the combobox values do not change and stay that of the vLookup results. So, the vLookup results can't change even if the user makes the change and hits submit. What am I missing. Do I have to declare somehwere that ComboBox1.value (for instance) is actually the value as of the submit button ebing clicked and not when the vlookup happens?
Private Sub TextBox2_Change()
On Error Resume Next
TextBox1.Value = WorksheetFunction.VLookup(TextBox2.Value, Range("Data_Company_Names"), 2, False)
ComboBox1.Value = WorksheetFunction.VLookup(TextBox2.Value, Range("Data_Company_Names"), 4, False)
ComboBox8.Value = WorksheetFunction.VLookup(TextBox2.Value, Range("Data_Company_Names"), 3, False)
ComboBox11.Value = WorksheetFunction.VLookup(TextBox2.Value, Range("Data_Company_Names"), 5, False)
On Error Goto 0
End Sub
This may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
I'm putting together a spreadsheet to do with football betting, that involves calculating various stakes and odds and probabilities. I've done pretty well, but I want to take it to the next level.
At the moment, I have 4 sheets which are completely self contained:
sheet one = betting on 2 matches sheet two = betting on 3 matches etc
This was the easiest way to do things when I was learning.
Now I want to look at incorporating more flexibility into my spreadsheet. I want to investigate the possibilities of having a small form to be filled in, such as the following:
Number of matches to bet on... a drop down menu for the user to choose from
and then for the visible area of the sheet to change to show the appropriate layout for their selection. So they'd choose the number of matches they want to bet on, and the spreadsheet would display what they need automatically.
I've looked at putting forms in and think I could do that quite easily, but how can achieve the effect of changing the layout/visible area depending on the chosen option?
I set up a range of cells to show a form when they are double clicked. This form gives the user the choice to pick a color and text from a list. When the user clicks "OK" the form closes and changes the cell based on the user's input (i.e. Red, "N/A"). I want only the double cliked cell to change color and text.
Problem: My code runs fine, but I cannot find a way to let the form change the text of the cell. Only the color is changing. I have tried calling the cell out in the code, but I get an error. I cannot find a way to change the text in the cell.
Code: Private Sub CommandButton2_Click() Dim ColorChosen As String Dim WordChosen As String[code].....
I want to merge two tables with keeping the data in the two tables in two worksheets, so my idea is to create a drop list in one of the two worksheets and depending on what the user will chose the datas will appear. for informations, the datas are all Vlookup in the same workzone but for each table the lookup will be done in differents columns, so how to make the column for the vlookup changement and depend on what I choose??
clarifications: it's like the choice on the droplist will be a link cell to the column of the research.
1. An amount of money is invested on the first day of any given month of the year;
2. This investment earns interest (calculated at a nominal , not effective, rate) which is credited each month in arrears;
3. Once the interest is credited, it is withdrawn and the effective original investment amount is carried forward to earn interest again (which is yet again withdrawn) until the investment matures in, say, 24 months, when the capital is paid out;
4. On each withdrawal, a certain portion is (with this specific investment, in case you wondered!) susceptible to certain Taxes, which I want to calculate for each Tax year (or portion thereof)
In South Africa, the standard Income Tax year runs from 1 March through 28(9) February of the next year.
I am trying to figure out a formula that will calculate the Taxable Portion of the withdrawal for each Tax year (actually, it may even be done for each month and simply totalled in February for the previous Tax year / portion thereof) until the investment matures.
If an investment is made on 1 June, I need to sum the payments from 06 to end 02 (Tax year1), then sum the totals for months 03 to 02 of the next Tax year and then sum the totals for months 03 to 05 (i.e. 31/05 - ending the 24 month investment period) for the third Tax year.
What can I do do allow for this ever changing differing in the inception month of an investment?
I've attached a s/ sheet where I've used all sorts of statements to mark the inception month (year1) as "Mark1", month 2 as "Mark2", month 3 as "Mark3" and the last month as "Mark4", I've used "running months" (1-24 in the example above) etc, but cannot figure out how to use any function I know to help!
Can I use a vlookup combined with IF function to lookup up to a different file dependent on what the cell says.
For Example cell a1 could have the value Alan Smith, Tony Brown or John Doe dependent on what value is entered the lookup would "lookup" against the file named Alan Smith, Tony Brown or John Doe.
I've 2 worksheets, Summary and Sales. I need to get the total sales figure from the "Sales" worksheet onto the Summary worksheet. Sales data are pasted onto the worksheet monthly. Tried using vlookup but problem is, the column containing the total sales figure may vary and I need to update the vlookup formula. Another problem is that sometimes the Total sales (the lookup value) is not in the first column. Tried using the Index and Match combo but cannot get it to work. I've attached a simple mock up to hopefully illustrate my case better. I believe the experts in this forum should find this rather easy!
I have about 85 files that I need to rename. These files contain data concerning delivery and quality data of each of our suppliers...1 file per supplier.
Current File Name: SupplierA 2012.xlsx New File Name: Supplier A 2013.xlsx
Is there a way to do mass rename the files..
and that leads to my second question...each of these files contain vlookups to 2012 data...which is contained in the a file with 2012 in the file name. I need to replace source data file (i.e. 2012 Index) with the new file containing the 2013 data (i.e 2013 Index). Is there a way to replace that source data across 85 or so files using some time of mass find and repalce? All the cell references remain the same
I am pasting vlookup formulas into a spreadsheet using a macro, and want to change the reference column number based on a variable generated within the code eg
= vlookup(RC1,table,i,false) where i is a predetermined variable in the code
As I copy and past my lookup formula down the page it is changing the lookup range which I think is what is giving me so many #N/A results. My first Formula is =IFERROR(VLOOKUP(A2,Coors2!A2:D3765,3,FALSE),IFERROR(VLOOKUP(A2,'AB2'!A2:C13944,3,FALSE),(VLOOKUP(A2 ,'WM2'!A2:C4843,3,FALSE))))
Then for instance at line 59 the formula is =IFERROR(VLOOKUP(A59,Coors2!A59:D3822,3,FALSE),IFERROR(VLOOKUP(A59,'AB2'!A59:C14001,3,FALSE),(VLOOKU P(A59,'WM2'!A59:C4900,3,FALSE))))
I'm working on an excel report that handles reporting for a large number of people. Another person at work is doing the VBZ coding, and I'm doing the formulas that aggregates the data. which has led to an interesting problem whenever I use a vlookup. I copied the sheets with the formulas from the test document I had been working on to an updated version. And all the vlookups automatically adjusted themselves to reference the document they had been created in. I had also moved over the tabs they were supposed to reference, but they keep looking at the old document. I spent an hour removing the references last night, but I don't want to do it again when I get the final product.
How to get excel to copy a formula EXACTLY as written, and not att in the name of the original document?
It should say =VLOOKUP($A438,totals!$A$1:$AH$63,32,FALSE)
and instead it says =VLOOKUP($A438,'[reporting formulas.xlsx]totals'!$A$1:$AH$63,32,FALSE)
This is the link to the thread where ByTheCringe2 showed me the power of SUMPRODUCT and sorted my problem out. I don't think you'll need it, but I'd rather reference it before Roy reprimands me! lol
I am building on the same topic, though.
Hi All!
I have an investment where the monthly interest is added back to the capital. The next month earns interest on the higher figure and this higher interest is again added onto the capital and the story starts all over at the next month. OK, that's easy to do (Again, I think!).
All works fine UNTIL the end of the Tax year is reached and Tax on the interest earned is payable from the investment as the capital carried forward is reduced by the Tax payable. In the attached file I show how I calculate the interest earned for each following Tax year (which always ends at month 2) using SUMPRODUCT. Sumproduct is used because I never know whether the initial investment is made in month 5, 11, or any other month and by using certain controls (thanks ByTheCringe2!) I can simply total the interest earned in each tax year - the months between month 3 (start) and 2 (end).
On a seperate sheet I calculate the Income Tax payable as follows:
Firstly, I calculate the standard Tax payable on the investor's income from other sources. I then add the taxable portion of the interest to the standard income from other sources and calculate the tax payable on that combined figure. I then deduct the two figures from each other and get the increase in Tax payable because of the interest earned.
This "increase in Tax payable"-figure is then returned to the spreadsheet on which the interest is added onto the capital. See Sheet2! cells O9:O15. The Tax payable for each Tax year is placed next to taxable values I calculate using SUMPRODUCT (again!) in column N9:N15.
VLOOKUP is then used to transfer this Tax payable figure into the calculations (Column K9:K68) as and when required (only when tax is payable) - and should be deducted from the value in (Same row) Column H and that answer should be transferred to next row Column E (Balance at beginning of month) -
BUT it causes CIRCULAR REFERENCE faults and I am absolutely frustrated at not being able to figure out how to sort it out!
Sheet2, Column E9:E69 (See Sheet2!E19 for Comment explanation) in the attached workbook.
I am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.
For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.
Sample:
John Japanese John Chinese - Simplified John Korean Martin Arabic Martin Chinese - Simplified Martin Russian Ramon Arabic Ramon Russian Sam Arabic Sam Chinese- Traditional
I was wondering whether there was any way to save the values put into a form, so that next time you open it the same values will appear - basically I want all the variables I type into my textboxes to be saved when a button on the form is pushed. But I can't find the magic words - I'll include the code so far below, but the crucial bit is missing (if it is possible at all)...
Private Sub btSaveDefaults_Click() Dim tb As TextBox For Each tb In fmImportSetup.Controls '*****HERES THE IMPORTANT BIT******* tb.??????? = tb.value Next End Sub
two formulas for one data set. The data is attached in the spreadsheet: "Product IDs". The data is a set of Master Product IDs (parent) and the Linked to them Products (children). I need to create a relationship between unique parents (Master Product IDs) and their children (Linked Products)
I need to create two formulas:
1. From the Data Set table, need to vlookup the unique value in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). So, the result will be as shown in Table 2.
2. From the Data Set table, need to vlookup the unique (de-duplicated) parent/children relationship in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). There are total 3 parent/children relationships in Table 1. So, the result will be as shown in Table 3.
Table 2 will be updated on a monthly basis with new patients and new visits. Table could also be updated if new clinics are added. The tables are from two different sources.
I need to combine the two tables so I can then pivot and have a sub-total per month, per site and grand total per month. I tried match, Vlookup, combining the two but I am getting lost.
I've been making a userform to enter data into a list of clients for our school counsellor. I've been using date picker to select the date, which works fine, but is a bit fiddly for a novice user (which the counsellor is) to jump down years.
My half-solution has been to change the data to a very rough guess of their data (so it's no more than a year or so away) - by taking their school year (years 7 to 11) in one combo box, adding 5, multiplying by 365 and taking the that number of days from the current date.
Private Sub CmbYear_Change() DTPicker1.Value = Date - ((5 + CmbYear.Value) * 365) End Sub And although it changes the content of the datepicker date, I get an error when I click my ADD button.
Run-time Error '13': Type Mismatch
And here's the code for my ADD button.
Private Sub CmdAdd_Click() ActiveWorkbook.Sheets("Clients").Activate
I am wanting to use a command button to bring up form {i have made the form and button for this]. Once it comes up, the user types in a date for which data point he will insert the usage. By pressing the execute button the user should be able to insert this value into the appropriate worksheet point. IN the end version on the form i will have multiple types of widgets. all that should happen is what the user inserts into the form will be inserted and replace the usage column value (i.e. column J of widget 1.45 WS). if the user leaves the box blank then nothing should happen to the relevent cell.
When I use the =RAND() function, everytime I do anything within my spreadsheet the number changes. Is there a way to get that number to change after(for example) 10 refreshes of my spreadsheet instead of every single time I do something to it?
I have two worksheets. One is called "Orders" and the other is "Invoice2".
On my "Orders" sheet I have a listed of product codes and ordered quantity. One column being ordered quantity and the other being product codes. For instance it is like this:
Product Codes Ordered Quantity
TH1 5 AB3 0 JC5 10 LFK 11
On my "Invoice2" sheet I have a column for product codes and ordered quantity. Basically, I want the "Invoice2" sheet to reference the "Orders" sheet in relation to these columns. However, on the invoice I only want to display the product codes ordered and ordered quantity. For instance TH1 would be the first line and JC5 the second line. This is because AB3 is less than 0. So ever product code on the "Invoice2" sheet must be greater than 0 in ordered quantity on the "Orders" Sheet. This is what the invoice2 sheet would look like:
I have a code that clears the content of a cell if the value of that cell exceeds two.
Code: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Cells(1, 1).Value > 2 Then Cells(1, 1).ClearContents End Sub
My problem is the value of this cell comes from a function and changes every second automatically (linked to a data feed). So I'm not changing it manually, therefore my code is not working unless I touch it. I just wanted to ask if I can add a code onto this which checks the values and runs the ClearContents macro if there is any change.. I've got a few sheets like this so I need this to run in the background..
i have a spreadsheet that has 3 shifts. its a weekly data sheet. i have 52 sheets for every week of the year. so far i have a "data" sheet aswell. this is the first sheet. I use this data sheet to generate the information or the other 52 sheets. example is you enter the date of the first monday of the year and it generates the dates on all of my sheets for the whole year. i would like to do something similar for the shifts. i would like to have a cell the enter the shift of the first supervisor on that first monday and have all the shifts generate. 1=nights,2=days,3=afternoons. but i do not want the number to appear i would like the word. the set up goes like this:
DATA E9 = the first number for shift to generate all other numbers
A5 = cell to display first shift A22= cell for the next shift A39= cell for last shift
on sheet1 i have A5 as =sum(DATA!E9)
i would like A22 to be the shift after example if A5 as 2 for days than A22 would be 3 for afternoons and A39 would be 1 for nights.
Of the form "packing list" page of the changes made to the necessary places "commertial Invoice" section is being transferred. So far, no problem. If you are loading only the truck. However, a truck, trailer or container are loading more than one, "packing list" Vehicle / Container Nos. are different. If you are loading more than one truck or container, "packing list" Vehicle / Container Nos. are different. This is not a problem for the packing list. But the "commertial Invoice" on the invoice values that must be unique.
For example, from "the product 2" 50 pieces laoded to the truck and trailer have loaded a 25 pieces, in the "commertial Invoice", from "product 2" needs to write 75 pieces.
My question to you is this:
In the "Packing List" find duplicate products, to "commertial Invoice" Can we write to collect the amount of write one?
Second question:
In the form comboboxes' chage events are the same. Can we make change events one code?
