I would like a formula to return the top 10 products sorted by value, but to return the colour name in order starting with the larges and the value in a different column. Ideally I would like to do as little formating as possible. I have attached a spreadsheet as an example
View 2 RepliesI need someone to go through them assigning a building number, level and location to each of them. I am using drop down menus, the first choice is building and has the following options:
B1
B2
B3
B4A
B4B
B5
B7
Depending on the option selected there maybe different locations, i.e. B1 has the following available locations:
1-4
4-16
MER
ALL
KIT
Whereas B7 only has: ALL. The third column, level, operates in a similar way.
I am trying to show a value in a cell, based on the values found in other cells. Essentially, here is what I've got:
If C2 is greater than A2, B2, D2, then put the value found in C1 in E1.
I've been working on this chart for a while and can't seem to get my x and y axis as well as my series corrected. I have time and distance but since my distances are small I cant seem to make sense on how best to depict my data. Right now it is shown as data points but would love to show value.
View 2 Replies View Related I need to count the # of unique employee #s based on the criteria in column B. If the value in column B=0 then I do not want to count the employee #.
I realize a pivot table would be much easier but I need to show each line which includes other data.
I have a data validation list where more than one value should show the same list. Instead of make multiple range, I want to make it so if any of the values are in a cell the data validation list will show the list that goes with those values.
View 4 Replies View RelatedI have 2 different formulas that I need changed in a similar way.
The first formula is for cell AV11:
=SUM(BI11,BP11,BW11,CD11,CK11,CR11,CY11,DF11,DM11,DT11,EA11)+10
Every cell starts off blank.
What I need is for cell AV11 to always start off blank until data is entered into one of the other cells. The problem is that since the sum always needs to be +10 only when data is entered in the other cells, I don't know how to keep 10 from showing in cell AV11 when no data is typed in the other cells.
The other formula is for cell CO39:
=(CU8)+3
I pretty much need the same thing. If no data is entered in cell CU8, then I do not want cell CO39 to show the 3.
I have a column of data held in column B.
I am required to show the following.
If there is data in the cell then it is to be left. If there is no data in the cell then I would like to show the value 0.
I have tried using a circular reference, using the formula =IF(ISBLANK(B1),0,B1) and other similar formulas but they dont work as the formula overwrites the data in it.
do I need a macro?, or conditional formatting?
I want to show values entered with percentage sign.(%) Right now, if i enter any value say,10 in a cell having percentage format, shows 1000%. For that, I don't want to enter 0.1. I just want to enter any value and see it with percentage symbol.
View 4 Replies View RelatedI have the following situation: I have some values in column A. other values in column B. I want to be able to display a list using the values in column A and values in column B. A1-> B1, etc.. Attach file: Book2.xlsx
View 5 Replies View RelatedI've tried creating a formual to only show the min values for each row,If
any values in my table is equal to 0, than, it should not display a 0, but
the lowest, positive number available. in my table. However, I'm not able to get it fully functionable.
Here's my formula:
i try to find any function, which make this.
I have a table: ....
I am creating a line graph to display changes in Cost Per Unit (CPU) of a given item over time.
The issue I have is that the CPU data on the line graph is displayed as 0 values where data is yet to be inputted. Instead I want the graph to remain blank where no data has yet been added. How can this be done?
find attached a simplified mock up of the spreadsheet.
Constant (fixed) values are:
- Date
- Hourly Rate
Values inputted on a daily basis are:
- Hours Worked
- Volume Picked
Calculated values are:
- Total Cost
- Cost Per Unit
- Mean [=average(CPU)]
- UWL [=mean+(2*stdev(CPU))]
- UAL [=mean+(3*stdev(CPU))]
I'm open to using VBA if necessary, however as I will not be the only user of this workbook when it is complete I would prefer to keep it a simple as possible.
I am modifying a template from MS's site. Its a recipe tracker.
They have one sheet with a table on it. Some of the data in this table is pulled, using a formula, from the different worksheets (recipes). The particular formula they use is this:
However, when I try to do my own version of it, simply changing the '!Category' portion, it doesnt work. The cell just shows the formula and I cant figure out why. Sometimes, Ill type out the same formula that they have working and it doesnt work, which makes no sense to me.
Here is the template for reference: [URL] .........
I am creating a spreadsheet that allocates hours to different categories of work - for example, "Fire Protection". I need the allocated hours for all of the different Fire Protection duties to only show up when a value greater than zero is entered in the column at the top of this category entitled "Fire Protection". If "Fire Protection" has a value of zero, then all fire protection-related duties will automatically be zero as well - in effect, this category of hours is nullified.
View 1 Replies View RelatedI have done a spreadsheet that shows leave for staff. At anyone time there can be only 3 managers and 4 staff off as a maximum and the cell value for the day is entered as "AL". Therefore on a given day if someone tried to enter a fifth "AL" for example in the staff section a pop up displays stating they have reached a maximum.
View 4 Replies View RelatedFor input I have 2 columns:
A---B
1---5
3---4
-----
2----
I need for output column C:
C
1
2
3
4
5
It's possible to do that using one or more function ?
Basically, I am looking for a macro code to link to an 'Update' button that will do the following;
I will have 2 sheets, one sheet will be referencing data from other sheet with the following
=IF(Log Sheet!A4="Yes",(A1),"0")
This means, if it needs to be 'referred' it will go to the other sheet, and if it doesn't, then it wont and it will show up as 0.
Basically, every row will have this type of formula for 30 columns, and i would like a macro code that will hide values of 0, but even once hidden, will then show them again if in the other spreadsheet i change the value to yes, meaning i want the previously hidden row to show.
I have spread sheet that adds meter readings. As you enter the reading it subtracts from previous month's and gives you net value. But this always shows a negative value in the following month until you enter the current reading.
Also I would like to be able to total these net values as a running total. Is there a simple way to not show the negative values and or use those when getting the running total?
I have selected 6 rows and 2 columns in a sheet and I have a button in the same sheet.Now I click the button it triggers the button_click where I have put msgbox to popup.But I need this selected value in message box instead of the my hard coded string.
sample value in the selected cell:
1,1.005
2, 3.006
.........
..........
I have an inventory sheet that uses multiple userform comboboxes to input data to a sheet. Everything works great except on one userform "pullmat" where the user removes inventory from a unique list of all material available in "Master Log" sheet. The combobox that im trying to fix is "combobox3" in the "pullmat" userform. I'm trying to get that combobox to only show P.O. numbers that are related to the material selected in the "Material Code / Name- combobox2. In other words, the user should only be able to select a P.O.# that matches that specific material on the Master Log sheet.
The file size is too large to attach but I can email a copy if needed.
I have a list of part numbers in a column and a list of suppliers in another column. Parts may have anywhere from 1 to 15 suppliers.
Is there an easy way to show each part number once down one column, with the suppliers going across the one row for each part number?
Example:
PN#Supplier
123ABC
123DEF
123GHI
123JKL
123MNO
123PQR
456ABC
456DEF
456GHI
456JKL
456MNO
456PQR
456STU
Desired Output:
PN#Sup1Sup2Sup3Sup4Sup5Sup6Sup7
123ABCDEFGHIJKLMNOPQR
456ABCDEFGHIJKLMNOPQRSTU
I tried a pivot table,
I am trying to add 2 cell values together then show the total as a % of a value in a 3rd cell, however I also need it to allow for 0 values in the chosen cells without displaying an error message or it messes up the average formula elsewhere on the sheet?
View 2 Replies View RelatedI have 10 graphs with more than 20 legend entries. However, each graph only needs 3-4 elements out of the 20 legend entries in the graph. Is there any way to force Excel to only show those legend entries that have a value? (without deleting them manually)
View 12 Replies View RelatedFor example: C4=5,C5=18, and C6=7.
I want A1 to show 4,5,18,7 (the values of cells C4 to C6).
I believe I have done this before, about 10+ years ago.
I have set of data in excel which is basically a table of workload and I need to be able to select someones name from a dropdown list and all the other names are removed.
I've attached a screen shot of an example, I want to be able to select any name from a dropdown e.g. Select Trevor and then only cells which contain Trevor are shown (i.e. the other names disappear). BUT all the jobs 1-10 and Stage 1-3 have to stay even if Trevor doesn't work on them.
Screen Shot 2013-10-15 at 16.44.12.png
I would like to compare two different columns and show only non matching values that exist in Column B but not in Column A in the next cell.
If we assume that my data is:
Column A: Column B:
A Z
B E
C B
D K
E A
So, I would like to show these values in the next cell as follows:
Column A: Column B: Column C:A Z Z
B E K
C B
D K
E A
Basically I have Two Columns.
Column A: Column B:
GN0001 DB0002
DB0002 DP0012
GE0025 GN0001
GR0026 GE0025
DR1235 GR0026
DP0012 DR1235
GS0025
PC128
Column A is the master codes list for a unique product.
Now every time a product is sold, its unique code number is entered into column B.
What I want excel to do is compare Column A and Column B and show me only the codes that DO NOT match.
This way I know which items should be present in store when I do a physical stock check.
So the end result should be:
Column A: Column B: Column C:
GN0001 DB0002 GS0025
DB0002 DP0012 PC128
GE0025 GN0001
GR0026 GE0025
DR1235 GR0026
DP0012 DR1235
GS0025
PC128
I have a list of 9 values ranging from 1 to 9 generated by formulas in column A. they can only be "1,2,3,4,5,6,7,8, or 9" and can be repetitive. Is there a formula i can use (not macro please) that will show me what values are missing from the list?
Example
A B
1
4
5
1
2
1
8
7
1
*Note there are 9 values in column A in any order. In this scenario the answer i would like shown is "3,6,9".
I attached an excel that I created. But I have one problem with it.
View 4 Replies View Related