Cells With Options In Same Sheet?
Nov 1, 2013
I have a table with a list of names of some products and their substances, firm, package and price.What I need to do is this. When I type a certain name in another sheet, I want it to show me a cell with the substances, a cell with the firm (that are fixed and cannot change them) and then in the next cell to give me options (if I type "package 1kg" to show me the price of this package, if I type "package 0,5kg" the price of this one, etc.).
View 6 Replies
ADVERTISEMENT
Feb 24, 2011
I am using Excel 2010 and I have a password protected workbook with password protected sheets that uses several macros. Most of them, in order to run, have to un-protect the sheet and then re-protect it again. This has been accomplished easily enough by adding ActiveSheet.Unprotect Password:= "mypassword" and ActiveSheet.Protect Password:= "mypassword" to the appropriate places in the script. All of my macros, which do various things like sorting and moving data, deleting blank rows, displaying dialog boxes containing warning messages etc. run fine.
My problem is this: when I password protect the sheets manually, I have checked the following options in the "Protect Sheet" dialog box. Under "Allow users of this worksheet to" I have checked 1)Select unlocked cells and 2)Format cells. After entering my password and closing the dialog box my sheet is protected, but I can edit cells in the manner my allowances permit. However, once I run any of the macros that un-protect and re-protect the sheet, I remain able to select and edit unlocked cells (practically, for my purposes, this means that I can input data which will appear in the default font size and color of the sheet) but I cannot format cells (which, practically, for my purposes would allow me to occasionally change the font color and size of the data). Naturally, after running a macro, the other cell-formatting options are unavailable to me as well. Is there any way to get my manual selections to remain in place after running a macro that functions as mine do? Or is there any way to make my manual selections the default settings for a protected sheet?
View 4 Replies
View Related
Apr 4, 2014
I have a report that works fine, but I'm trying to simplify its usage. My challenge is that I want to show the filter options from the "SCORE Data" sheet (in cells B2 and C2) on the "SCORE Output" sheet. I attached a sample spreadsheet with explanations, for illustration.
Broker Scorecard_04-04-2014_SAMPLE_v1.xlsm
View 4 Replies
View Related
Apr 20, 2009
I've been breaking my head over this and i haven't managed to make this work. The attached excel file contains a list of students that participated in exchange programs on the past. What i want this to do is that when i choose the university the student went on exchange with, that the city and country cell fill in automatiocally with their respectuve info. I have a worksheet on excel that has the university name, on the next cell the city, and on the next cell the country.
View 3 Replies
View Related
Dec 24, 2013
I want to do a loop where you can copy say A3 worksheet 1 then add another sheet naming the work sheet "A3" then copying A3 worksheet 1 to A1 "A3". After that looping to A4 to a new work sheet naming the work sheet "A4"copying the value to A1 "A4", etc...
Is there a simply way of doing this loop? I can probably fit my other coding into the structure.
View 4 Replies
View Related
Feb 12, 2014
Need to autohide cells in sheet 2 and 3 based on sheet 1 cells that are hidden. (Example if i hide 150 switchgear on SOV tab it will hide 150 switchgear in Customer and Foreman sheets as well).
View 1 Replies
View Related
Jul 11, 2013
I have a sheet (sheet1) of around 900 rows with data in columns A-K. Column B contains a unique value.
Sheet2 contains data which needs to be highlighted in sheet1 and each row in identified by a unique value in Column B.
sheet1.PNG
sheet2.png
In sheet 1, I only want to highlight the cell which exists in sheet 2 for the matching reference number in column B. For example, in sheet2, the value 'PM328491CN' exists and the value 'CN' is present in cell c2. I want the cell that contains this value in sheet 1 to be highlighted (cell C6).
So, find the matching reference, the highlight the cell in the row which is present in both sheets.
View 1 Replies
View Related
Apr 18, 2009
I want to be able to copy a name from one sheet (Available Players), paste it to a cell in another sheet (Round 1 through Round 20). The cell that will be copied is fixed but the place where it will be pasted will be different and may be on a different sheet.
also i would like to change the color of the copied cell to "greyed" out or cut if it can not be greyed out. I have created a button and put in a macro that i created but have been having problems with it, generic 1004 errors that i can not figure out. i am attaching the document.
View 8 Replies
View Related
Feb 18, 2014
I have a workbook that uses the values that a user had entered into 3 cells to calculate multiple other charts/diagrams on multiple sheets within the workbook. Each sheet would show what the user had entered in the 3 cells to allow them to see what is being used to calculate each table. Is it possible to link these cells so that the user can change the 3 values without having to go back to where he originally entered the 3 values?
For example, a user has entered in 3 values in Sheet 1. A formula in Sheet 2 displays what is entered by the user and uses these calls in Sheet 2 for calculations. When the user wants to change the three values, he would have to navigate to Sheet 1 and enter in the new values to have the workbook recalculate all the tables. Is there a way to link the three cells from Sheet 1 and Sheet 2 so when the user is on Sheet 2, he has the opportunity to change the values on the current Sheet without having to navigate to Sheet 1 to do so?
View 1 Replies
View Related
Jun 11, 2014
I am trying to write a formula with the following:
-if I9=0, then write 0
-if I9=501, then write 1
-if I9=502, then write 2
What would be the best way to write this formula? I am sure this should be pretty basic, but everything I try comes up with an error.
View 10 Replies
View Related
Jul 7, 2007
i am currently designing a userform which has a drop-down box for user to choose from the options given. However, i would like to have in the box fields such as "--Subjects", "English", "Maths", "Science". In this way, the user will be shown a list of options of the different subjects for them to choose from as well as to clearly identify to them that these are the subjects available therefore the heading "--Subjects". I would also have to make sure that the user can only select "English", "Maths", "Science" and NOT "--Subjects".
View 11 Replies
View Related
Oct 12, 2009
We have developed an Excel workbook with hundreds of lines of VBA code written for a specific application, and we would like to protect it.
Could someone give me an idea of what security options we have over and above the below (if any)?
We have protected all sheets and the workbook itself with passwords. The VBA itself is protected with a passoword.
My understanding is that VBA can be hacked into quite easily.....is there any way of adding another layer of security/protection without having to move to another platform altogether?
View 6 Replies
View Related
Nov 26, 2008
numbers in one column need to be rounded to the nearest half decimal, with next conditions(ill took number 704,00 for example):
- if last two decimals are < 0,25 then my number has to be 704.00
- if last two decimals are >0,25<0,75 then my number has to be 704.50
- and finally if last two decimals are >0,75 then my number has to be 705.00
I tried with IF, CEILING, INT and ROUND functions but i didn't made it work with three options, that i need. I only made it work if i use only two options.
View 5 Replies
View Related
May 4, 2007
I would like to know how do I go about adding the following:
This is a test it counts out of 1250 points
But there are 3 fields which can be omitted from the grand total of 1250
Some students might not have all three fields. The fields totals are 30, 90 and 130. The students might have one or two of them in either order. Now what I would like to do is have 3 different fields where I can mark with a Y=Yes and N=No in a block. then it would change the Grand Total score accordingly.
View 9 Replies
View Related
Sep 5, 2008
How do I filter the options in column A to bring up results in Column B. I want to do a one-many filter where if the user selects an option in column A then the results/values in column B should be based upon the the user selection.
View 9 Replies
View Related
Sep 19, 2008
I recently added a macro in excel that stopped users from cutting and pasting and dragging etc. I got the macro from the web. I have delete the macro, but for some reason I cannot get it back to normal. If I right click on a cell the cut, copy, insert, delete, format cell menu is grayed out. The undo button also does not work? I have searched for the macro and deleted it but it seems to have changed something else. I have even tried to disable macros, but all menu items are still gone?
View 9 Replies
View Related
Jun 27, 2006
I use several different workbooks during the day, and sometimes I would prefer the " move selection after enter direction" option to = "Right" and sometimes "Down". It doesn't seem to be an option that is saved with the workbook.
what could be done to a workbooks code to change this option upon opening the book?
View 2 Replies
View Related
Aug 17, 2006
I have a userform that prints out pages using a autofilter, the code in the userform prints out 13 different criterias from the autofilter. But the problem is i have no control over what criteria i can print out. Theres the
ActiveSheet. Unprotect ("seasons")
Sheets("Day Handover").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Activities Pick Sheet").Select
Selection.AutoFilter Field:=1, Criteria1:="1"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="2"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="3"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="4"......................
So i have design another userform that will able me to control what criteria can print out using checkboxes. But i don't know how to code the new userform so when i select a checkbox it removes that criteria from the code in the first userform called 'frmPrintOptions' and when deselect the checkbox it puts the criteria back in the code. Also the Second userform called 'frmAutoPrint' must remember what checkboxes have been selected and deselected.
View 5 Replies
View Related
Nov 25, 2006
I am currently having trouble getting =IF forumla's to work, with more than 3 options AND an equation being taken into account. Attached, is an example of a spreadsheet, involving what I currently have, with Row 5 showing the formula's I've used, and rows 2-4 showing examples of 'Yes', 'No' and 'Equal' outcomes.
For Column E, I would like the value from the above cell (set to zero in the formula of the first cell) to:
-Go up 1 digit (from the above cell's value) for a Yes in that rows C cell
-Go down 1 digit (from the above cells value) for a No in that rows C cell
-Go up 2 digit (from the above cell's value) for a Equal in that rows C cell
As can also be seen from my example, I only have it working so that 'Yes' in C will take the value up by 1, and anything else (including 'No' and 'Equal') going down a point, which is only really using 2 options. I have tried numerous different possibilities for trying to get this to work, all of which gave me #VALUE error, or wouldn't even let me accept the formula. My current formula for what I have now (with only 2 options) is
=If(C5="Yes",E4+1, E1-1)
Basically, if possible, I would just like to make the E column change from 2 different ways (e.g +1 and -1) to 3+ different ways (e.g +1, +2 and -1).
View 3 Replies
View Related
Feb 9, 2007
I have a spreadsheet that I access over a citrix network, when I opened it at the start it gave me an option to enable or disable refresh external data, also enable or disable macros both of these are gone now and I have searched the net to find a way to get them back.
View 2 Replies
View Related
Jan 28, 2014
At the moment I have to click in the cell and then look at the cells used and look across to the title of the rows.
So for example, performance = D3*D4*D5*D6
I would like, performance = vehicles*availabliity*utilisation*TKM.
That is easy if I have just 1 option. But what if I have 3 options? Naming each cell would be a way to do it but pretty laborious, is there a 'smart' way to use named ranges here?
View 3 Replies
View Related
Feb 10, 2009
I have created a drop down list, but when selected the text is really small. I know this is because I am zoomed out, but if there any way for the text to be larger so that I can zoom out and still read what the options for the dropdown list are?
View 4 Replies
View Related
Nov 10, 2008
For my school project, I must create a spreadsheet with lists. I am not allowed to have the data from the lists displayed anywhere on the sheet except for the results which will be hidden by the list (Combo box) in question. I have 3 such combo boxes and I need the options inside them to be hard coded into the spreadsheet. How do I do this?
View 4 Replies
View Related
Apr 18, 2009
I have a workbook (VBE.xls) that creates menu options in the VBE when it opens.
Several of them modify the code in the active codepane.
When the codepane is in VBE.xls, everything gets reset and my macros don't run anymore. I have to re-run the macro (menu_setup()) that creates the menu options.
I don't think there is anything I can do about it resetting when I modify the project that created the menus.
Is there some way to have each macro re-run menu_setup() AFTER it has ended (after the reset has cleared everything)?
I've tried having each macro open the immediate window and paste in "menu_setup" then tried to use "sendkeys" command to execute it, but I can't seem to get it work.
View 5 Replies
View Related
Dec 6, 2009
Having great success at using Countif and Sumif in totaling values across two sheets, but at the risk of sounding like a dummy I would like a suggestion as to which formula I should be using for this basic little function;
I have a column on a sheet with dropdowns offering 4 options; Active, Cancelled, Completed, and Suspended. I'm using Countif to total the number of "Active" but I also need to total the number of "Canceled", "Completed", and "Suspended" under the one heading, as if they all meant the same thing.
View 6 Replies
View Related
Jan 31, 2010
I would really like to know if there is a way that when my workbook opens if the following can happen?
- Not display the tabs (NOT "HIDE" the sheets, just not be able to view the tabs themselves)
- Go to full screen (not display ribbon)
View 6 Replies
View Related
May 4, 2012
I know I can figure out the number of possible criteria using FACT(#) if it is just a number. But what if i want to figure out the number of passwords that can be used if the password must be 6 characters and have at least 1 number and at least 1 letter (so it could be any combination of up to 5 letters and 1 number, or 5 numbers and 1 letter). To make it more complicated, passwords are case sensitive. Just wondering about this since whenever I have to reset a password I always wonder how many possible passwords there are to choose from.
View 9 Replies
View Related
Jan 22, 2013
i have 2 columns i want a formula that will test both cells at the same time for different possibilities:
First Column Second Column
IN Place 1
Not In Place -1
Part In Place 0
I need to check for all these possibilities and return a grade for it
View 9 Replies
View Related
Apr 10, 2014
I followed a youtube video on setting up a survey in excel, and it works great, but it was only for two options. I wanted to make it 5 options per question, but when I started adding to the code ( which I thought was correct, but was not ) I kept getting errors. It probably is a simple addition. How to make the survey options more than two?
There are three tabs. The first tab (Start) only has a button that leads to the QA Survey. The Second tab has the questions and answers. The third tab as three columns in it as well for the name of the person, question number, and answer choice.
Here is the code inside the form (QASurvey):
Code:
Private Sub button_next_Click()
''confirm there is a name
If TextBox1.Value = "" Then
MsgBox ("Please enter your name")
[Code] ...
Here is the code in Module 1:
Code:
''global variables
Public info() As Variant
Public results() As Variant
Public questionnumber As Integer
Here is the code on the sheet that has the button to start the Survey Form:
Code:
Private Sub Start_Button_Click()
QASurvey.Show
End Sub
View 5 Replies
View Related
Dec 13, 2006
I have a custom command bar & and I have several buttons in it.
I only want some buttons availble when a certain worksheet is active.
What code would I use to only make certain button available.
ie if Budget worksheet was active then the botton below would be active?
But if Overages worksheet was active the button would be inactive?
Private Sub AddMenuToCommandBar(cb As CommandBar, blnBeginGroup As Boolean)
Dim m As CommandBarPopup, mi As CommandBarButton
If cb Is Nothing Then Exit Sub
Set m = cb.Controls.Add(msoControlPopup, , , , True)
With m
.BeginGroup = blnBeginGroup
.Caption = "Page Go"
.TooltipText = "Page Select"
End With
View 9 Replies
View Related
