Change Cell Value Based On Value In Dropdown Of Same Worksheet

Jul 7, 2014

I am working on excel based tool where I need a small piece of macro. I used two IF loops in the code.

The second IF loop is working fine. The problem is with first IF statement.

What I need to do is -

If E6 is Yes, then in the same worksheet named "Input Sheet", i need to change values of cells D14:M14 to zero and also

I want to restrict people to change this value. So, after changing the value to zero, I am hiding that row.

But if E6 is "No", then -

Unhide the Row 14 and allow users of this tool to change value of cells.

I believe the only issue is with statement - Sheets("Input Sheet").Range("D14:M14").Value = 0

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If [$E$6] = "Yes" Then
Sheets("Input Sheet").Range("D14:M14").Value = 0
Sheets("Input Sheet").Rows("14").Hidden = True

[Code] ..........

View 8 Replies


ADVERTISEMENT

Cell Change Based On Dropdown And Conditional Value

Sep 19, 2013

I have a value in Cell B2, a drop down value in Cell C2 and a conditional value in D2. I will very much like to have Cell A2 change it's value automatically if the correct if and then statements below are met.

Below are my if and then statements for the values I want in Cell A2.

Cell A2: ADD

If Cell B2 has a value of 1 or more and Cell C2 has a text value "No" and Cell D2 has a conditional value that's "Green"
Then change the value of A2 to Add

Cell A2: Delete

If Cell B2 has a value of 1 or more and Cell C2 has a text value of "No" and Cell D2 has a conditional value that's "Red"
then change the value of A2 to Delete

I haven't used If and then statements in a while and the example I gave above is just to get an idea of what I'm looking for to change in my cell.

View 4 Replies View Related

Change Color Of Cell Based On Dropdown Selection

Dec 14, 2012

I have a dynamic drop down list using OFFSET and want to change a selection of cell colors based on the drop down selection. My drop down list is located in cell G4 and the cells I want to change are

=$C$9,$C$10,$C$11,$B$11,$B$12,$C$12,$B$13,$C$13,$C$14,$C$15,$H$15,$H$14,$H$13.

These are random cells. There is no value in the cells, I just want to change the color from green to gray.

View 3 Replies View Related

Autofilter Data Based On Cell Value (dropdown List) In Different Worksheet?

Apr 5, 2013

I've created drop-down lists in cells B3 and B4 (this is in worksheet 1), based on a column in Worksheet 2. I'd like Worksheet 2 to autofilter the data based on the drop-downs in B3 or B4, or both together.

View 2 Replies View Related

Dropdown List Triggers Worksheet Change

Apr 26, 2007

Trying to create a dropdown list to navigate to another worksheet . I tried iterations of the following code but am getting an 'object required' error.

If I can't assign the result to a string, how can I change the worksheet from the result of the dropdown list?

Sub DropDown7_Change()
Dim temp As String
temp = ActiveDocument.FormFields(“DropDown7”).Result
Sheets(temp).Select
End Sub

View 9 Replies View Related

VBA Change Cell Value To Specific Letter Based On Value Of Different Cell In Different Worksheet

Jun 6, 2014

I've done the following:

Sub BrownBH()
If Range("Brown!B4:B31") = X Then
Range("C4").Value = [#A]
Else
Range("C4").Value = NT
End If
End Sub

However, this doesn't even work.

When somebody enters an X in a specific cell on one worksheet, it's supposed to change the value to A of a specific cell in a different worksheet. Sounds simple enough...but...

View 5 Replies View Related

Hide Columns Based On Date Value In Cell (Worksheet Change Event)

May 23, 2014

Hide Columns based on Date value in cell (Worksheet Change event)Looking for a worksheet_change event macro that will automatically hide columns based on a value in cell S3. S3 is a data validation pick list of months based on the 1st day of each (e.g. 1/1/2014, 2/1/2014, 3/1/2014, etc.). I have date columns in row 6 from columns T through AQ. These values are 1/1/2014 (T6), 2/1/2104 (U6), 3/1/2014 (V6)....12/1/2015 (AQ6). I would like to hide columns that have a date in row 6 (T6:AQ6) that is LESS THAN (<) the date in S3 after the cell is value is changed.

View 2 Replies View Related

Change Sign Of Number Based On Validation Dropdown In Another Column

Feb 21, 2014

This spreadsheet will be tracking sales and purchases. I want to be able to have any purchases formatted as an expenditure (negative sign or brackets) to show it is money out. Column C "Action" has a drop down with either Buy (infrequent) or Sell. I want to be able to have column I "Unit Price" be a negative number if the corresponding cell in the same row in Column C = "Buy". I do not have these prices formatted as currency as their are 2 different currency used and I don't know how to make the correct currency format appear automatically, though I do have a drop down validation column for which currency the transaction was in, so maybe that is possible. I know this is probably simple, but I don't spend that much time using Excel. I have some sample data in their for the moment.

View 2 Replies View Related

Change Button Function Based On Dropdown Menu Selection?

Jun 9, 2014

I've got a drop-down menu with four options in it that match up with the cells D11:D14, and next to those I've got spots for counting the totals (E11:E14). Next to the drop-down I've got plus and minus buttons, and I need to make it so if I have the top option selected in the dropdown and press plus, it adds +1 to E11. Second option and the same plus button, +1 to E12, and so on.

The purpose is for a call tracker, the employee would select the reason for the call (maybe more added later) and hit the button to add the call to the report.

I've looked so I know you normally ask for what I've got so far, and that's nothing. I've got all the other buttons wired up but I don't even know where to start with this one. I can include a screenshot if necessary.

View 5 Replies View Related

Adjust Worksheet Placement Based On Dropdown Selected

Feb 8, 2012

I have created validated dropdown, which in turn I have used for conditional formatting to highlight specific consecutive rows based on the dropdown.

Is it possible for the worksheet to be positioned based on the dropdown chosen, so that the highlighted rows are visible without having to manually scroll down, looking for the highlighted rows?

If dropdown chosen highlights rows 50-60, I would like the page to automatically show rows 50-60 (not necessarily hide the others, but at least scroll to rows 50-60).

View 1 Replies View Related

Automatically Color Worksheet Tab Based On Dropdown List Selection

Nov 21, 2012

I am new to VB macros, I am trying to figure out how to color a tab Green based on a selection from a drop down list.

View 9 Replies View Related

Copy Named Range Data To Worksheet Based On Dropdown Or Combobox

Feb 10, 2013

I have a worksheet called "Lookup" with several dynamic named ranges (each is 1 column wide) including facility, department, shift, etc. On a summary worksheet in the same workbook I want users to use a dropdown or combo box (don't care what type) in cell B2 to select a facility and then based on their selection, copy the department named range data and paste it into the summary sheet beginning in cell A5 and paste the shift named range data into the summary sheet beginning in cell B5.

Example: user selects "AR Plant" from the dropdown or combo box and the data from the "AR_Rpt" named range is pasted into cell A5 and the "AR_Shift" named range is pasted into cell B5.

View 9 Replies View Related

Creating Dropdown List To Customize Worksheet Based On Country Selected

Aug 16, 2013

I have a product list where each row of the worksheet has an image, product details and suggested lists/retails for each local currency.

Some products are not available in certain countries and each market doesn't care to see the other market info. (There are hundreds of items and dozens of countries.)

How can I create a drop down selector so that a user can choose their market, i.e. "Australia," and only the rows and columns relevant to them appear?

Can this be done without macros,? This sheet is distributed among 100+ people and needs to work for lowest common denominator.

View 3 Replies View Related

Change Cell Color With Different Dropdown Values

Jun 12, 2013

I would like to have the cell color changed according to what value is chosen from a drop down menu. I guess you need to make some kind of rule with conditional formatting ...

View 14 Replies View Related

Changing Value Of Cell By Change Of Value In Another Cell Using Dropdown List

Jun 7, 2013

I have a spreadsheet with numerical data in B1:B11 and textual data in C1:C11. The numerical data is a couple of digits, nothing big, and the textual data is always two letters, in this case AB, AC or AD. I have the options for AB, AC or AD for each of the cells in C1:C11 in a data validation drop down list. What I'm trying to do is make a VBA macro, of some sort, that acts upon when, AB for instance, is changed to AC, and then multiply the adjacent numerical value in the B column by a conversion value:

AB -> AC = 3
AC -> AD = 7
AD -> AB = 4

So for example, if I had cell B2 as 10 and C2 as AB, then changed AB to AC using the drop down menu, the macro would recognise the change, select the adjacent cell, B2, and multiply it by 3 to get 30, and put 30 in B2.

This is what I've got so far, and I'm no VBA expert, so I've probably made mistakes in multiple places. At the moment, the code below is getting stuck on active.celloffset - and I'm really not sure why. I did manage to get it working with specific cells rather than a range, but that only ever worked once and I had to close and re-open excel to get it to work again. Is my code any good, or am I coming at this from the wrong direction?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Code:
Static ov As Variant
For Each Cell In Range("B1:B10")

[Code].....

View 9 Replies View Related

Worksheet Change To Function To Add Text To Cell Which Initially Triggered Change

Jan 10, 2014

i have some existing code which is trigerred when anything is input into column c. The code then adds various information in another three columns. One of which pastes a vlookup formulae, and i would like this forumlae pasted into the column c cell which i initialy edited, in order to remove the requirement for one additional column.

The existing code i have is:

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MyText As String
MyText = Environ("username")
If Target.Cells.Column = 3 Then
With Target
If .Value "" Then
.Offset(0, 2).Formula = "=VLOOKUP(D:D,'P:TAOffshoreTAOffshoreTreasuryRecsGeneralCommit ID''s for control Sheet - Do not move or delete[commit ids - DO NOT DELETE OR MOVE.xls]Sheet1'!$A$1:$B$65536,2,0)"

[code].....

I have tried changing the offset to (0,0) or changing the offset to 'target = ', which does add in the vlookup but then the macro debugs at the 'If .Value "" Then' code?

View 3 Replies View Related

Excel 2007 :: Automatically Change The Worksheet Tab Names With Cell Value In Each Worksheet?

Feb 14, 2012

I am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?

View 3 Replies View Related

Event Change To Change The Sheet Name Based On A Cell Value

Jul 21, 2009

Im trying to use an event change to change the sheet name based on a cell value, but my issue is how can I error trap if the sheet name is a duplicate? Here is what I have so far

Sub ChangeName()
On Error GoTo errhandler
Sheets(1).Name = Sheets(1).range("d10")
Exit Sub
errhandler:
MsgBox "sheet name is already exists"
End Sub

View 9 Replies View Related

How To Change Value Automatically If Change Products From Dropdown List

Jul 11, 2014

I want to change value if i change products from drop down list.....

For more information please find attached file: Book.xlsx‎

View 8 Replies View Related

Hiding Sheets Based On Value In Dropdown Cell

Mar 21, 2007

I have a workbook that has about 30 different sheets with names titled "Joe", "Jane", "Paul", etc.

I have a Cell (B12) that has been formatted as a dropdown menu with about a dozen different options such as "Audit A" , "Audit B" or "Other" etc. What i'd like to do is hide or display certain sheets based on what the value is in cell B12.

if cell B12 says any of the following "Audit A" or "Audit B" then it would hide certain sheets. If the value in B12 says "Other" or something other than "Audit A" or "Audit B" then it wouldn't hide anything at all. I tried searching and couldn't find anything like this although it may have been answered before.

View 14 Replies View Related

Populate Cell Based On A Dropdown Selection Of Another

Jan 25, 2008

I found many examples on this forum of how one can pull in data from closed workbooks, or copy it and have it stored in the active sheet. So far so good, but I need a solution which would do the opposite - I need the macro to take a range of data form the active sheet store it in a closed workbook and save any changes in the target workbook without opening it.

View 2 Replies View Related

Dropdown List Range Based On Two Different Cell Values

Aug 2, 2012

I'm new to excel and was trying to make a workbook that will have a drop down list populate based on a user input in two separate cells. Basically, i have columns: name, pages, and books. I would like to have the drop down list match data by first finding the amount of books read, then finding the amount of pages read. Then any name that meets the criteria will be available in the drop down list. How can I do this?

View 1 Replies View Related

Export Cell Range Based On Dropdown Menu?

Jun 3, 2014

Ok, so I have a sheet with a working drop down list with dates, and depending on the date you choose, certain columns getting hidden and some un hidden. The layout is the same form is repeated 7 times, and which ever date you pick, the corresponding form gets un hidden. My question is, I have a button that will export it to a new sheet with just the values, to be sent to the office. I don't need to send the entire work book and confuse people.

I know how to export, just not using an if then to choose which columns to export.

sample: Attachment 322549

fixed the attachment: added my codes

View 4 Replies View Related

Cell Value Is Dropdown List Based On Other Cell Value Which Is Also Dropdown List?

Aug 4, 2013

fill the column "Level 2 Area" based on the value selected from "Level 1 Area" which is coming from a drop down list. So the "Level 2 Area" will be a drop down list also based on the selected value from the drop down list from "Level 1 Area".

Level 1 Area
Level 2 Area

View 7 Replies View Related

Excel 2007 :: Populate Another Cell Based On Selection In Dropdown List

Mar 7, 2013

[URL]....When I select a name in column B, it populates column C's dropdown with that name's list of units. When I select a unity type in column C, I'd like it to automatically populate column E in the same row with the value associated with that unit. The values associated with a particular unit can be found to the right of the sheet. So if in row 2 I select Blake in column B, then Unit 5 in column C, I'd like column E to be populated with 14.

Edit: using Excel 2007.

I attached stripped down version of the sheet which is small enough to post. None of the things I removed should affect the topic at hand. The full sheet is available at rapidshare.

View 6 Replies View Related

Automatically Select Item From Dropdown List Based On Cell Text

Apr 8, 2013

I have a spreadsheet where user can search for information inside a search box and the appropriate rows are returned using formulas.

I have a drop down list (Category: Model) in the search box as well as a search field (Category: Program, cell D2). Underneath the search box, search rows are returned with column categories: Program, Model, etc.

What I require is that if a user enters the specific program into the search field D2, then the dropdown list would automatically choose which model that program belongs to based on the returned row.

For example, if I typed "engine" into D2 and pressed enter,

Under the records section (Row 14 and beyond), the following record will pop up:

Program
Model
Indicators

Engine
F-16
3a

Based on the record, F-16 would be chosen from the dropdown list. Is there any way this can be done?

View 6 Replies View Related

Autofill Cell Based On Results Of Multiple Dynamic Dropdown Lists?

Aug 25, 2014

how best to do this (bare with me I'm not quite a pro at excel yet!). I'll attach my doc (JMP Excel Forum) so you know what I'm taking about. I've set up the data to work in a similar way to this, as you can see I've a table set up (Appliance Sheet) which feeds information to the Main Sheet which contains the drop down lists.

What I need it to do is autofill the wattage column (Main!F8 onwards) with the wattage for the device as described in Appliance!N3 or N4 for example. For the wattage column to equal 28 (equivalent to Appliance!N3) then the following would show in the dropdown selections Type>Computers SubCategory>Monitors Device>BENQ G2222HDL Status>On

Is there a way to do this with the set up I have?

I did have a previous version of the whole worksheet that each Device had its own table and I looked up the wattage using and If formula to lookup a info on the device and knowing where to look it up based on the subcategory

=IF(ISERROR(VLOOKUP(D7,IF('Main UI'!C7="Heating_Cooling",Appliances!$A$7:$B$14, IF('Main UI'!C7="Kitchen_Bathroom",Appliances!$A$16:$B$27,IF('Main UI'!C7="Monitors",Appliances!$A$52:$B$62,IF(C7="Computers",Appliances!$A$64:$B$68,........

And I got it to work but it was getting to cumbersome to add in new devices and update the formulas. If attached that doc (OLD) so you know what I'm talking about. The last two sheets are the same sheets that make up the new document (JMP Excel Forum)OLD.xlsm

I really prefer the new way I've got it set up. Its easier to add new information or options to the dropdown list

View 2 Replies View Related

Change A Cell In A Worksheet Using VB

Dec 15, 2008

what is wrong with this code. Range("ActiveSheet.!D3") = Me.TextBox9.Value. Basically a new worksheet is created using a form and it renames itself but i also need to change cell "D3".

View 2 Replies View Related

Change Same Cell On Many Worksheet

Oct 17, 2006

Am trying to create some VBA code to select a group of contiguous sheets in a workbook. I have about 80 sheets with the first one named "First" and the last one named "Last" with 80 sheets in between. Because the number and names of the sheets in between will constantly change, I would like to use the first and last sheet name and have all those in between selected without having to hard code the sheet names....

View 6 Replies View Related

Changing Cell Colors For All Matching Textboxes Based On Text From Dropdown Menu

Feb 19, 2014

I have a large workbook, with multiple sheets. I would like to be able to select a name from a drop down menu, and have the cell color change for all entries of that name in my other sheets. I can do it manually, but it would be much cleaner and easier to have Excel do it for me. I have looked at other answers here on this forum and know that if it can be done,

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved