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
Fill the corresponding cell with last value entered?
View 9 Replies View RelatedI am creating a spreadsheet for inventory use. I want to have a running total in (1) cell based upon a new/different number being entered into a different cell.
Column B, Row 1 (This will be a new/different number entered every day - inventory in or out, so positive or negative number)
Column D, Row 1 (This will be a running total based on numbers inserted in previous 2 columns)
Basically what I have is a key inventory. So there is column A with key number, B should be keys IN, C should be keys OUT, or ideally B would be IN and OUT meaning, for inventory IN input a positive number and for inventory OUT input a negative number, and D Should be total.
Column E represents number of keys currently in the inventory. I was going to hide the current inventory column so all you see is IN/OUT and total.
So what I need is to be able to just come in and type in a number in the IN and/or OUT column, without having to add/subtract it with the number already in that column.
Bottom line, I'd like to be able to use IN and/or OUT columns to just type in numbers as they come and not have to worry about what's already in those columns and get correct total number.
Ok here it is. Attached worksheet shows
Column A - Key Numbers...No data value
Column B - Inventory IN
Column C - Inventory OUT
Column D - Total
Column E - Current Inventory(Starting point)
So the formula I used to get what i currently have is (=B2-C2+E2). This way whatever i input in columns C and C. totals out in D. But this way every time I want to add/subtract a number in B and C, i have to add to the number already in the column. I'd like to be able to type in a number in B and C as i go and still have a correct total. I wouldn't mind having just one column for in/out and use positive and negative numbers to differentiate inventory in or out.
Key Inventory - Test.xlsx
So I am having trouble figuring out how to do multiple text to equal multiple numbers. For instance right now I use =IF(E9="JOHN DOE","07", but I would like to have it where IF E9 = John Doe the cell shows 07, but IF the E9 = Sam Doe I want the cell to show 08, and the same IF E9 = Pat Doe the cell shows 09 and so on.
Meaning is there a way to have multiple functions go to a cell dependent on what is entered into E9 for example. I just want to have a number come out in one cell if a certain name is entered in a different cell. I loooked at this post [URL]....which got me started,
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?
Currently have a column of cells that after the data has been entered we would like the cell to lock to prevent changes. The sheet is currently protected with the cells in question unlocked (D173:D250). We would like to be able to change the data if we unprotect the sheet. We would also like to be able to add/delete cells (list) in the code as needed.
View 9 Replies View RelatedI have Two Sheets in same workbokk.
Sheet1 Sheet2
Sheet1 B2 = "any numeric value (whole digit i.e 10 or 55 like) - user input "
Sheet2 Starting point is cell B3.
What need to happen - As soon as user enter number in Sheet1 B2 = any value say, 20 then,
Sheet2.Select
Range("B3").select
suppose i assign value p as,
dim p
sheets("sheet1").select
p = range("B3).value
now here i want to indetify this number, i.e.- 20 (User input) and then as soon as user enter this value,
step 1 - sheet2 get select.
step 2 - from B3 sheet2, by row wise it select next 20 row as well next 20 columns.
for eg.- from B3, the data should select like following,
B3 to U22.
and this selected area highlight with single border only. That's it.Therefore, my main problem is how excel will understand this numeric number ?
I have a userform that I want to add to a button click event, to fill userform data into a different (already existing) workbook.
What I have is a userform for entering Purchase order information (Supplier, Date, PO#, Products, Descriptions, Qty's, expected dates etc.) What I am trying to accomplish with this particular button, is to 'Generate Purchase Order'... Have a seperate workbook, with a 'blank' pre-formatted formal purchase order that will be autofilled with said userform. The now generated Purchase Order ready for print/ save-as.
How do I write this formulae ??
For cell range M6:AV6 fill with N/R If the data in cell D1 = Wont have
I am looking for advice on how to move cells containing data (columns D to K in EXAMPLE A), upwards so that the information lines up with data already existing in columns A to C (to be included as part of an overall VBA routine).
In the upper example data in cell D2 needs to move upto D1, E3 to E1, F4 to F1, G7 to G1 etc and K9 to K1. Now, data on following rows is to move upto row 2 eg: E5 to E2, F6 to F2, G8 to G2.
The first 'block' of data starts at row 1 and finishes in this case at row 9.
The next 'block' of data starts at row 10 and finishes at row 18.
Row 19 shown is the start of the next 'block'. These 'blocks' may have upto 20 rows ....
I'm putting together a workbook to keep track of income generated from Contractors (I work in recruitment). I have an existing system that displays margin, hours worked and income (margin * hours) but what I am looking for is a neater solution that would still have the margin column, but when hours are entered into a cell it is factored by the margin and displays income in the same cell.
So to be sure I am describing correctly, I am after a formula that will enable me to
1. Enter a value into a cell (for the hours worked - lets call this B1)
2. Multiplies this value by another cell (a fixed amount, the margin - lets call this A1)
3. Displays this result (B1 * A1) in cell B1, the same cell as the hours were entered in.
I have a command button [Start] that when clicked, will enter the time the process started. I then wish to have the cell locked so the time cannot be changed after it has been entered. I tried this code:
Private Sub CommandButton7_Click()
Range("I5").Select
ActiveCell.FormulaR1C1 = Now()
Cell.Locked = True
End Sub
But am getting an error for the "cell.locked = true"
Any proper code to lock the cell after the data has been entered?
I am trying to create a macro to fill Sheet1 cells A1 through P1 down, and I would like it to be filled down based on the number in Sheet2 cell R1.
View 4 Replies View RelatedI'm working in MS Office Excel 2003. I have a spreadsheet with numerous drop down boxes and references to named ranges. Here's my current quandary:
In cell I12, I have a drop down box with listed choices A, B, C and D. In cells I15, I18, I21 and I24, I also have drop downs.
What I'm trying to do is when choice D is picked from I12 it locks cells I15, I18, I21 and I24 so that the choices in the drop down cannot be picked. Also, a popup message is displayed.
I was able to receive help in getting this pretty much taken care of. But the problem I'm having with the code is that the popup message is also being displayed whenever I make a choice from other drop down boxes. So if I choose option D from I12, it locks the other 4 cells and displays the message just fine, but then if I choose anything else from say K12 or L21 then the message continues to come up. Here's what I have...
In cell D10, there is a drop down menu with choices A, B, C and D. Cells D13, 16, 19 and 22 also have a drop down menu. What I would like the code to do, is when option D is chosen from D10's menu, cells D13, 16, 19 and 22 are locked so that the user cannot choose an option from any of those cells.
So basically, if D10="OptionD" then [whatever the value is to lock cells D13, 16, 19 and 22 from drop down use)
If possible, I'd like this as an IF function, and not a VBA macro. I'm not completely familiar with all of the IF functions...um...functionalities, so forgive me if I ask the impossible. But if the IF cannot do it, VBA code is fine.
When I type numbers in the cells from D4 to D14, E4 to E14 and so on, the particular cell should turn into "Green". All the the cells will remain "yellow" and blank until numbers are entered based on the daily cleaning of machines. When particular machine no is entered in to a cell cell, that cell should turn green(this shows that "service is done for that machine")
View 2 Replies View RelatedI am looking to use a VBA routine so when a Date is added in a cell and a number is in the cell below on running the routine I would like the Date to be filled by serial number to the cells to the right. So if D3 has a date in the cell say 03 July 2014 and E3 has the number 10 then to the right of D3 it fills the Date's 10 Cells to the right 03 July 2014 through to 13 July 2014.
Code:
Dates
03/07/2014
Amount
10
I have been using Excel for several years and I'm fairly proficient at its basic functionality. I've recently learned and made extensive use of pivot tables. However, I have not taken advantage of such features as Data Validation, VBA, etc.
View 3 Replies View RelatedI'm creating a "universal-fluctuating" vendor inventory return worksheet for a auto parts store that consist of one criteria (cores, warranties, or N/R ) and will return one or two results of core cost and/or unit cost. This part of the task I have accomplished by using a drop down list for my criteria and my results will appear in two different columns using a Vlookup table. The problem is due to inventory fluctuating from cores and waranties on a month by month basis, vendor requiremnts differ for the number of units returned, and last make the boss happy on ink and papers supplies :D I was wondering if it is possible loop my code in a given column where it will move my code to the next row untill I reach a grand total?
View 8 Replies View RelatedI am working on an Inventory Spreadsheet. I have need it to date time stamp that willpopulate a different cell /field when text are entered in a different cell/field and will not change all the previous dates entered on the spreadsheetevery time I re-open the spreadsheet. I formula I am using is =IF(E3<>"",IF(D15="",NOW(),D15),"")and it changes each time I open thesheet to do the inventory so I do not know the date of the last inventory. Iwant to capture the actual date that the inventory was completed in thesecond/different cell?
View 9 Replies View RelatedI would like to do conditional formatting...
Range (A1:Y192)
Turn the row number (identified in cell AC1) into RED fill.
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 want to fill down a column and instead of my formula changing from A6 to A7 i want it to change to B6.
View 9 Replies View RelatedI have a Validation drop down box (column B) and a defined range drop down box (column E linked to the Totals tab with A2-A31 defined as Products) that I need to activate everytime data is added to a new line in column 'A' and cells in column 'B' or 'E' are selected. Additionally, they need to activate if the cells in column 'B' or 'E' are selected for any of the old data so changes can be made. This will help reduce the overall file size and calculation time for a year's worth of data.
There is already some code on the worksheet that looks like it can be adapted if someone knows how to code in the drop down boxes.
I have a set of data in column R, with an unknown number of rows that looks like this
Days Late
-28
150
3
16
41
.
.
.
and I have written a script to add an adjacent column "S". I want to fill column S with conditional values based on the value of column R, sorted into categories such as "On Time", "Less than One Month Late", etc. Here is what I have so far, it doesn't work:
Is it possible to have a macro create a hyper link once data is entered into specific cell, i have a multi sheet work book used as a stock card system.
Each stock item has its own worksheet and the product code is entered in to cell E3
(which in turn has a macro in it to change the sheet name to match the product code).
the macro also inserts a row of info into row 5 of another sheet used as a summary sheet ( CONTROL.SHEET or SHEET 4) using copy and paste link.
i need a macro to generate a hyperlink in cell D4 of the control sheet between the row info and the matching product code sheet ( the product code is displayed in cell E4 of the control sheet.
I only input one item at a time and then manually cut and insert the row info into the stock list on control sheet. so every time i input a new item, the row info is always in row 5 of the control sheet.
I need a formula that will calculate the number of days from a date entered into cell A1 to today's date. Whether it's before or after todays date. Example:
5/10/2009 to today is -9
5/22/2009 to today is 3
I have to keep a record of the running totals of school house points for each week. The problem is that teachers are very lazy and don't record data every week so I have many blank cells which my current formula can't cope with. I've tried using N/A but it doesn’t seem to work? (Have thought about threats of violence but would probably lose my job) I’m if there is no data (blank or 0) then I need it to keep the same total in the cell as the previous week and so on until new data is entered and updates the total. I have attached a simplified copy: Teachers enter points in the HP sheet, the Running Totals sheet (TAB) contains the formula.
View 4 Replies View RelatedExample;
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 have been stuck on this for months.