Cell 1 equals a number
Cell 2 is the number entered into cell one times(X) 35 up until the number 6 is entered. Any number above 6 is Times 50. So. 1-6 is times(X) 35 while 6+ is Times(X) 50.
I am. Here's another example:
A salesperson gets a commission of $35 for each widget sold up to the first 6 widgets. If he sells more than six widgets, he gets paid $35 for the first 6 and $50 for anthing over 6.
The sales person will enter the number of widgets sold into a cell and the formula should do the rest.
I am entering values in columns AV and BD of the attached spreadsheet. Rather than manually editing each value to adopt the decimal place value of the adjacent cell is there a formula that can achieve this before the value is entered? The decimal place values are determined using a VLOOKUP table (column3) on the NES tab.
I want the cell to remain blank until at least on variable is entered, but then I want it to calculate the average of only the cells that have a variable in them.
I need to achieve the situation below with a formula. I have tried simply doing, for example, = B3+1 but this throws an error, probably because the value being checked is alphanumeric.
Data A001
Data A002
Add next number (A003) when data entered in cell to left
In sheet1, (B2 & C2) are the numbers entered by the user. After they entered, it will create rows of table according to the number of groups they entered on the respective sheets.
Where A is associated with 2 other letters, B and C. D is associated with one other letter, E, and F is associated with 3 other letters. I want an automated way to paste this into 2 columns in a new sheet, like this:
I have a question regarding the data validation lists. What i have is 4 different lists
Department Assays Instrument Manufacturer
What I want to do is...once a user chooses a department, he gets a list of assays done in the chosen dept. and also a list of instrument used by the dept and a list of manufacturers
and was able to succeed in creating dependent lists to some extent...I am attaching whatever I have able to accomplish so far, which is able to pull up the assay list based on the dept. ...but am confused as to how to get the Instrument and Manufacturer list at the same time .....please choose 'Chem 2" from the dept list to see what I have managed to do so far
I am creating a spreadsheet i want it to be that i make a selection from a drop down and this will then narrow the options available in the next drop down. Say box one you select 'half day' and then the next drop down menu will automatically loose all the data for a 'full day' and only show half day information such as costs for food and room bookings ect. i have made the drop downs but im not sure what to do next.
I have a list of electrical item brands in column B and a list of item types in column C what i need to happen is when i select a certain brand with a certain type i would like the cost of the item to automatically enter in to column D!
Because of my rubbish description i have attached an example
I am trying to make a drop down list and based on what I select from the drop down needs to populate a different drop down list with information based on what I select.
For example: if I have Sheet1, Sheet2, Sheet3 and Sheet4. Inside of Sheet4 (My Main Sheet) I have a Data Validation (Drop Down list) in Cell A5 and Cell B5
I would like to choose Sheet1 from Cell A5 and in that same sheet (Sheet4) in Cell B5 I want to have a drop down with a list of names that are located in Sheet1 in Column A:A. Or If I would've picked Sheet2 in Cell A5 of (Sheet4), I would like to see a drop down with a list of names in Cell B5 (Sheet4) that are on Sheet2 in Column A:A . Is this possible?
So far I could achive it if information I want is located on the same Sheet. But I need it to work if info is located on different Sheets
I'm trying to do a formula that references a cell and returns a different result dependant on the number in the cell being referenced.
For example I've said if A1 has a 3 in it then put the word TEST as the result, plus if it has a 4 put the word RESULT.
What I wrote as my formula is as follows-
=IF(A1=3,"TEST")+IF(A1=4,"RESULT")
It works fine when I only use one result but goes wrong when I add two. If I change the words I want to show to numbers it comes up fine but with words it just returns a Value error.
I have declared a 'long' variable. however; how do I change the variable data type to string if user input is not a number? how do I recognize the user input's datatype?
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
I would like to be able to enter a number in cell a1. Let's say 44.2. I want that number to actually be locked in as 100'-0" in cell b1. Below in the next row I want to enter another number, lets say that one is 41.5 into cell a2. And then I want the cell b2 to return the difference in feet and inches. So ultimately the only cells that I will entering data into is in column a and column b would just return information.
The other thing I am trying to figure out is a formula for converting decimal feet to feet and inches (which I have found) but a formula that is less than 40 characters.
Im trying to enter an employees name into a data list. I have a program that generates a list including their employee number into column B. I want to write something to put the employee name into column C. Im working with 60+ employees so i dont think if statements will work? anyway to reference a chart or table with the numbers and names on it?
if I use the following formulae in A1 and fill it down, I get the serialization 1,2,3....etc. =(INT((ROW()-1)/1)+1)*1
But if I begin formulae at A11 and fill down, I get 11,12,13.....etc. Now I type 1 in A11, how do I modify the formula so that A12 will be 2, A13 will be 3, A14 will be 4 and so on ?
Is there a function in excel that alerts you if you have entered a number (or word!) more than an agreed amount of times?? e.g. If you have agreed not to input the number 7 more than 3 times in a selected range, but then do so, will excel inform you??
I want to pick the last number entered in column and put it in a formula. Is there an easy way to do this, or do I have to stack a lot of IFs in a string.
I have been working on a spreadsheet with 3 sheets and have done everything except the last function which I just can't work out. Data is entered on the first sheet in a list with an item number, description, dates etc and then finally, if they are a certain type of item, a variation, a 'variation number' in a column.
The next sheet is then called 'variations' and here starts my problem. I want to have a formula that picks up if a variation number has been entered on sheet one in the 'variation number' column and if so, add that item into this sheet, in order.
So to summarise, I need sheet 2 to search on sheet 1 in a specific column for numbers 1-30(ish) for a series of items. I then need sheet 2 to list each of these in order, copying over each item that goes with the number from sheet 1 in the same format.
I am trying to simply count the number of times each entered name appears on my list IE if John Smith appears 3 times in one sheet, in a column after his name would simply be the number 3. I tried this doing =COUNTIF(A8,A:A) Where A8 is his name and column A is all names. I keep a return value of 0 every time!!!!! I even tried =COUNTIF(A7,A12) where they were both the same names. And yes,I did do Ctrl + Shift - enter
trying to limit the number of characters entered in a cell.
I clicked on Custom then =LEN(A10)=6 which works when i enter more or less than 6 characters, however it lets you paste in something which has more than 6 charters.
I want to incoroprate into my Workbook a sheet with a list of phone numbers, that sheet can be added to and more numbers entered if necessary and will be called "DATA"
The questions is this. If one of those numbers in the "DATA" sheet is entered in any other sheet in the workbook can the cell that number has been written on (not the one on the data sheet) be made to change colour?
My colleague in accounts has a sheet that lists all the invoices and values and other 'accounts department things' that I know little about.
Anyway, she has to manually input all the invoices she receives onto this excel document but sometime receives duplicate invoices (& inputs them twice be accident).
Is it possible for someone with macro skills to create a macro to run in a worksheet so that if she enters a number in a given column more than once an alert box appears to tell her. I've summarised the 'rules' below*
*Can is be written so that I can edit which column the alert is based on?
[In (for example) column F is a list of invoice numbers - all unique]
If user types a number in column F that matches a number that already exists, show alert box "THERE APPEARS TO BE A DUPLICATE ENTRY IN COLUMN F"
====
Any further clarification, let me know i'll try to post a blank sheet if my boss lets me.
I've done lots of looking on the forum, and the helpfiles, but just cant seem to format my text box properly! Its a form where the user inputs to the text box. I want to control thatway where they enter numbers e.g 05052008 and it gets converted to 05/05/2008 or 05-05-2008. I've tried a range of things, but am really stumped!
what I am trying to do is take the number in the G column multiply it by 2.9% and add 0.30. For instance if 20.00 is in the G2 cell, the number I want the formula to produce is .88
the formula works for me but what happens is the rest of my sheet that does not have any numbers in the G column gets filled with .30
How do I prevent the formula from calculating if the G column is blank?
I am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.
Dim Totals As Range Set Totals = [U37: AE37]
Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.
