I have this current IF function in this worksheet I am working in:
F7 =IF(E7=0, " ", E7*0.17)
Then what I need to do is if I type the letter "N" into G7, then the value which is labeled as "E7*0.17" in the first function needs to appear as a blank, in otherwords as: " "
Is there any way to do this? I have tried nesting an OR function within the IF function, but the result always appears as #NAME? with an error sign.
Here is the function I have tried:
=IF(OR(G7=N,E7=0), " ", E7*0.17)
Then I also have a function for H7 where H7=E7+F7 which needs to stay true at all times as well. When I put in the nested function above, the value in H7 changed to #NAME? as well.
If we look at Produced date and TO Date in Sheet1. If the TO date is less than the Produced date then the TO date is the date to use. i.e. for May it will Say Produced:, 22 Jun 2009 "From:","2009-05-01","To:","2009-05-31". So use "From:","2009-05-01","To:","2009-05-31"47 (because TO < Produced use TO )
However in June it might say Produced:, 22 Jun 2009 "From:","2009-06-01","To:","2009-06-30". So use "From:","2009-06-01","To:","2009-06-21" (because TO > Produced use Produced -1 ).I've already have some formulas in place. Look at the attached file, but I don't know how to set up the IF formula or any other.
I have a really long function which counts the number of unique values in a selected range, and ignores blank cells. The function is like this (only works as an array function):
I would like to call upon a function until a certain criterion is fullfilled. Then, I would like to have the result returned to me and exit (all) open functions. For value1 = 1 and value2 = 10 I expect value1*value2 = 100. Instead, the routine returnz zero. What is the logical flaw in the code below.
Function testfunction(value1, value2) If value1 = value2 Then 'Calculating the difference testfunction = value1 * value2 Exit Function ElseIf value1 < value2 Then value1 = value1 + 1 Call testfunction(matrix1, matrix2) End If End Function
I am aware of the following topic in the VBA Help file:
"Using Microsoft Excel Worksheet Functions in Visual Basic You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.
Note Some worksheet functions aren’t useful in Visual Basic. For example, the Concatenate function isn’t needed because in Visual Basic you can use the & operator to join multiple text values."
And I'm aware of how to call Excel funcitons from within VBA; e.g., answer = Application.WorksheetFunction.Min(myRange)
However, not only are some Excel functions not useful; the fact is they cannot be used because VBA has a native function that does exactly the same thing and you have to use that native VBA function to achieve your goal. It is these overlapping functions that I am especially interested in. I want to know what I should use directly in VBA and what I need to go to Excel for.
I'm compiling data to be transfered into a report program but the programe can't deal with any Excel Formulas so the data has to appear 'as is' so to speak. I have 2 columns of data (roughly 1200 rows) and I need to merge the two together but i can't have the formula in the merged cell......
I am looking for some tips to merge the 2 rows into 1 row with all data transferred. I have tried the following:-
=IF(MOD(ROW()/2,1)=0,INDEX($B$1:$B$100,ROW()/2),INDEX($A$1:$A$100,INT(ROW()/2)+1)) However, it returns "0" when cell is empty. Your help is greatly appreciated. Attached a file for better understanding.
I have 3 Worksheets in Excel I would like to merge into a new worksheet. Worksheet A, B, C. I have 133 rows of information on each sheet.
I would like to merge them in a particular order. I would like to take Row1 of Worksheet A, then merge Row1 of Worksheet B, and then merge Row 1 of Worksheet C.
So on the new Worksheet I have 3 rows of combined 3 worksheets. The would like to repeat this for all rows 2, 3, 4 etc. till will get to row 133 or end.
I collect data from a number of tools for our server and switch info. I don't have a problem merging that data. My problem is trying to get that data into a working format.
-- I have a server that has dual network connections.
-- This data resides in different rows, and each has slightly different data that I need to preserve.
-- I want to merge these two rows into one pre-defined output layout on a separate sheet without loosing any of the data.
Some other things of note. Not all of the servers have duplicate entries, but still want that data on the output sheet as well.
I have attached an example of what I have to work with and what the output sheet that I'm trying to get to looks like.
I have two worksheets in the same workbook that need to be merged into a third worksheet. I need to take all the data in worksheet "On Hand" and bring in from the worksheet "Transactions" the data in columns E & F (into the new worksheet "Excess Inventory"). There will be item numbers (Column B in both sheets) that will only be in the "On Hand" sheet. I have entered data in the sheet "Excess Inventory" to show what I am trying to do.
I have an excel file with the same columns of data for different years (each year's data is in a different sheet). Each of the columns corresponds to a different survey question asked to the same farmers year after year. These farmers have been identified by ID number in the first column and I would like to consolidate the information from all the years into a single sheet.
The problem is that there are many farmers who dropped out of the survey or were added to the survey over the years. So, for example, in year one you may have data for the following ID numbers: 1, 2, 3, 5, 7, 8, 9 And by year 4 you may have data for the following ID number: 2, 3, 4, 5, 9.
merging two Excel files. 2009 Item List.xls file has two columns Part number and Description. Price.xls file has two colums: part number and price. I need to merge them into a file that whould have three colums: part number, description, price. There 6712 rows in 2009 Item List.xls and 4743 rows in Price.xls file. So I need them matched based on the part number, and have the cells blank for the part numbers for which the price was not assigned.
I'd like to combine two VBA subs existing on a single sheet. Since they can't duplicate I need some sort of a switch statement...?
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim VRange As Range, cell As Range Dim Msg As String Dim ValidateCode As Variant Set VRange = Range("E4:E100") For Each cell In Target
I have given an example of below of what i am trying to achieve. I am seeking to match up the region name with the project id and to have the outocme put into expected output. i have done two examples below. The problem that i am facing is that "Asia" as you will see has more than one project info. (the list is quite long)
ProjectInfoProjectIdRegionRegionNameExpected Output: CW28915NL - Netherlands CW28915HK - Hong KongNL - Netherlands ; HK - Hong Kong CW30695Asia CW30695America CW30695Middle East / Africa CW30695EuropeAsia ; America ; Middle East / Africa ; Europe CW32739Middle East / Africa CW32739Europe CW32739Asia CW32739America
I've created a time sheet that we fill out daily. I currently have two macro buttons on the sheet. One "submits" the sheet by creating a .pdf, saving a copy and then emailing it to the boss. The other closes the time sheet without saving so that it doesn't carry over today's data to tomorrow. My question is, can I assign one macro to do both?
1 spread sheet has 1500 filenames store in Column A and the another spreadsheet has all filenames(Near 3500) in column A and their properties in the next three columns.
Can anyone teach me how to automatically match the same filename and merge together the data and put to sheet 3?
so in sheet 3 I can have the following Column A = Filename | Column B = Property 1 | Column C = Property 2 | Column D= Property 3
I have 2 tables in excel that i need to merge into one, Table 1 has 10 colmns and Table 2 has 7. Both tables have as column 1 a heading of Material, this is the column i need to compare as table 1 does not contain a material description field, but table 2 does, the bold headings are the key ones, but other column information is required. I have attached a sample ss to show what i am trying to do.
Table 1 Col Headings Material MTyp MS R Clt L/O Created on Created by
I have two seperate graphs in two different workbooks. Both graph's are identical in the setup, they have the same X axis and Y axis value. They are identical in every way except for the varying in percentages. This graph was originally setup in that it would calculate the results for a period of a week at a time. Is there a way to merge the two graphs thereby showing the results for a two week period?
There is no unique common key to relate records in two work sheets of same workbook. Therefore, the first step in generating this report is to create a common key that can be used to match records in worksheet 1 to worksheet 2 records. Common key is generated by concatenating few of the columns common in both the data sets (say something like brand + Site + End Date + Last name + First name) The matching run should match the pseudo keys in the two datasets, and merge the records into one file using the following criteria Event.key1 = ECB.key2 and Event.act_psrv_amt = ECB.folio_amt
Used Vlook up function but it takes really long time to return matching record (note that I want to return whole of the matching record) as we are dealing with huge data. attached is the sample data