I have information that I want to pull using an If, Then statement. The goal is to populate the information in column J with the information in column A by matching the information in columns B and C.
Is this possible with an If, Then function?
I would like to start with a spending value, (SV), that would be the amount of money that I want to spend in a year, not counting taxes. The function (or functions) would then go through some sort of iterative calculation process that would compute the total spending level including taxes, (TS), the federal tax, (FT), and the state tax, (ST).
These values would have the relationship that TS=SV+FT+ST. When the iteration was finished, the federal tax and state tax amounts would be consistent with the taxes that would be due for this level of total spending as determined by the normal tax tables for both federal and state taxes.
I am not concerned that the tax figures be absolutely exact, so a lot of iteration is probably not necessary.
Column A contains State names; column B contains a few cities for each of the states. Row 1 contains rank as the header row. Then the table begins in cell C2 with the rates. I have two of these (on separate sheets), one titled w dep (with dependants) and the other w-o dep (without dependants). On a third sheet I have 4 combo boxes.
1.Chooses between Dep or W-O Dep
2.Chooses Rank
3.Chooses State
4.Chooses City
Here are my questions.
•How can I filter down the cities so that if I chose Texas for example, only the cities from Texas would show up in my combo box?
•I have figured out the formula to display the results from the w dep sheet but cannot get the information from the w-o dep sheet. Here is the formula I’m using.......
Is it possible to use Sumifs to calculate multiple columns i.e. if a summary were able to sum Part No. 742810 O-rings for March it would arrive at 24 O-rings and 20 of the same for April. I tried it using
=SUMIFS(Issues!$F$2:$F$6,Issues!$B$2:$B$6,B8)
(Example) but it seems that Sumifs may have restrictions on the number of columns.
******** language="JavaScript" ************************************************************************>
Microsoft Excel - Parts Distribution.xlsm___Running: 14.0 :
OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutL2L3L4L5=ABCDEFGHIJKL1Parts Description Part No.EquipmentMar1Mar5Mar12Mar30Apr6Apr17Apr27May3
[Code] .........
It seemed to be working OK for the first hour I had my sheet open. I then noticed that a variation of the following Sub would not update unless I clicked somewhere else in the sheet or edited another non-included cell. It's like something is holding up Excel from running this function and updating the cells if b2 is greater than a3 then a3 will not update to = b2 immediately.
Private Sub Worksheet_Change(ByVal Target As Range)
If [b2] > [a3] Then [a3] = [b2]
End Sub
My data in column B is formula derived and that formula contains data that is live updated data brought in from DDE link.
My question is, should I setup this formula as part of a macro, instead of workbook code and attach an Application.Ontime function to ensure reliable execution? Could too many IF statements cause delays (about 100 of them currently in the code Private Sub Worksheet_Change(ByVal Target As Range))?
Is there a built-in dialog box that allows the user to select a range by clicking and dragging with the mouse? I'd like to use this in my code and have the dialog box return either a range object or and address that I can use in a range assignment statement. I'm looking for something like what you get when you select Insert-->Name-->Define. The "Refers To" text box at the bottom automatically updates as the user clicks and drags across cells. Is there such a thing accessible via VBA?
View 2 Replies View RelatedI am creating a spreadsheet utilizing the National Vulnerability Database (NVD) from NIST.
I am successfully able to import the xml files and have the xsd mapped fine.
My problem is within each xml files for each records(1000s of records) there are sub pieces to certain record parts. I.e. software versions (that will be different per piece of software) however they will always fall under prod vendor
"
"
Excel creates a new row for each of these.
How can I make it only create the 1 row and comma seperate those?
I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.
View 2 Replies View RelatedSheet one will contain the following:
DATE A B C D E
2/22/2008TRUEFALSEFALSEFALSERon
2/23/2008FALSETRUETRUETRUEPhill
2/24/2008FALSETRUEFALSEFALSETracy
2/25/2008FALSEFALSEFALSEFALSESharon
2/26/2008TRUETRUEFALSETRUEBill
On sheet two I need to list any date that has three or more true statements with the coresponding name.
EXAMPLE:
2/23/2008Phill
2/26/2008 Bill
How can I combine the two below statements like these to make one "Or Statement"? Sorry...I haven't done excel and/or's in forever?
=IF(('Proj Info'!L10="main")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
OR
=IF(('Proj Info'!L10="serv")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
I’m a fairly basic excel user, I think I have a fair idea of what I’m trying to achieve.
I’ve tried to take the time to explain my query so if anyone is happy to help, I’ll definitely take the time to give a decent response.
I’ve broken it down abit.
Background
I’ve created a form for which users select a series of drop down boxes, as each drop down box has a value selected, the options available for the proceeding drop downs are filtered and the options then become limited.
In order, the drop down boxes are;
1. Select a Utility – the data validation source list is “utility”GasElectricity2. Select a Distributor List of 3 Gas Distributors the data validation source list is “ElectDist”List of 5 Electricity Distributors the data validation source list is “GasDist”If user selects Gas as a Utility as per dropdown list #1, then the option shown in “GasDist” validation source list will appear.
If user selects Electricity as a Utility as per dropdown list #1, then the option shown in “ElectDist” Validation source list will appear.
3. Select a Service the data validation source would be either “Eservice” or “GService”Special Read Investigation4. Select a Sub Type the data validation source lists are either “EReadSub, GReadSub, EInvestSub, GInvestSub”
I have a column of numbers each have a 0. infront of them (example 0.2346
0.5698). I want to pull the number from the right until it hits the decimal sign. So for the two above the result would be 2346 and 5698
I have a spreadsheet that is updated weekly -- but every week new info is added that needs a user to input corresponding info. I use a vlookup function to link to another spreadsheet that populates the info from previous weeks and the info that is missing shows up as #N/A...
First I was using a msgbox function to get the info:
HTML Code:Â
For Each b In myrange
If Application.IsNA(b.Value) Then
Employee = b.Offset(0, -2).Value
SSID = InputBox("Please enter ID# for " & Employee & " :", "New Employee Found")
b.Value = SSID
End If
Next b
But it can be up to 30 different new employees... and that is time consuming.
I would like to make it more user friendly by creating ONE userform that displays all of the employees as labels -- has a text box in which to put the ID # -- and then has a drop down box to choose the type of employee (2 options). I want all of that info to go back to the reference spreadsheet so it will be saved for following weeks, and then redo the vlookup to get the info into the new weekly spreadsheet (I can do that part)....
HTML Code:Â
Private Sub CloseButton_Click()
Unload UserForm1
End Sub
Private Sub ComboBox1_Change()
[Code] ......
Looking for a formula to accomplish the following:
I'm trying to populate cell A31 on a worksheet titled "VolumeTotals" with the data in Cell E23 from a worksheet titled "CurrentCustomers" if the merged cells F3-F22 on worksheet "CurrentCustomers" are equal to the word "Contract".
I have an excel workbook with 2 worksheets. One worksheet shows the MASTER LIST of COMPLETE Customer Names (e.g. ABB Supplies Incorporated). The other worksheet has information on customers but the customer names typed in are incomplete (e.g. ABC Supplies). I need a macro that would look do a comparison of the customer names in the 2nd worksheet to the Master List worksheet and pull the data (complete name, address, etc.) for those that would match (partial match since company name is 2nd worksheet is usually incomplete).
View 2 Replies View RelatedI have one excel 2010 workbook with 5 work sheets, each work sheet contains a list with first/last name(one column) and the company name, some have a 3rd column with their email address in each sheet represents each year starting at 2008 thru to 2013 i have to find out if the people that attended an event in 2008 also attended it in 2009/10/11/12/13 and if they didnt, put their name and company name onto a blank worksheet within the same workbook without using a macro, how can i do this?
View 2 Replies View RelatedWhen I enter my sales data into a sheet it can be 10000 rows long, I want to be able to enter a set number of transactions on a second sheet which then uses a formula to look up what items was sold on said transaction.
I'm pretty sure it's possible but I'm out of my depth. I've using something like it before which was this statement - =IF($B1566="","",INDEX('RMS Sales'!P:P,MATCH($C1566,'RMS Sales'!$A:$A,0),1))
I've attached example sheet : For-Excel-Forum.xlsx
In column G, users select one of three values. I want a list of additional options (my named ranges) to appear in column H based on the selection of column G. I can get this to work when just referring to one of the named ranges, but not all three. I also do not want any of the data to appear if there is no selection in column G.
My not quite right code:IF($G$5="Code of Conduct",COCList,IF($G$5="Integrity",INTList,IF($G$5="Behavior",BEHList,"")))
I'm trying to copy one cell data from one sheet to another if it matches a ciritra but need multiplue arugments
i.e
if A1,sheet1 = Yes then I need to copy B1sheet1 into a say,a1sheet2, but if A1 = No then copy b1sheet1 into b1 sheet 2
So copying data from one sheet to another sheet, the if true copy to one cell in that sheet if false copy to another cell in that sheet
I want to pull data from another sheet, however there are multiple listings of each and I want to pull the data to another sheet for each line. I maybe easier if I try and show below:
Col. ACol. BCol. C
Smith101
Jones512
Green65
Black1214
Smith 3612
Jones1512
Dual25
Green1225
I want to pull this data for each name in Col. A in to another sheet. I've tried "IF", "Vlookup" and a couple of others, it just seems to be hitting the first option and pulling the data but not the ones below.
So if I selected Green on the second tab it brings through the "6" and "5", but not informtion from the listing from Green below. So I wiould like to list all the Green's, and the applicable data.
Is there a way to use Data Validation that will pull data from a source list and also be able to type in additional data or just new data in same cell?
View 3 Replies View RelatedI have a Pivot Table, with lots of data in, what I want to be able to do is on a separate sheet pull out the data from the first column(A), and then average out the figures from Columns(B-E).
When this has been done on the sheet (with the average data) in the next column, I then want to use Column(F) (from Pivot Table), and do a simple sum to work out 5% of the difference if it is greater than ZERO between Average Column and Column(F) (from pivot Table) Column(F) must be higher than the average in order to work out the 5%.
On Sheet1 I'm attempting to auto populate specific cells in columns B,C & D with information found in Data Validation lists (found on Sheet2) based on the "value" chosen from a list in column A. For instance,
If A2=Pig Then B2=Slop, C2=Pen, D2=Food
Is this best accomplished through VBA or a basic Function?
Excel spreadsheet will contain a form that will consist of drop down boxes (data validation). Each drop down box will define the data that can be selected in the next drop down box.
The way I initially went about it, is creating nested IF statements. However, we all know there is a limit of 7 nested IF statements. I have 10! So below formula does not work:
=if(B5=1,F5:I5, if(B5=2, F6:I6, if(B5=3, F7:H7, if(B5=4, F8:J8, if(B5=5, F9:H9, if(B5=6,F10:G10, if(B5=7,F11:H11, if(B5=8,F12:H12, if(B5=9, F13:I13, if(B5=10, F14:K14))))))))))
I tried other workarounds such as CONCATENATE, or & signs. No luck. VLOOKUP does not work also, because there are multiple columns in col_index_num. Anything else I try gives me this message:
“The List Source must be a delimited list, or a reference to a single row or column”
What should really happen is this:
User selects value in first list box. Second list box shows values associated wih the value from the first drop down only.
Is there a way to pull out all of the data from a row and place it into a cell out to the right of the range?
Some of the data is text and some of it is dates.
My problem is that it is a big spread sheet and the data is scattered in the sheet. Not uniform at all.
I've been playing around with VLOOPUP, MATCH, SUMPRODUCT... But I can't seem to get this one right..
See the attached Excel file..
I think it should be fairly explanatory.. I want to use the dates in SUMMARY sheet, to pull out the data in the DATA TABLE sheet. The numbers in Column A on the DATA TABLE sheet, is first date in each month, and the numbers represent the day of the month.. .
Example:
01.01.2009
1
2
3
4
5
..
30
01.02.2009
I don't know if this will require a macro or maybe a function of excel, but I have a large listing about 1000 row in spreadsheet. Each row correspond to a unique customer. But I will have to extract only the customer that I am looking for, which is about 30 of the 1000. The cell A1 is the customer # which will be use as reference, so I have about 30 customer # to pull out from that listing. Which will be the greatest method to do so?
Example:
A1 44334 ERIK JOHNSON 60$
A2 34555 KARIM EDWARD 50$
A3 32555 EDWARD CHRIS 65$
A4 55666 STEPH LAWRAW 45$
...
I want to pull 44334 and 55666 the whole row.
I am trying to get the starting point for this new sheet going but I am not able to formulate the required code. I will explain what I want to do
In the attached sheet, I have three tabs
Over 20K
Under 20K
PartNumList
This is what I want to do...In PartNumList tab if Annual Rev (Col O) is less than 20K then value in Col (A) i.e Part Number should be copied and pasted in Under20K tab... it has to pasted in either B18, B24,B30,B36, if B18 is populated then B24 if that too is populated then B36 and so on..
I want to do that same for Over 20K but if I have a starting point for Under 20K I can work on it offline too..
I need to pull some data from one sheet into another. Here is an example of what I am trying to do if anybody know hows to do this? I need a formula that will look in sheet 1 look at the idnumber, match it with the idnumber in sheet 2 ande fill in the serial.
sheet1
idnumber serial number
12345 66181
12346 66182
12347 66183
sheet2
idnumber serial number
12345 insert serial number here
12346
12347
I want to create a macro that will automatically pull data from the web by simply pressing a macro button. I assume that this would be an easy task if the data is from a website with a specific URL such as [URL]
However, there are times when someone must enter data into the web to pull a specific report that does not have a unique URL. Is there anyway to create data fields in excel that would allow an individual to type in specific information (name, report period, etc.), and then press a macro button that would go out to the web, enter the information automatically, and export it as an excel file. I realize that this is probably substatially more difficult than my first question.