I have the following code would like to have the results from the input box to copied to cell B7, however it is not copying the results.
Private Sub CommandButton1_Click()
'Sub AddSheetTest()
'Replace "Sheet1" with the name of the sheet to be copied.
Dim i As Integer, x As Integer
Dim shtname As String
Dim shtTemp As Worksheet
[code]...
Here is an example of what I am trying to do in E2013.
The orange Bin is my input. It want that to return the column associated with the # as show in orange output column. I think this is an index command? I cannot seem to make it work.
Also - is there an easier way to insert sheets other than a screen grab and photos?
I have a spreadsheet with 5 columns. A1 to A5
Entries are made into columns A2 to A5 but only ever one entry across all cells
If an entry is made into A2 to A5 the I need a "P" to be placed into A1
I am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:
Input Box Msg 1 - "What is your labor cost?" (NUM1)
Input Box Msg 2 - "What is your productivity rate?" (NUM2)
Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:
=(NUM1*(NUM2*$H10))/$H10
So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.
I would like to know if it is possible to transfer values of cells this way?
I've attached the excel file for easier reference.
I am just starting to get my feet a little damp with VBA and I am trying to make a macro that will act whenever any text is entered in any cell within a 3x3 square. Whenever any text is entered in any of those cells I would like it to enter a text ( "X" ) in a cell which is specified by a cell in the spreadsheet (this cell will output which cell the macro should write the text in in this format "A:1" or "C:3"). How can I make the program act only in reaction to the user entering thier text, and also, how can I get the macro to read A:1 and enter the text into that specific cell? Any help would be so awesome, the book I have is really difficult to navigate.
View 9 Replies View RelatedI'm wondering if it is at all possible to have a cell comment automatically update depending on what data is input in the cell (via user input, VLOOKUP, etc.)?
For example, if cell A1 contains the text "CHARLES" with a comment saying "Employee of the Month", and is then subsequently updated with the new text "JOHN" (again, via direct input, a VLOOKUP, data validation, etc.) is there a way to have the comment automatically update to say something else, such as "Team Lead" for example?
I've considered using VBA to accomplish my goal, but am unsure how to compose an effective code to do so. I've also considered perhaps creating a named table filled with all the different comment possibilities I would like to have used in this cell and then inputting a formula in either cell A1, or the comment contained therein, that would then call the corresponding text from that table based on the data in cell A1. Honestly, I'm not sure that what I'm trying to do is even possible;
I have 2 problems relating to LOOKUP.
Not sure if Excel can perform these calculations as they could get to complex.
Problem 1
Can it be possible to have excel look at data from one cell reference another cell then display the results from the cell next to it in another cell, sort of example:
Tab 1 (Never changes)
AB
Bob1
Jon2
Fred3
Tab 2 (Dynamic, changes each week)
AB
Jon
Fred
Bob
So it would work as follows.
Tab2 column B will take Tab2 column A’s data check Tab1 column A and display Tab1 column B’s result.
Problem 2
Weekly league rank table that shows position movements week by week
Example.
Week1
1Jon
2Bob
3Fred
Week2
1FredUp 2
2BobNot Moved
3JonDown 2
Can Excel calculate/show the actual movements of league positions?
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
View 2 Replies View RelatedI would like to be able to enter the data into the grey cells and then have the yellow cells auto generate a result. The only problem is that the required value in cell B5 is from when the entered value in B4 is looked up in the table, rounded UP to the next highest value and then the answer from the next cell.
So, if I enter in B4 a value of 500, a lookup is made in the table and the next highest value would be 553 with the correct answer shown in B5 being 13.
I am working in both 2003 and 2007 Excel.
This is a simplified version of my worksheet:
I have two cells, A2 and B2. When I enter a numeric value into B2, I want it only allowed when certain text values are in A2. I.E.: Allow any value in B2, but only if A1 is either "SYDN or "ADEL".
Now using info found in an old post, I can achieve this with a combination of data validation and code:
=OR(A2="SYDN",A2="ADEL") for the validation and for the code:
[Code] ......
The only problem is I also need to reset B2 if A2 changes to invalid data as a result of some other input to that cell (A2). In other words if B2 is showing "1" already and A2 were to change from "SYDN" to say "LOND", then B2 needs to be cleared. If A2 changed to "ADEL" then it doesn't need to clear but it is ok if it does because it is fine to re-enter "1" into B2 again.
I have attempted to attach a sample worksheet : Xl0000003.xls‎
Macro Based on an input cell’s (e.g. B12) setting of “yes” or “no”, format several other cells (e.g. B20:G20, B23:F23, C26:J26) as follows: keep the existing yellow shading if input cell is “yes”, change to grey shading if input cell is “no”. I would like this macro to run automatically whenever the input cell value is changed.
View 2 Replies View RelatedI have to design an input user form (a button that opens an input box or dialog box), which asks user to "Enter number of "columns"".
User enters a number i.e. 2 or 3 etc
It then asks user for the headings of these coulmns and places first column heading in A1 and second column heading in B1, third in C1 and so on ...
I attached my example with this message, I want the cell Z6 for instance to make the sum of the "X" of the same record based on their "weight" in the gray ribbon respectively.
View 3 Replies View RelatedI am pulling what hair i have left out of my head, I am a New Desk Officer for a Local Police Dept trying to set up a patrol report sheet, I need to have a return of "Mids", "Days", "Swings" to be displayed in the cell next to the one labeled "Shift" depending on the time of day. IE: 12am-8am = "MIDS", 8am-4pm = "DAYS" 4pm-12pm = "MIDS".
View 10 Replies View RelatedI want to be able to see the average rating for each employee.. Is there a way to put in the rating from one survey and have it automatically add it another cell and then have it clear the first cell?
Here is an example of what I would like to do: Say we get 2 surveys with one rating of 5 and one rating of 10... If I input the 5 into cell Q4 and have it automatically put into cell R4 and then it gets deleted from Q4 but saved to R4... and then I can add the rating of 10 to Q4 and it automatically ads it to R4 and clears Q4 and totals R4 to an average of 7.5.
This way, with each new survey I can just input each rating to Q4 and have it cleared for the next rating and then have an average rating of all the surveys in R4... we do not need to keep track of how many ratings we are getting, just the average rating.
I have attached the excel sheet if you would like to take a look at what I am trying to do..
I get a text file daily for routed deliveries containing a barcode, name, address and phone number that I convert into csv and then upload it into a route optimization software. after optimization is complete it has assigned packages to drivers and I export it to excel and print. My sorters are having a difficult time reading the addresses on the packages and assigning them to the proper driver, so I am trying to come up with a way.
My idea is that I can import the barcode as a custom field into the route optimizer and then when I export it, that barcode field will still be associated to that order. I want to scan the barcodes of the package into column C which will reference all of the barcodes in B and when it finds the match, it will return the value in A, the driver assigned to that particular package. This way my sorter will just have to scan the packages and throw it into the proper bag for the driver instead of having to visually scan the printed manifest to match.
I'm trying to work out the way of having a cell give a result based on what has been input into two other cells.
Basically I'm doing up a risk management spreadsheet. I need to have in one cell the LIKELIHOOD of an incident, in the second cell, the CONSEQUENCES of it happening and ending up with a RISK MANAGEMENT ASSESSMENT in the third cell.
I want to tie the values of some cells on Sheet 2 to what's put in into corresponding cells on Sheet 1. Example
If Cell B3 on Sheet 1 has the string "B off" put in, Sheet 2 should automatically get "R1" in cell B12, "B off" in cell C12 and "ID123456" in cell D12. If cell B3 on Sheet 1 is empty, B12:D12 should be blank.
I have two coloumns on a worksheet. In the first column I have data validation drop down list. I want to be able to: if the first column "Other" is chosen then the next cell will allow text for description, if anything other is chosen in the first drop down list cell then I want the next ( text for descritption ) cell to be resticted.
View 4 Replies View RelatedI have Quote Master.xls open
I have a value in AA1 that carries a number
I want to open Quote Log.xls
I want to find the cell in Column A (Quote Log.xls) that AA1 (Quote Master.xls) directly corresponds to
I want to offset from that found cell 3 cells to the right and insert from T7 (Quote Master.xls) into this offset cell. Here is the code so far
Private Sub InputIntoQuoteLog7_Click()
Dim CostSheetBook As Workbook
Dim QuoteLogBook As Workbook
Set CostSheetBook = Workbooks("Quote Master.xls")
Set RFQQNumber = CostSheetBook. Sheets("RFQ").Range("AA1")
Set RFQQStartDate = CostSheetBook.Sheets("RFQ").Range("T7")
Workbooks.Open ("\ACT3ENGVAULT EngineersLsheriffDocuments (2008)Quote System MashQuoteLog.xls")
Set QuoteLogBook = Workbooks("QuoteLog.xls")
Set vOurResult = .Find(What:=RFQQNumber, After:=[A1], _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3)
vOurResult.Value = RFQQStartDate.Value
End Sub
I get an un-numbered error on the .Find
how to take the result of a formula calculated in one cell and show the results in an other cell
View 4 Replies View RelatedJust yesterday when I opened my excel file, all the cells that contains formulas does not display the results (leaving the cell blank).
For example the formula for cell A1 is =B5. But after putting a value in cell B5, cell A1 is still blank.
Is it possible to combine the results from 1 Text Box and 2 Combo Boxes to return to 1 Cell?
For example, i am using this code to write username to Sheet 2
Sheet2.[c9].Value = txtFullName.Value
and this code to write account number to Sheet 2
Sheet2.[d8].Value = txtAccountNumber.Value
BUT - i would really like to take the Value from txtFullName and take the Vaule from txtAccountNumber and combine that into (1 cell only) with space separators please.
Example : (username & account number combined)
Slim 12345678
I have a list of data entries. I use auto-filter, to view them according to which category they belongs to. However, when I wanted to sum just the quantity associated with the criteria which I am filtering, I couldnt find a formula which does that
View 2 Replies View RelatedAs I type in text into Excel often I see number signs instead of the text. Baffling - I have tried switching the format to wrap etc - nothing works.
Can anyone help - See attached file. The short solution is to retype the text - but this occurs alot and I am not sure why. Really appreciate your help.
I am trying to work out how I can make an input box pop-up every time a cell in between the range of AU12:AU3000 is clicked on. The user will need to input two numbers, the lowest number needs to be copied into column BG (in the same row).
View 4 Replies View RelatedIf any text or number is added to a specific cell then a letter "P" is entered into another predefined cell.
Example:
Cell A1 is normally empty but if a number or text is added to cell B1 then a letter "P" is automatically placed into cell A1.
I would like to auto populate a cell (A2) due to the input results from another (A1).
For example, if I enter A1 with "100%" then I want A2 to auto populate with "Complete". And just the same for other percentages; if A1 was input with any range from 1% to 99% it would populate A2 with "In Progress". And if A1 was to equal 0% then A2 would be "Not Started".
my spreadsheet to return the word unit if cell a18=1 or the word units if its over 1 but i also want the cell to remain empty if there is nothing input into cell a18 is this possible