I have been finding answers on this website for soo long. But today is the day, I finally am stumped and could not find an answer here. I am setting up a standard excel sheet of data. Row 1 has labels and the data goes down each column.
the first column is 1 of only 5 words. the rest of the columns are number values.
The data in the first column will be 1 of 5 city names.
My question is how can I setup a cell to keep a sum of num1, num2 and num3 of each of the 5 cities. I have been scratching my head and googleing for a couple hours now.
I was thinking of using an if then statement. Here is my psuedo statement for a cell that would be keeping track of num1:
(if any cell in column 'city' = cityname add the current value in this cell to the value located in column num1 of the row thats currently being evaluated.
The cell that contains this formula would have to constantly monitor the city column and update the num1 column as needed.
I'm going to be using a spreadsheet to keep track of where different people are at. So if Person 1 is in Room 3, I will stick a 3 in the box next to their name and then can look at the spreadsheet whenever I need and see what room they are in. When I'm deciding what room to put a person in, though, I need to be able to quickly glance at a list of Room #'s and see what one's are still available. So I have a bank of Room #'s in the spreadsheet....1,2,3, etc.
What I'd like, is some way to set this up so that when I put, for example, "3" in the cell next to "Person 1" the spreadsheet automatically removes "3" from the bank of available Room #'s and when I delete the "3" because the person has left, it adds "3" back to the bank of available Rooms.
I have problem with function, I have array with random numbers in cell G1 and columns with X in some of the cells, now I want to check the cells with numbers from cell G1 in column F to see is there X or not if yes count if not do nothing. In attached example result should be 3.
I am trying to do with data validation, trying to stay away from vba on this... and it is probably very simple:
Cell A1, they can select Rice, Cheese, or Rabbit I want to use custom data validation on B1, so that if A1 = Rabbit, they can only enter 1. If it is blank or the other two choices, they can enter 1 through 10.
Can I do that with data validation? I can't get any if thens to work in it.
Would anyone have a script for something like this?
I'm looking at range c5:c1200
Starting at C5
If the first 2 digits in that number start with 01 then I want the formula to be in G5 = F5*12
if the first 2 digits are 03 then I want G5 = F5*24
I have several of these if statement to put in However I need it to check the entire range as well because the formula may be different in each cell (G)?
Set cell fill color based on number of adjacent cell
I am doing a research with the following information. In column B, I have the name of the buyer company, column B cells that contain numbers 1, 2 or 3 opposite the company name in column B depending on certain characteristics. I would like to mark the companies in column A in different colors (or some other recognizable way) as described below.
columns result col A col B col C 7 Materials Inc 3 7 Materials Inc 2 6 Rayon Inc 3 6 Rayon Inc 3 6 Rayon Inc 3 8 Commun Corp 1 8 Commun Corp 2 8 Commun Corp 3 8 Commun Corp 2 8 Commun Corp 1
I would like to have on output in column A as shown above according to the following criteria:
4: if there are only 1 in column C opposite the specific company in column B
5: if there are only 2 in column C opposite the specific company in column B
6: if there are only 3 in column C opposite the specific company in column B
7: if there are 1, 2 and 3 in column C opposite the specific company in column B
8: if there are 1 and 2 in column C opposite the specific company in column B
Any suitable function in excel or Macros that can work with the formatting, because I have tens of thousands of companies and it will take too much time to do it manually.
As you can see from below I already have a spreadsheet which updates the fill colour of cells based on a certain criteria. This was initially set up in Excel 2003....I have now moved onto 2010 and want to use a certain colour based on it's RGB value. I tried as you can see from below, the part which is commented out (as it didn't work)
Code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim n As Long, lngIndex As Long For n = 6 To 842
There are several hundred rows in each column that all need individual ranks. My first thought is to program a if then else statement for 1 to 10 with a % limit range, but this isnt feasable as i would have to program a separate if statement for each of the many hundred rows, or do one heck of a fancy loop that i just cant contemplate (as the rows are split and sepperated... no solid uniform place for the data to be plugged.) What is the best way to go about this? I'm at a total loss of ideas with my knowledge of excel.
I have a column (A) with names, Column (B) with platoon numbers. On another sheet I have a table for each platoon and I want it to be populated with the names of each platoon indevidually. The names are alphabetically and the platoon numbers are mixed.
Column A Column B Name Platoon J Goodman 1 L Barns 7 H Law 1 B Aims 10
and so on....
I want all the names from platoon 1 to appear in the table for platoon 1. I have about 3000 members and 40 platoons.
In the sheet I am working on I have some numbers that represent todays activity. These numbers are volatile and reset when the date advances. I have a column on another tab that has all the days of the month as F4:F33
The results I want will go in the G4:G33 column. C2 is Today() to avoid too many Today()s. Active!J10 is the value I need in the G column.
Basically what I need is the value from Active!J10 to be copied to whichever cell corresponds to the date next to it in the G4:G33 and then stay there when the date advances
The formula I am using for the g range is : =IF(F24=C2,Active!J10) =IF(F25=C2,Active!J10) =IF(F26=C2,Active!J10) And so on down the column.
I dont mind the FALSES for future dates, but the current one changes to false upon the advancement of the date. Basically, I need a running log of the final numbers going down the G range and for those values to stay there when the date advances.
This is a spreadsheet for a real estate office. The boss gave me a spreadsheet that he created to give a seller an idea how much money he will walk away with at closing. The spreadsheet includes all of the pertinate numbers but there is one item that he would like to have filled in automatically. When selling a house, you use a title service that charges a fee based on the price of the house. So, for example, If the house sells for $0 to $50,000, then let's say the fee is $112. If the house sells for $50,000 to $60,000, then the fee is $118. If the house sells for $60,000 to $70,000, then the fee is $125, and so on up to 1.5 million.
The boss' spreadsheet includes 5 different selling price scenarios, which I guess doesn't matter since you do the first one and the rest follow the same pattern.
So how do I automatically fill in a cell based on how one number falls within a range of other numbers? The fee schedule will be within the same spreadsheet, if that matters.
Ideally, I think they should be separate files, but this will be used in various locations as different realtors take it home or use it on their laptops.
I have a pivot table of values displaying agency's performance based on their sales percentage to target. When the race is 'loose', and all companies are 1 or more percentage point away from each other, I format the values without decimal places. If two agencies appear tied, I add a decimal place to seperate them. Occasionally the race is really tight and I have to increase the format to two decimal places to seperate them. how to do this automatically?
I have numbers that will display in column G. I have payment types entered in column E. So if 'C FUEL','FA','C M/C', is entered in column E I want the number in column G to be red.
I have a list of numbers in sequential order. There are numbers missing. Is there a way to have Excel insert rows for the numbers that are missing in the series.
I have a mass of data which look something like this:
table removed
and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.
So basically for example I would want the top row to now read:
table removed
and then apply the same procedure to the other ranges below this.
I am looking for a formula to grab only the numbers withing a cell that has text in it i.e (John Peters142 lp) or (Sally Jones54) or (Terry Cram310 jkb) sometimes there will be text after the number and sometimes not, the format is as shown with no space between name and number but a space after the number.
I need the " solutions cells " to match to the " source cells " and return a " yes " or " no ". in cells d4:d10. Ex: #1 cell C4=01234598, it will conduct a search & match of cell A5 which contains the number 123, since cell C4 contains all 3 numbers from cell A5, then the formula would result in a " Yes ". The match has to contain all 3 numbers from the solution cells to the source cells.
Ex: #2 cell C7=67891234, it will conduct a search & match of cell A8 =901. Since cell C7 only contains the 9&1 from cell A8, it would return a " No " because cell C7 did not contain all 3 numbers from cell A8. I'm not sure if the IF, Match, Index or Count formula is the right one to use, they all seem to partially work, but still don't complete this problem.
multiple numbers and text are in single cell which are separated through spaces or comma, is there any formula which can bypass text and add all the numbers in the cell.
For example:
red 12, yellow 10, green 5 27 red 3, blue 9 12 yellow 21, blue 3 24
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutG1G2B4C4D4E4F4G4= ABCDEFG104/30/07$107,777$0*$0$0$107,777209/06/07-$107,777$227,399*$98,962$91,165$309,7493???$28,046$170,458$0$86,434$18,243*4*$28,046$397,857* $185,396$109,408$417,526Sheet1* [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Using two columns of data: Column A with a list of first names sorted by first name, Column B with a different integer value for every cell in Column A. How do I write a formula that sums the values contained in Column B for each unique first name in Column A? The formula needs to identify where first name changes in Column A as there are different numbers of entries in Column A for each first name. (Note: I do not wish to use sub-totals, as I want to copy/paste values back into the results column and use sort to generate a new lookup table)
Have attached an example of the two columns I would like to test. Ideally I would like to see the results in a third adjacent column with the sum displayed only where a new first name appears in first column (the cells would be blank in the third column when the first name is the same as the row above).
I have a series of numbers going down from max to min in column M, in column N I have the number of counts of the same numbers.
The Modal value is calculated by a formula and already displayed in cell F3 (it needs no modification).
What I am trying to do is this:
1) Identify the address of the same modal value in column M.
2) From the modal values address in column M, search Column N searching upwards and downwards from the modal value address, until the first instance of the pre-specified count number is identified.
3) When the pre-specified count number is identified look beside in the cell in column M and display the value beside that pre-specified count in cell.
We stop at the first instance of that prespecified count both when 1) counting upwards and 2) counting downwards.
The upper value is displayed in F2 and the lower value is displayed in F4.
An example would be.
1 1 2 2 3 3 4 4 5 6 6 1 7 2 8 3 9 4 10 1
The modal value is 5 looking beside it as it has 6 counts, so in the count column we search upwards from the count of 6 until we encounter the pre-specified number of 4, then we look beside the count of 4 and observe that the number 4 above the modal value has 4 counts hence we display the number, now we search downwards from 6 counts until we reach the first instance of 4 we find it and the cell beside it contains the number 9 so we display the number 9.
The end result is numbers 4 and 9 that have 4 counts each.
I would like to extract numbers based on the first 2 digits (22....) of a column along with its next column (on the right) and put them on another sheet.
I got some functions from some of you to spot duplicate bar codes
the first function was this =IF(ISERROR(MATCH(D5;$D6:D$65536;0));"";D5) the second next to it this =COUNTIF($D:$D;$D5) and the third: =IF(COUNTIF(D:D;D5)>1;"DUPLICATE";"")
well fine but now I dlike to add the numbers of the 2 cells of those duplicate codes. In some cases I have more than 2 identical bar codes.
I quickly put together an inventory form on our company server where employees can essentially just enter part numbers, quantities, and a few other things, rather than write everything out. So what might happen is, an employee puts together a list of 10 items with their respective quantities, but a few days later, another employee pulls up the spreadsheet and needs to add a few more of a part already listed. Protocol for the company is to do a separate line, rather than add it to the line where the item is already listed. What I am trying to do is, create a separate sheet within that workbook, that simply lists the part numbers, in order, with their total quantity. I have attached a basic spreadsheet showing what I would want, with a tab at the bottom labeled, "What I want."
I'm trying to add up some values based on a criteria in another column. I have numbers in column A ranging from 0 - 100 and in column B have totals for those numbers. What i am trying to do is Add up the totals in column B based on the following criteria's in Column A.
<7, >7 & <=30, >30 & <=60, >60.
I have a summary page to display the individual results in separate cells.
