In sheet one, down column A, I have a list of numbers 1-100. What I want to do is have those numbers be removed from sheet 1 when entered in column A in sheet two. For example, in sheet 2, A1, number 53 is entered. I would like number 53 removed on sheet 1.
View 9 RepliesI have a macro that asks the user what pages they would like to print on a particular sheet. In the example below, a button will be assigned to pages 1-5 on a sheet and thus the user has the option of printing from page 1 up to page 5. Any page number outside this range will result in an error message and the user will be asked to re-enter the end page number. This is the code I pulled from this forum and partially modified:
View 4 Replies View RelatedI have a long list written twice in 2 worksheets worksheet A has a list with some of the numbers repeated. worksheet B has the same list (none repeated) and another list with new numbers beside it. What I need is to take the new numbers from worksheet B and put them next to their correlating number in worksheet A. With many of the numbers being repeated I need something to identify and repeat the new #. I'd copy and paste and drag etc. but there's about 21,000 numbers to go through.
View 3 Replies View RelatedC Attached file.
I want the items that appear in B9:B5000, automaticaly in Combobox_Category. How can I do this, so that every item stays unique.
example B9 = fat cow and B10= fat cow, fat cow will only appear 1 time in the combobox lista and not twice, how can I organise the combobox list Alphab?
I was wondering how to input data on one sheet, then have specifics from that first sheet get sorted and put on the second and third sheet according to it's value. I have attached an example with my comments in red.
View 3 Replies View RelatedI have an issue when I use the sumproduct formula, it only sum the quantity and price orderly which is not matched
I have attache the file for example : Sale monthly.xlsx
Using Excel 2003
We have a spreadsheet set up showing a list of sales invoices, each invoice has the customer name and a customer reference assigned to it i.e B1, B2 etc
We want to set up another sheet that takes the sales invoice information but only picks up that which relates to a specific customer, i.e want a list of the invoices relating to B1
This will be done with a view to creating statements for each individual customer reference number which will just take info from these sheets B1,B2 etc
I cannot figure out why this setting has changed. When I right click my tab worksheet in Excel to copy or rename, it does not allow me. I will need your advice to re-activate that options so it is visible and not gray. I tried in the VBA workbook properties settings with no luck and also Tools, Options.
View 6 Replies View RelatedI have a workbook with one sheet (called "Pipeline") and another sheet called "Completed". The Pipeline sheet is used to keep track of all of the tasks that the team are working on, and then when the task is completed, it needs to be moved to the "Completed tab".
I currently have managed to write some code that looks for items marked "completed" in row G of the "Pipeline" sheet, then copies them over to the "Completed" tab, and then deletes the row from the "Pipeline" sheet.
There is one macro to copy the completed cells (called "Transfer"), and then another to delete the cells (called "Delete"). I then just have macro called "Clean" that just calls the transfer macro, and then calls the delete macro.
Often, there are 6 tasks marked complete, and only some of them will be copied over, but all of them will be deleted (a disaster).
Transfer Macro : [Code]....
Delete Macro : [Code]....
Overall Macro : [Code]....
Can you display items from 1 column on a worksheet in 2 columns on a Listbox?
This is to avoid having a Listbox that is too long for the form, (I know I can use a scrollbar and I am but I would still like it shorter if possible).
I am a bit stuck with an excel spreadsheet I do for work and would like to ask your advise, I will try to explain as best as I can.
I am running a small store that feeds 13 buildings.
On the spreadsheet there are 13 sheets, one for each building.
The items will go out monthly.
What I need to do is record the total items from each sheet in a column called All Totals on the first sheet, this will add up the totals for all the buildings.
If I put an attachment that may help out a bit.
If you can look at P3 it is all the P3s to add together, next down will be total of P4,P5,P6.
im looking to have a drop down list on sheet2 that displays unique items in column A on sheet 1.
then when an item is selected from the drop down list, i want to be able to display all unique values that correspond to that option. i have attached an example worksheet
data on sheet1 is ever changing
(File is attached here)
I am trying to work on Sheet 2(Details per person). I want to be able to display all items in a row that matches the 2 criteria (Skype ID and Date) and the items are based from Master Raw file which is in another sheet. I would like to just use index and match.
I have attached a sample workbook, (Pricing Sheet - Major) within this work book there is a worksheet entitled Price Book which has commonly-used materials, unit prices and labour rates. What he wishes to do is use the contents of the Price Book worksheet within a worksheet called Pricing Sheet to enable him to prepare quotations.
I had the idea to first sort the items in the Price Book worksheet and then produce a dropdown list of these items for use within the Pricing Sheet worksheet and used the VLOOKUP function to obtain the values for unit prices and labour within the Price Book and use them in the Pricing Sheet worksheet.
The sheet shown, Complaints, lists on each row information on a single complaint. This information extends from Col A to Col Y (though not that way here, the data actually begins in row 3). I want to highlight a Customer cell, say H227 for Customer A, then activate a macro that (1) recognizes the active customer (here A), searches the list, identifies each row where A is the customer and then writes each row into sheet "Event" beginning in row 4. I should then see the same A to Y cells of information, beginning in row 4, filled in sheet Event for the six customer A complaints. Note, the actual data in sheet Complaints involves 300 rows of data, and over 100 customers can be on the list, some up to 15 times. I know that I could do this by filtering the data, copying and pasting, but a simple macro would be quicker for the group of individuals involved. Note, I then use this information to populate a chart.
Excel 2010ABCDEFGHIJKLMNOPQRSTUVWXY226#INITIATION MonthINITIATION DATECRM Transaction IDSales OrderSalesSHIP PlantCUSTOMERCITYST/PROVPRODUCTProductLbs InvolvedPROBLEM Complaint
[Code]....
I have a spreadsheet that lists a set of actions and the days they need to be completed.
The due dates are listed in column c and in column h I have a the user choosing "completed" from a drop down data validation.
I have a dashboard on a separate worksheet, showing if the item is complete and the due date.
Ideally I would like to display the dates on the dashboard in red if they are past due, and the box in column h was not marked complete.
I have been going through my books and trying to solve this, but I run into a different issues.
Even if for now If I can get the main worksheet to display the color conditional formatting that would work
I need to count the total number of times 4 different values appear in a column. This formula works for one value:
=COUNTIFS(Source!$C:$C,$B5,Source!$J:$J,$L$3,Source!$L:$L,$H$1)
Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.
I am wanting to create a list where I can select multiple items within that list and then print only those selected items. I have created something similar thru data validation, but I can't get it to print.
In addition, I would like to be able to subdivide the list into multiple categories, then select items from these multiple categories and print them.
If i select 3 items from a list 20 items to be show on the cube this is fine and works however if i then add another item to the list i now have 21 items and for some reason it also adds itself to my 3 selected items to become 4 items. Is there anyway I can get it to be added to the 20 items and not to the 3 items..
View 4 Replies View RelatedI know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
I have an Excel spread sheet as shown below. Sometimes, there are two rows (could be more )with the same items-for example, such as Inventories, but different values. How can I sum those rows with the same items and put it in a different column, and in the same time also copy over the single item to the same column? ....
View 9 Replies View Related 1) How do I get the three (3) items columns to be right underneath the ''Recovery Turnaround Days of Files Per Cheque'' heading
2) How do I get each of items #1 - 3 column, to have each their own color (that is not the same color as the '' Average Recovery Days '' column color) ie;
Item #1, 30 = I would like it to be say, Red
Item #2, 35 = I would like it to be say, Pink
Item #3, 30 = I would like it to be say, Yellow
3) How do I get the column under the ''Average Recovery Days'' heading to have it's own color scheme as well, such as as say the color ''Purple'' (or any other color that is different from all the other 3 columns).
how you go about putting the columns so they align right underneath the particular heading, that would be awesome. Also, how to have a color scheme for the ''Average Recovery Days'' to have it's own color (For the column under this heading to have it's own color, what ever color that is), that does not match any of the column colors in items #1 - 3 (under the heading ''Recovery Turnaround Days of Files Per Cheque''),?
See attached sample file. Any way to get column graphs next to each item (horizontally), each column being the revenue for that item.
Attached File : Sample.xlsx
I need to add items to an array and used code out of the web to create some kind of an array list.
View 12 Replies View RelatedThe below code shows the way I add items to 10 different comboboxes. However, the items to be added are actual the same.
Is there a way I can simiply it? I am worry that by the end of day, i might have up to 50-60 comboboxes and the code will be a relativity long and not efficient.
Taking into consideration the the comboboxes will be labelled as Jieyi_task_ComboBox1...to 10
jane_task_ComboBox1...to 10
Kenny_task_ComboBox1.. to 10
getting correct formula and normally there are people much smarter then I in the
discussion group. What I have is a workbook with 4 worksheets and on on of the worksheets I have a layout like this. (sheet 1)
A B C
1 Name Origin Driver #
2 Mrs Soandso 125 anywhere
3 Mr. Someone 327 anywhere
Drver numbers are blank
On another worksheet(sheet 3) I have a list of subscriptions (I.e drivers
numbers already filled in) I have this set up as a list.
A B C
1 Name Origin Driver #
2 Mrs Soandso 125 anywhere 37
3 Mr. Someone 327 anywhere 39
What I am looking for is a way to pull the drivers numbers from sheet3 into sheet1. so basically in sheet 1 if a1 and b1 match the name + origin in sheet 3 it will pull the driver number from that row into c1 sheet1 . If nothing matches then it should leave it blank. The data in sheet 1 changes everyday for me as I get a manifest on who to deliver wheels on wheels to, my problem is while I know all the correct drivers go to what people, I can never have a day off as someone filling in for me has to go through 400 addresses and look each one up and put coresponding drivers number into it.
I have a monthly calendar on each sheet for employees, attached is a copy of one of the sheets. I can have up to 2 combinations of text in a cell, i.e., 'V' or 'T' in this example, followed by a number, that can range from 1-12, i.e., V12T4. What I am trying to do is add up the total 'T' and 'V' time for the month for each employee. Hopefully between what I have written and my attached sheet, will explain what I am trying to do. I know how to write the =Sum(If for using only 1 item in a cell, but not 2.
View 14 Replies View RelatedI am working on a set of data and need to group items in the same style together.
Example: ...
I am setting an array with
Code:
Set my_array = Sheets(1).ListObjects(1).DataBodyRange
Column number 2 contains numbers, but I want to 'reset' all the numbers in the second column. How can I do that? I know I can do a loop through all the elements, but I thought it would be smarter if I could set the entire column as zero in the array?
I have a code which works perfectly and adds the dates from Column A to UserForm1 ComboBox2.
In column B, I have textdata - UserForm1 ComboBox5.
I'd like to transform the code to add only the dates which correspond (cascading) to 1.1 textdata in ComboBox5 (Column B) and 1.2 dates in ComboBox2 (Column A).
Code: ...