This is easy enough, but I only want to have items listed if every field on the row is unique which I can do, but is it possible to only display these unique lines and have any lines that contain a duplicate not be shown as a blank or anything else?
View 1 RepliesI have a company with upto 5 products, and I have created a dropdown box where you select your product.
When I select product 1 i would need info on product 1 , when i select product 2 I need info on product 1 and 2 and continue. so for product 5 -i need info on product 1-5.
I tried to add all info on excel and tried to delete it one by one by deleting rows in excel for products but it does not work.
I have a workbook that needs to display the relevent weeks in month, the dates would be the start and end dates of each wekk (monday to Friday) but also need to take into consideration, partial weeks i.e. only a wednesday, thursday and friday at the start of the month given where the first available date falss on.
see the attached sheet for reference, I am looking to do this with as few formulas.
It also needs to show a list of monday only dates for the selected month.
An integer in stored in Sheet1!A1. I want a formula in Sheet1!A2 to display data of sheetx!A3 where x is the number chosen in Sheet1!A1.
e.g. Sheet1!A1 store 3. Then Sheet1!A2 will display Sheet3!A3.
e.g. Sheet1!A1 store 7. Then Sheet1!A2 will display Sheet7!A3.
I have a treeview box w/ multi-select enable. My question is how do I display in a message box of all the item I've selected.
View 1 Replies View RelatedI would like to automate a sheet to report statistics of projects based on a single cell that is the year that a user manually inserts. I've got a separate worksheet with all dates manually set up for next 10 years and would like it to be read by formulae in a reporting worksheet based on the year that user enters.
So if I specify year 2014/15 (financial year), I would like the cell C1 under "Reporting" sheet to display 1/07/14 predetermined in "Dates" B3, if I specify 2015/16, the cell C1 will read 1/07/15 from "Dates" B6 and so on.
I could do it with "IF" function but there will be too many nested IF functions in a lot of cells (hundreds if not thousands). This may make my file size very large.
Attached is a simplified example of what I mean.
I have an excel sheet which looks similar to below and with some more columns.
HTML Code:Â
AreaBranchEmpolyeesProductsShelfs
Area1Area1 Branch110Product 110
Area1Area1 Branch25Product 16
Area1Area1 Branch38Product 28
[Code] ......
I want to have data in other sheet With respect to Product details. Where It gives me the count of Product 1 and so on.
I am able to get the count using countif function and it displays like below.
HTML Code:Â
Product Count
Product 16
Product 28
Product 34
Product 49
Product 510
Product 68
Product 78
Product 89
Product 97
Product 108
What I want is when I select "Product 1" It should display some selected columns data from sheet where complete data is available.
HTML Code:Â
Branch Empolyees Other
Area1 Branch1 10 abc
Area1 Branch2 5 xyz
Area1 Branch5 7 pqr
Area2 Branch5 5 wxy
Which function we can use. I tried Vlookup but was not successfull.
I have problems inserting new rows in selected files. The files with the problems have a macro. I don't know if the problem can lie within the macro. There is no error message, it just won't insert the new row(s).
View 4 Replies View RelatedThe following code inserts a row below the selected row, and copies the formula of the row above into it.
Dim Rw As Integer
Rw = ActiveCell.Row
Selection.Insert Shift:=xlDown
Rows("" & Rw - 1 & ":" & Rw - 1 & "").Copy
Rows("" & Rw & ":" & Rw & "").Paste
However, I need to alter this to work for inserting more than one row at a time. ie. the user selects 'x' number of rows and 'x' rows are inserted below (in the same way 'Insert Row' works in Excel) and the row above the selection is copied down.
Software: Excel 2010, Windows 7
What is the VBA code for inserting text in all column B-cells of multiple selected rows?
I am creating a worksheet with a table containing various data related to orders of various materials (this is more or less data gathering from an older, paper-based 'system'). This table spans, columnwise, from A to D and expands downwards as more orders are added. The information in each column is: A=order number, B=type of material and C=material specs. and D=additional comments.
I've set it up so that the only thing I really have to do is to insert the type of material in the cells of column B, and the rest will sort itself out. Instead of having to insert a new row for each new entry and manually typing in the name of the material (these entries are often done in the midst of already existing data), I created several similar, macroed buttons for the different types of materials we use. These macros work by selecting the row of the currently active cell, inserting a new row and then add the name of the material in the column B-cell of this new row. What I am having trouble doing though, is to get the text-entry to work for a selection of multiple cells.
As an example, lets say that I would like to add 5 orders of "Grade A Steel" in the middle of the table - in the row above row 8. With the macro I currently have I can select cell B5, click the macro, and a new row will be inserted with "Grade A Steel" in column B of this new row. This action could be performed 5 times over, but would be easier if I could just mark a range of 5 cells, say B8:B12, click the macro and get the text/data inserted the column B-cells of all 5 of the new rows. So far I've been able to create a macro that inserts multiple new rows, but I've only been successful in filling the column B-cell in the first row leaving the 4 below empty.
In my userform I have a list of check box's that can be selected. Currently if more than one is selected, they will appear in the spread sheet in the same line one after another with a space between them. How do I make it so they either appear with a comma appearing after each, so the next value appears on the next line below or most perferably the cell turning to a drop down list with the values?
Current code
VB:
If CheckBox1.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox1.Caption
If CheckBox2.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox2.Caption
If CheckBox3.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox3.Caption
If CheckBox4.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox4.Caption
If CheckBox5.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox5.Caption
If CheckBox6.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox6.Caption
Spreadsheet current cell appearence if all 6 are selected ' Chinnook EH101 Lynx Puma Sea King Fixed Wing'
Required appearence:
Chinnook
EH101
Lynx
Puma
Sea King
Fixed Wing
I want to to copy selected columns of sales data into rows organized by salesperson. I have just started out with VBA and find that I cannot do it myself.
My original data are in the form of the following:
invoice_no
product
sales
qty
total
[Code] .....
I want to display the data in another sheet in the following format:
sales_a
sales_b
sales_c
sales_d
[Code] ...........
Need help with this file, i did most of the code but cant figure out some bugs i am having.
CTRL-SHIFT-F runs the macro,
the problem i am running into is ,
1), i have the macro looking up the legend in row 3 in the factory install tab, and having it find this legend in the T1 Verbiage file. Once found i want all of the data in the column of that legend pasted in row 12.
I currectly have the macro pasting the legend verbiage according to the legend in row 3 correctly but it leaves out some rows. Can someone look at the code and find out why its leaving out some rows of verbiage?
Next i have certain requirements of where Capital E's should go, where routine maintanence should go ect. I have that at the end of the code and is correct.
The problem here is with this part. ( starts at row 12, counds all of n which is the verbiage rows, then adds 8. So in other words this formula needs to be 8 rows after the verbiage ( which is dynamic depending on the legend ) ....
I have some values in the column and i want those values to be displayed in rows now. Example:
In the Excel sheet the data is in the form,
Column1 Column2 Column 3
TASK_TYPE NE_TYPE ADC_PARAM_ORL
I want in the format,
Column1
TASK_TYPE
NE_TYPE
ADC_PARAM_ORL
I have two groups of lottery results. The first group has the last year results of 200 rows, and the second group displays this year results of 100 rows. I want to start comparing the first row of the second group (green group) with all the rows of the first group( blue group)
View 12 Replies View Relatedis to display a set of data based on filtered information.
My Data base:
Company Department Name
A X John
A Y Joe
A X Jane
B Y Bob
C Z Kate
A X Kerri
Based on user selection of Company and Department, I want to be able to display the relavent names.
If user chose Company A, and Department X, I want to be able to display
John
Jane
Kerri
I've used the ROWS, Index, Small combination that works perfectly (Please see sample below). However, since the this software doesn't support the ROWS function, and doesn't support Macros.
I have n rows on a tab and I have counted them using Offset and Counta and put that count on B1.
I now need to make a little macro that print from row n to row number in B1. I've seen macros using things like "For i" "Next i", etc... but I do not know how to set the macro to start at a cell with a number n and repeat till the number in B1.
I have a sheet that pulls hundreds of names with a query. These names are ordered by department and each department prints (in gridded table format) on a separate sheet of paper. I would like to set it up to add additional empty rows to the bottom of each table if there is any space available on the page. I'm clueless how to go about it, though...
View 4 Replies View RelatedI am trying to create a dashboard of reports. I want to be able to click a button and the user will be taken to the tab with the appropriate report. I am not a programmer so I am trying to use Excel formulas to the fullest.
I am trying to create a 30/60/90 report. I am using a named range for all data. Is there a way to create a dynamic named range and specify for rows where the age column is between 0 and 30, 31 and 90, 90 and 120, and so on?
Finding a way to tell excel to pull Comments and Tags from files. I pulled the following code from another source:
VB:
'Force the explicit delcaration of variables
Option Explicit
Sub ListFiles()
'Declare the variables
Dim objFSO As Scripting.FileSystemObject
Dim objTopFolder As Scripting.Folder
[Code] ....
I have been using it to get the Name, Size, Type, and relevant Dates associated with files in a folder. However, this doesn't seem to work with finding Comments, Tags, etc.
I have found functions that are used to pull Comments and Tags from files but haven't been able to incorporate them in to the code I already have.
I have a workbook, which I use for different departments that displays a number of different pie charts and I have found that a particular pie chart omits a specific label repeatedly. The pie chart displays the wedge within the chart itself, but does not display the label. At the moment I have data labels with percentages. All other labels display, of which there are 7. I found a solution that fixes the problem each time it arises and that is to select Chart Tools/Format/Series 1 data labels and then Format Selection. When I then select any data label, I click on "Clone Current Label" and the missing label appears with the correct percentage amount. I use the workbook for multiple uses and rather than have it present as a template (*.xltm), it is a macro-enabled spreadsheet (*.xlsm). Could this be related to the problem?
View 2 Replies View RelatedI have an excel file with 2 different columns filled with letter combinations which I need to update every now and then. I would like a code that will automatically fill in the codes not available in each list at the next columns.
See attached Sample Data to look how it will look like : Sample Data.xlsx‎
My Excel 2007 worksheet contains a cell where a percentage is manually input. A freight cost is calculated based on the input percentage. Typically, the percentage is 3-7% but once in a while freight is excluded and the percentage is zero.
My issue is that when a 0 is input the cell appears blank and I would like it to display 0.00%. The remainder of the worksheet needs to have the zero display turned off.
In the column marked SA I have some numbers. These are Sub-Accounts. I am looking to run off a report that prints off all rows with the respective SA number. I have only included a "4" and a "5" in my example but there is going to be more than one row "assigned" to Sub Account 4, Sub Account 5 etc. Ultimately it provides a break down of the accounts.
View 7 Replies View Relatedwriting a VBA to convert a set of data in sheet 1 to one in sheet 2(I am enclosing that as a Excel document"Test -Original").I have described what needs to be done ( step by step ) below.
Develop a macro
1. I have a report from SAP BW, the original format of which is in sheet 1.I need to develop a macro using VBA and need the report with the format in sheet 2.
2. I need a “Results “row after every Bill to Party in column A as below(screen shot 1.doc):
The number of customers is dynamic i.e.it keeps changing every month
4. Nothing needs to be done to column.SAP BW will not overwrite the format and the data in column G.Hence leave it as it is.
5. Calculate the number of Sales document numbers for each customer and put the value of 1 for every value. If it is blank it should not be counted and put the value of 0 for those rows. (Shown below) Display the sum of the number of sales document numbers in the results row for column H
6. In the column I, put the value of 1 if the difference column (column G) is 0 and put the value of 0 if the value in the difference column (column G) is any value apart from 0.now sum the value in the results row for each customer and display the summation value in the results row under column I
7. Compute the percentage which is the values in (column I/Column J)*100 .This should be done only for the results row
I am using Windows 7 and have a piece of code that brings up a warning message after 20 minutes to warn the user they have been in a workbook for too long.
If they have the Excel application window minimized, is there a way to get Excel to take the application out of minimized mode into max screen, before revealing the message.
Currently when the warning message activates, the excel icon just flashes in the start bar. Some users may not see this.
Here is the code I have so far:
[Code] ....
I have already tried simply adding a piece of code with maximize screen, however this doesn't seem to work.
I'm using Windows XP with MS Excel 2003. I have a pivot table representing a survey. Let's say I've built the survey outside of excel and I've imported the response data into Excel. One of the questions in the survey is "ratings" and the possible valid responses for it is: "Excellent", "Good", or "Poor". In my data set in excel let's say I have 10 responses or rows and all the responses for the question on ratings are either "Excellent" or "Good". (There are no rows with a "Poor" value in the ratings column).
For example, let's say out of the 10 responses, 6 are "Excellent" and 4 are "Good". As such my Pivot chart shows two bars: one for the number of respones with "Excellent" (10) and another bar for the number of responses with "Good" (4). My delima is how to show a third bar showing "Poor" with a zero as the number of responses.
So I have a pivot table of about 2000 rows with data on employees.
In the table, there is a field associating each employee with a location (midwest, southeast,where ever) and I have a report filter on it that lets me view only people from a given region.
It was working fine, but I'm doing a lot of VBA macro related to the pivot, and I think I somehow changed a setting somewhere in the process and don't know how to reverse it.
When I apply the filter now, it only shows data for people in the region i choose, except it still shows all the other lines that should be hidden, just with no values. Here is a crude visual
Name region sales
Jim_____MW__10
Henry___MW__34
Jerry____SW__20
Aaron___SW__44
Scot____MW__22
AFTER FILTER for "MW"
Jim_____MW__10
Henry___MW__34
Jerry________0
Aaron_______0
Scot____MW__22
(jerry and aaron should not be visible at all)
I have this form (Invoice) on a spreadsheet and I want the footer (Taxes and Total) to appear only at the bottom of the page or of the last page, if many. I call this a "Report Footer" because no matter how many pages the invoice might have, this section will be pushed to the bottom of the last page.
I need to ask because we all know that we cannot program formulas in the actual Excel spreadsheet footer!!Facture-Logicim.xlsx
I have created a excel document. Would like to create report using the data.
create reports using excel data