Plotting Range Of Numbers As One Entry (min And Max Standards)
May 23, 2014
I want to plot a graph where our customers can compare their results against our standards. (line graph) This has been easy in the past but rather than just having a single value as the standard we now a range for each standard a minimum value and a maximum value. How can i get both the minimum value and the maximum value plotted on the chart? This would be two lines with the area between the standards shaded in. The customers could then plot their results to compare against our standards?
I am trying to generate a column chart with a high-low range overlay so that it looks like error bars. I've precalculated all values but cannot figure out how to add the high-low range lines. The ranges are in the min and max columns.
I tried using custom error bars but it did not work.
I am attempting to compare S1 & S2 (samples) to guideline values (guide1 and guide 2).
If Guide 1 value is exceeded I would like it highlighted and if guide 2 is exceeded I would like the same cell to be have the font colour changed.
The Sample values that have < signs are currently being highlighted as an exceedance. I'd like them to obviously not be highlighted as they are not an exceedance of the guideline values.Needhelp.xlsx
In my efforts to improve worksheet readability across the board at my work I am researching spreadsheet standards. I have found the Spreadsheet standards review board http://www.ssrb.org/index.html that has a massive document containing valuable standards to adhere to for financial modeling. Since I learn best by example, I am wondering what standards any of you guys are using and if possible post a sample spreadsheet containing those standards.
I need to have a message box appear if a user tries to enter a value not between 992200000 and 992299999 in the range B11:B1182. I used the following code to test this out (only for B11).
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Range("B11").Value < 0 Then If Range("B11").Value < 992200000 Or Range("B11").Value > 992299999 Then MsgBox "Please enter the Associate ID" End If End If End Sub
The problem is A: I need to apply this to every cell in the aforementioned range. And B: I need to only restrict this when there is a number in the cell (I tried to add the first If statement to do that, but it didn't work).
I want to show the number of weeks remaining from the sum of a column BUT I want it to distinguish between the entry in another column.
See below.
Columns B to L represent a number of weeks
EG1 Column C: Year 1 has a set amount of 47 and C156 displays this. C157 should display the amount of weeks in Column C but only when a number "1" appears in Column "Y" C158 displays the total amount remaining
EG2 Column H: Year 2 has a set amount of 38 and H160 displays this. H161 should display the amount of weeks in Column C but only when a number "2" appears in Column "Y" C162 displays the total amount remaining
I have a sheet with thousands of rows of ID's which are made up of a series of letters, numbers, underscores and or spaces. Within the sheet there are numerous duplicates so I need to able extract the numerical element, which is the key data to be able to identify the duplicates.
The numbers I need to extract are mainly 6 and 7 digits long however don't always sit in the same place within the ID. I don't need the 3 digit number sequences, just the 6 & 7 number sequences
I've found a formula that strips out all the numbers, however it also included the 3 digit numbers which I don't want. I've also used Text To Columns to break up the ID across a number of columns however that leaves me with 2 and sometimes 3 columns where a 6 digit ID appears
Is there a simple way to extract the number if it's a certain length? i.e. 6 or 7 digits long
Example. I have a range of information on sheet 2 in cells A1:A25 that contains values that are text. Another range in B1:B25 that has numerical values. I would like to link the last value entered from those ranges to cell A1 & B1 on Sheet 1. Is there a formula option to do this?
I have created a very simple form. I want the user of my spreadsheet to fill in 1 cell in each section of the form, but they will have a choice of a couple of cells to fill in. For example, they can fill in one cell with the temperature in Fahrenheit or another cell with the temperature in Celsius. I want Excel to stop the user from putting data in more than one cell. (This way they can't put in a value for both boxes, which could confound their outcome.)
I have a formula that calculates the number of rows in a spreadsheet. I would like the results of that formula to complete a range description. For instance, if the range would be expressed as A1:A20 (for a file with 20 rows), and the result of the formula (the cell enty) for a different file is 35, I would want the range to be expressed as A1:Ax, where x is equivalent to the result of the formula (effectively A1:A35, but expressed with a reference to the cell with "35" in it, not the 35 itself).
I'm having a problem with a dynamic range. I've re-checked my formula at least 10X and its syntax is correct. The problem is the dynamic range stops short 3 rows up from the last entry.
I have a user form that has a combobox, two textboxes, and a button on it. When the form is loaded, the combobox fills with data from a worksheet I created. THe worksheet has a column with the item names, and another column with the quanity of each item. The combobox is filled with the item names. SO far I have this done. My issue is that when the user selects an item from the combobox, and enters a quantity in to the first textbox, and then clicks the button, I want the quantity in the textbox to add to the quantity cell that the part number from the combobox references to. Then I want this new quantity to show in the cell and the second textbox.
I have created a worksheet with different validation rules for different columns for users to enter data. Is there a way I can make it so each cell must be filled in? I don't want any blank cells. Is it possible to create some so that the user must fill in the cell before moving on to the next cell.
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 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 trying to work up a referral tracker for my wife's employer. I have Named Ranges on sheet 2, a running log on sheet 1. I also have a userform that is being populated from the ranges on sheet 2. I have the project mostly functional, except for a feature I would like to add in. I am interested in adding to the named range that is being accessed if the entry is not in list, via the combobox1 on the userform. Also, if possible, I would like the list to re-sort behind the scenes so the added entry is properly located in A-Z format for next time. After searching the web a bit, I found some code that might work with some tinkering, but currently I am having issues with it. Here is the bit I am trying to use.
[Code] ....
I have been getting 424 Object Errors and a few others as I continue to mess with this. I am also attaching the project if someone may see a better way of getting the task accomplished. The overall scope of this is to log all referral sources so monthly and yearly reports could be made.
I have to report Actual Headcount every month. Since I cannot add the Headcount numbers in the FY Column. What is the best possible forumula I can use to pick the last month's reported numbers into the FY Column.
For example.
I have 13 columns (A - M). Jan - Dec and the 13th column is the FY number.
This month, I am reporting March numbers in Column C
Column D through N (Apr through Dec) are emplty
I need Column N (FY number) to read Column C and enter the value from that cell.
similarly when I report April numbers in Column D next month, I want Column N to read April numbers and ignore the prior months.
I tried using the min/max function but since h/c fluctuate every month I could not use that. I could use an "if" statement and go back every column but that seems to be inefficient way to solving for this. Any thoughts?
I have a range B1:B20. These cells are populated by the user and I want to ensure that no two entries are the same. Is there a way of presenting a dialog box that prompts the user to enter a different value if the value they are trying to enter is already entered somewhere in the range?
I have a number of named ranges on seperate sheets. As I add more entries to each sheet, the named range is naturally increasing so I dont have a fixed cell refence Effectively what I would like to know is - is there coding that would allow me to copy one named range - and paste the values to the the row beneath the last entry in a second named range. Filtering these I can do - it is just the copying that has me stumped
I am having a sheet where I keep track of when online surveys have been sent to users. The users enail address (column K) may be on the list for several times, but I need to make sure that there is at least a 7 days pause between sending the first mail and the second, depending on the visit date (column G).If there are less than 7 days between two entries with the same email address, the user is not qualified for taking another survey.
I was thinking to write an IF formula which returns either 1 or 0 and then let conditional formatting highlight and HIDE the row via a VBA loop.
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.
Is there a formula that will allow me to look for the existence of any number value in a row of one worksheet and then return a specific number value in a cell on another worksheet? For example, if the formula finds any number value it will always return the number 15 to a cell on another worksheet.
how do I plot the axis data in my graph in an increasing order, when I have my datas in a random order?
In other words, I have bunch of data to be plotted as my x-axis. All numbers are within [0;1], but they all in a random order, like (0.001, 0.923, 0.120, ...). And I want them to be in an increasing order.
I use spectrometer in my reesearch. The datas are in (a, b) format in single cell. Is it possible to seperate a and b seperately? like a in seperate cell and b in seperate cell?
i have this set of data (d.jpg). each cell is 2ms.
I want to compute how much is spent on each set (shown as one color) until the sate changes. In other word, the values are voltages, so i want to know how often the voltage toggles. then plot the toggling instances.
