I have a User Form in which users can enter R,G & B values in 3 seperate Text Boxes in order to change the colour scheme of a worksheet to suit their personal taste.
I have tried combining the 3 values into a string to give, for example, RGB(255, 182, 45)
But, of course, because it is a string variable it is enclosed in quotaion marks and so, when add to a .Interior.Color function, the qutation marks are added too and the macro fails.
I have tried altering with Left, Right, Mid and Trim but cannot get rid of the quotation marks. I also tried converting to Hex but got totally confused there!
code to convert the values in the 3 list boxes (called LBRed, LBGrn and LBBlu) into an RGB value that can be appended to .Interior.Color
I want to use a value in one cell as a row designation, and a value in another cell as a column designation. Ultimately, the values will be text which will refer to row and column headers. What formula would allow me to do this? example:
A1 contains B B1 contains 2 B2 contains "tribbles"
Is it possible to have a formula where it will retrieve a specific value on one sheet based on matching values? Unfortunately, hlookup wont work as there are several values in the table where the 'lookup_value' is the same.
Example:
On sheet 1 (titled BSRC), I have a table which has a list of values in column 1. On column two I have the 'lookup value' which needs to be matched and all values underneath it to be retrieved from sheet 2 (titled CSRD). The 'lookup_value' range is on row 9 of sheet 2.
I have attached a sample spreadsheet for clarification.
I'm having trouble combining some values in cells. For example in cell A1 i have the value 372-25. In cell B1 i want to make A1 part of another value (it is an APN if you must know). So that cell would be 011-372-25-11. I would be adding 011 as a prefix to each cell and 11 as an ending. I'm sure there must be an easy way of doing this.
I have tried =011-A1-11 ="011-"A1"-11" ="011-A1-11"
I have the follwoing example. Basically, there is an "X" for each posession of Andrew (cells: B1, C2, D3, E4). What I'm trying to do, in another sheet, is centralize the below information in only one row for "Andrew", with an "X" under each of his posessions (all the 4 "X"-es are now in cells B1, C1, D1, E1).
The company I work for has kept a list of the types of objects sold with their houses and how many were bought for each customer, via an Excel document. Here's a snapshot - You can see the name of each type of object on the left, with each column afterward representing an individual customer (Each vertical row is one customer) and how many of each item they bought.
[URL]
We're hoping to make a bar graph of how many of each type of item has been sold. I selected all the titles and columns of numbers and ended up with this, which is almost what we're looking for:
[URL]
The problem here is that each individual customer has their own line on the graph. The Recessed Can Light one I highlighted is a good example of a lot of people who bought varying amounts of that one item. What I'm looking to do is combine the individual sales for each item and make a single bar for each representing the total sales overall, so we can gauge what the best and worst-selling items are. Is there any way to do this?
I need to have two area charts combined, but they have different x and y values. Is there another way to do this.When I try to combine them in one graph, I can only use one x-axis value.
I receive a monthly report containing a list of people, and how much is being paid for certain services. The company that sends me this list is preparing to adjust their rates and it will be retroactive back a few months.
The way they plan on doing it is by means of taking a credit back several months, then "re-paying" the correct rate. The main data will include the month for which the payment (or credit) is being made, the person's unique identifier, as well as the amount.
Here's a sample of what it would look like:
Name, ID, Month, Amount John Doe, 123, 04012008, 25.00 John Doe, 123, 03012008, -20.00 John Doe, 123, 03012008, 25.00 John Doe, 123, 02012008, -20.00 John Doe, 123, 02012008, 25.00
So basically the above shows they paid $25.00 (correct rate) for April 08, then they took back $20.00 the prior two months (the old rate) and paid the correct rate right afterwards.
In what I need to do, this is going to be a lot of work. Is there a way to programatically merge the amounts given the member's unique ID as the "key field" as well as the same month? So it might look like the following:
Name, ID, Month, Amount John Doe, 123, 04012008, 25.00 John Doe, 123, 03012008, 5.00 John Doe, 123, 02012008, 5.00
Just giving the sum of the amounts for a the given people in the same month?
I'm pretty good with VBA but this one is stumping me.
I have a data set that I'm trying to sort in order to be efficient for some of my team members.
Below is an example of the raw data and how I've currently got it sorted. I think my problem may be that I am trying to join a number with a string but I'm not positive.
Col A
123-45678-A-1 123-45678-A-10 123-45678-B-2
I need to eliminate the letter from the data, add a leading zero to all single digit numbers and sort ascendingly.
My first step is to split the data into columns using the "-" as a delimiter. I end up with 2 columns as shown below.
Col A Col B 123-45678- 1 123-45678- 10 123-45678- 2
Next I add a leading zero to Col B. The assumption is there will never be more than 99 numbers, so I use the following code:
A transportnumber can have multiple instances of itself, such as 106200. This transport number can also have multiple instances where the postcode is the same. These are the rows i want to combine
So shortly: Transportnumber double/triple...+ Postcode double/triple...= combine these rows.
if only transport number is double, or only postcode, then dont combine. I want it to combine 2 columns of values, one will b the kgs, other one a price.
So example of end result for 106200 would be 106200 8500 value+value (these 2 rows matched so it combined the 2 values i want it to) 106200 8600 value ( this was unique so it stays unique)
I have the below macro which is failing to insert text into A1 of the Header Sheet, followed by the values in the designated cells that are from Sheet1. I would also like to have the values in cells J2 and K2 enclosed in single quotes.
Sub Header() Worksheets("Header").Activate ActiveSheet.Cells(1, 1).Select ActiveCell.Value = "create or replace" & " '" & Sheet1.Range("J2").Cell.Value & "' " & " '" Sheet1.Range("K2").Cell.Value & "' " End Sub
how do I get it into the nicely formatted version most of you are using? The Mr Excel HTML add-in?
it should compare and see if product, model for same id is the same but brand is different and the quantity of one or more of those brands=0 then the result (In column F)should combine the brands. check the attached image for more details.
i have a workbook with two sheets. lets just call them sheet 1 and sheet 2. on sheet 1 i have data for employees and their current wages and other info. on sheet 2 the data is for compensation scale on three separate columns.......
in order for me to automatically get the data from (sheet 2 B3) the formula for sheet 1 E1 would be: ='Sheet 1'!B3. how do i formulate the equation so that i can do ='Sheet 2'!(C1)(D1)?. in other words i want to specify the column and row from the values declared in sheet 1 column c and column d respectively.
I'm from Macedonia, we have to values for VAT, 18% and 5%. I try to separate values that have 18% VAT and 5% VAT. I'm using IF function but not is that I need I'd like to SUM all cell from H2 to H24 that have 18% VAT in H27 and in G27 I'd like to SUM all cells that have 5% from H2 to H24.
I have a cell with several values; however, Excel seems to only "see" the first item. I have tried to use Text to Columns with no success. I have tried copying and pasting the cell in a variety of formats with still no luck. I think a macro may work, but I am not skilled enough to know what to do. Here is an example of the cell data:
In this case, the first item is PRT-BNS-SVR-01/p;2. I need separations between the / the ; and between each item. I am not sure where the quotes come from, as this only happens when I copy and paste to text. I can not see them in Excel. When I use Text to Columns, the first material will separate, but it's like the others so not exist.
I am in the process of creating a Phone Roster for my unit. On Sheet1 is their administrative data (Name, Position, Phone Number, etc.). What I am attempting to do is use a formula to find the persons name for that position and place it in the appropriate position and then their phone number below.
Admin!H:H contains the positions Admin!B:B contains their name Admin!N:N contains their phone number
I believe the following is what I need =LOOKUP("Operations NCO",Admin!$H:$H,Admin!$B:$B) and it works, but only for the first person on Sheet 2. After that it is random. If there a different formula I need to use to make this work?
When I get the phone number I used =LOOKUP("Operations CO",Admin!$H:$H,Admin!$N:$N) and the same thing happens. It works for the first person and everything else is incorrect.
A problem is the Admin sheet is listed alphabetically, not by position, so I cant use a specific cell to copy the data. I need the formula to find the position, then find the name and phone number of the person in said position.
I'm building a work schedule based on 12 hour days and I would like to separate a full weeks hours, 84 total, into the Regular hours and the Overtime hours that the employee will be paid. The way it is set up is I have 1 spread sheet that has the schedule for a full year (using "IN" as a working day and leaving the cell blank as a day off) and on a 2nd page I've set up with a COUNTIF formula to total the number of days worked (IN) during each pay period and then it multiplies the number of days by 12 to get the total hours.
Where I am having trouble is that the employees are paid Regular hours and OT hours in a single day and I don't know how to separate the total hours automatically into those two types of pay. For the first 5 days of their shift they are paid 8 hours of Regular time and 4 hours of OT time, and the last 2 days of the shift they are paid 12 hours of overtime. In a total of 7 days they will get 40 hours of Regular time and 44 hours of OT time.
To break this down into simple formulas, ie. (84 -40 = 44), won't work because if an employee works more than their 7 days the pattern they are paid restarts again with the 8 hours of Regular time and 4 hours of OT time and etc. (8 total days would be 48 hours of Regular time and 48 hours of OT time, max of 14 days per pay period).
Is there a formula out there that can break a number down in sequence (8-4-8-4-8-4-8-4-8-4-12-12) and total them in two different cells?
I have values in 4 Cols a,b,c,d and row count varies... For ex i make selection from A1 to D5
I want values of colA1 to A5 stored Range K1 values of colb1 to b5 stored in range K2 values of colC1 to C5 stored in range K3 and values of colD1 to D5 stored in range K4..
I need the values of each columns seperately based on the selection..
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.
I am trying to build a report based on data in a separate worksheet. The report must only show the name of the value and qty associated with it only if the qty is greater than 0.
As an example, the values can be colors.
Column A = Names of Colors: Red, Blue, Green, Yellow, Black Column B = Qty of each Color: 5,4,0,1,0
The report is held on a separate spreadsheet. I would like to only show the values that have qty's greater than 0, not any values that have a value of 0.
1. In my first column(Let's call it "input"), there will be a variety of values from 1-10 which will appear in cells as 1,7,10 or 4,5,6,7 or any other similar variation. The quantity of numbers can vary and will always be comma delimited.
2. Following the first column are 10 additional columns. For simplicity, let's say they're labelled 1-10.
3. If 2,5,6,9 is present in the first cell, I want the number 1 in the columns labelled 2,5,6 and 9. If 1,4 is present in the first cell, I want the number 1 in the columns labelled 1 and 4.
The input will only be in the first cell so I am hoping to find a way to automate the placement of 1's in the appropriate column depending on the input in the first column("input").
As a bonus, I'd like all cells not present in the first column to appear as a "0".
I have an excel sheet I create with lots of inventory items distributed amongst several locations. The first column is a location field where several 1-2 digit location codes are seperated by commas. I would love to get the macro to copy each row, once for each location code, onto a new tab as a new longer list. Because the amount of data per row varies, I need the entire row to be copied to the new tab....
If I am working from cell (D16), I am looking for a formula to compare the value in 2 different cells (D8 and D12) to a value in a third cell (D14) and the one with the closest value without going over returns a seperate value from either (D7 or D11), But if both D8 and D12 are above the value of (D14) then the closest value would be the answer and return the answer from (D7 or D11).
Example: D7=Bob and D8=25 D11=John and D12= 40 D14= 45 D16 (answer cell) =John OR: D7=Bob and D8=46 D11=John and D12=48 D14=45 D16 (answer cell) =Bob
I need to create time sheets for about 30 contract workers.
Each time sheet needs to have their name as well the date for sunday of that week inserted into the form. (once the date for sunday is entered into the sheet, the rest of the dates for the week will be extrapolated out).
Then, once the data is inserted for a single worker, I need that file to be saved using the workers name in the file name.
The script would then open a new time sheet template, insert the second workers name and the date for sunday and save the file. Repeate for next 30 workers.
I have spread sheet and values in some columns have highlighted in color.What I have to do is I need to quickly separate the highlighted data and put in the other spreadsheet.
