Setup Dependent Cell Based On Contents Of Independent Cell

Oct 20, 2013

I'm trying to set up a dependent cell (B) based on what is in the independent cell (A)

If A is blank, B should be blank

If A has any value other than X, B should display a set string Y

If A contains X, B should contain a drop down list with a range of choices.

I've tried setting it up as a dependent validation cell and I've tried using OFFSET, but the best I can do is to get three different drop down lists to populate cell B. One with only string Y, one with only a blank, and one with the range of choices. The problem is in the first two cases above, I want the cell to fill automatically with either blank or string Y -- I don't want them to just be drop down options, with the cell displaying the last value that was input.

View 2 Replies


ADVERTISEMENT

Cell Contents Dependent On Active Cell?

Nov 9, 2012

I have a list of items in cells b2 to z2. I want to display the contents of those cells in b2 dependent on what the active cell is. For example if the active cell is in column b I want a2 to show b2. If the active cell is in column C I want A2 to show C2, etc.

View 3 Replies View Related

Date Dependent Cell Contents

Jan 19, 2008

Is there any way to make a cell's contents change automatically according to the present date?

View 9 Replies View Related

Generating List Of Combinations With Both Unique / Independent And Dependent Input

Mar 26, 2012

I coordinate access requests for several contracts, and I have to list the approved accesses in a list where each line represents one person and one contract. For each access request, there will be an arbitrary number of persons obtaining access to an arbitrary number of contracts.

The input would then be as follows: Joe A and Jill B request access to contracts 1001, 1002 and 1003 ->

Joe A   1001
Joe A   1002
Joe A   1003
Jill B     1001
Jill B     1002
Jill B     1003

To automate this task, I have made a simple macro for generating a combination list of all persons having obtained access to a selection of contracts.

My macro worked well when I only wanted to list unique and independent list items, but now I have been asked to include each person's email address. How I can change my code so that only one email address is copied into my list for each person?

Code:
Sub AccessList()
Sheets("requests").Select
Dim rng As Range, c As Range
Dim rng1 As Range, c1 As Range
Dim rng2 As Range, c2 As Range

[Code]....

View 1 Replies View Related

Disable/enable A Cell Based On A Condition Which Is Dependent On Other Cell Data

Jan 26, 2007

how can we disable/enable a cell based on a condition which is dependent on other cell data

View 4 Replies View Related

Dynamic Cell Setup (Based On Click To Copy) Error 91

Oct 1, 2013

So here is my code

Code:
Sub Worksheet_SelectionChange(ByVal Target As Range) 'single click version
Range("J3:J32").Interior.Color = RGB(200, 160, 35)
Range("K3:K3").Interior.Color = RGB(200, 160, 35)

If Intersect(Target, Range("J3")) Or Intersect(Target, Range("J4")) Is Nothing Then Exit Sub
ActiveCell.Copy
End Sub

The If statement line gives me a 91 error. Simply right now i'm trying to allocate several squares in the sheet to be cells that when clicked will copy to your clipboard automatically. I can do 1 range easy but now i need to make it a little more advanced.

View 3 Replies View Related

Return Contents Of 1 Cell Based On Contents Of Another Cell?

Mar 12, 2014

I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.

View 3 Replies View Related

Workaround For Public Variable Bug: Creates Different Cell Colors Based Upon The Cell Contents

Oct 15, 2008

I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.

Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.

View 5 Replies View Related

Macro That Will Clear Contents Of Cell Based On Format Of Text In Adjacent Cell

Feb 18, 2009

Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.

View 2 Replies View Related

Automatically Update A Protected Cell Based On Contents Of An Unprotected Cell

Apr 30, 2009

I am trying to find some VBA that will allow protected cells to be updated when a user enters or selects data in other cells.

For example:

Cell 'A1' starts out blank.
Cell 'B1' has a drop down to select specific items.
Cell 'C1' is the date of the order and is a locked cell.
Cell 'D1' is the date of completion and is a locked cell.
The worksheet is protected to prevent direct changes to cells C1 and D1.

If a user enters any information in A1, then C1 displays the current date.
If a user selects 'Complete' from the drop-down list in B1, then D1 displays the current date. Both of these actions are independant of one another. This format is the same for every cell in the 4 columns indicated above. I thought I was able to do this in another spreadsheet I created a year or so ago but I have not been able to figure out what I did and I do not have the spreadsheet to look at.

View 5 Replies View Related

Replace Cell Contents Based On Matching Data In Another Cell (On Another Sheet)?

Apr 28, 2014

I am working with a spreadsheet that has our user data; User(First) Name, User(Last) name, Computer Name, MAC address, Phone Number, Seating Position and Network Port Number.

I have let this get horribly out of sync, moved computers around, and moved them while the MAC address column was hidden, As a result, I have a bunch of users listed as being at the wrong computer. I have a second spreadsheet I generated that gives me the logged in user for about 2/3 of my computers, with the computer name(Just 2 columns, 'A' and 'B'. I would like to have Excel match the computer name and then overwrite the user name. For example, if the first computer in my correct user list is DELL-99945ty2, and the user name is "Jimbo Jones", I would like it to search the first Excel doc(The one with all of the user data), and replace the user name with "Jimbo Jones". Is this simple and straightforward?

In my main spreadsheet, the user name is in column B and the computer name is in column G, So I want to use the values from Column B in my second document to find its match in Column G of the first spreadsheet, and then replace the data in Column B with Column A in the first spreadsheet.

View 5 Replies View Related

Lock / Unlock Excel Cell Based On Contents Of Another Cell?

Sep 12, 2013

How to Lock/Unlock an Excel Cell Based on the Contents of Another Cell?

View 1 Replies View Related

Finding A Cell In A Variable Location Based On The Contents Of The Cell

May 2, 2012

I am creating a macro to automate data analysis for work, but I've become stuck.

I need to find the coordinate of the first cell in a column with word "reserved" or "extended" contained in the cell, and then assign the row number of that coordinate as a variable to use for moving data around. Basically, I want something like VLOOKUP, but instead of returning a value, I want it to return the coordinate.

View 1 Replies View Related

VBA To Clear Contents / Change Value Of A Cell Based On If Another Cell Has A Value And Continue L To R

Mar 9, 2013

I am relatively new to VBA. I am creating an attendance calendar that tracks employees calling sick, late etc.. It is a point based system. What I am looking for is, a way to clear the point value that was manually entered in a specific cell (I3), if there is a Value manually entered in (CU3). Each column in my worksheet is for a specific date ie; I3 is the cell where I enter the points (1.00) for that employee by calling in sick on 3/1/2013, (Column "I" is for 3/1/2013). After 90 days, this point accumulated by the employee does not count against them, so I need that point entered in (I3) to either = 0 or the cell contents to be cleared if there is a value entered in cell (CU3) which is 91 days after, so my (A3) cell does not add that point acquired on 3/1/2013.

I need this to run in a range (I3:I450) so if any value is entered into (CU3:CU450) it has the same result and continue to for (J3:J450) so if any value is entered into (CV3:CV450) and so on..

View 9 Replies View Related

Clear Contents Of Cell Based On Comparsion Of Cell Values

Mar 7, 2007

I am a financial administrator & every month I have down load 4 bank accts as CSV, import into xl & code (CACode) the amounts for our accountant. I have VBA that formats, adds headings & formula etc but I have a problem/s. In H col I place CAcode & I use a sumif formula in I col to sum all the amounts with that have the same CAcode. For simplicity sake I copy the sumif down & then sort H col ascendindingly (this is done by VBA). Now I am trying write a macro to clear the contents of the cell in I col

if eg h60 = h59
then I60 clearContents, Select h59
Else select H59
Do until H3 is selected

Psuedocode
Select table (A2:I Xldown)
Sort Table by CAcode (H Col)
Set Range as H3:Xldown
Select Last cell with CAcode (Xldown) in H col
For every cell in Range (H3:Xldown)
Use If/thenIf Last cell = 2nd Last cell (H Col) then
Clear contents of I col (last row)
select 2nd Last cell (H Col)
Else 2nd Last cell (H Col)Next Cell

I have to use Xldown to select range as the range will be variable each month & for each bank acct. I need to clear contents of cell to verify that all the sub-totals of unique CAcodes of the CAcoded amounts = the totals Because I may have up to 120 rows X 3 bank accts I am slowly using vba to do my work.

Sub sort_And_delete_Sumif_amounts()
Dim r As Range
'Select range to sort
Range("A2:I98").Select
'Sort CAcode in H col ascendingly
Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'select range for comparisomn of CAcode
Set r = Range("H3", Selection.End(xlDown))................

View 4 Replies View Related

Adding Color And A Value To A Cell Based On Contents In Another Cell

Apr 28, 2014

I have values in a number of cells. Next to them, if there was a value, regardless of what the value is, except blank or zero, i would like the cell to turn blue and if possible have a 1 in the cell.

I've used the conditional formatting, however, among the options, 'less than' is the closest to to getting the color right. The issue is that if the cell has nothing in it, it still colors the corresponding cell blue; because it is not less than the already blank cell. If it is blank, I don't want it to color the cell.

View 1 Replies View Related

Dynamic Cell Contents Based On An Other Numeric Cell Value.

Dec 3, 2009

cell A1 is equals to B1 because the cell C1 contains the number 1.
Now if i change the value of the C1 to 2 how can i make the A1 shows the value of the B2 ?

Just like. [A1]=B(C1-content)

View 3 Replies View Related

Populate Value Of Specific Cell Based On Two Other Dependent Validation Lists?

Feb 15, 2014

I want to populate the value of a specific cell based on the choices made from two other dependent validation lists.

I am attaching a workbook that explains what I need to do.

View 9 Replies View Related

Showing The Contents Of A Cell Based On Another Cell

Nov 9, 2005

one cell gives me the MAX value of a range of cells
I want to then use that value to refer back to the range of cells and return the value one column to the left

This formula returns "A4" which is the cell number for this result I want to show but it just enters A4 not the contents of cell A4

="A"&(MATCH(B10,B2:B5,1)+1)

View 9 Replies View Related

Gantt Chart Independent Cell Widths

May 28, 2014

I am developing a Excel gantt chat i have chosen to display the bars by the lenght of the cell because the data is also most real time so as the time reduces so will the bars however the problem comes when there is tasks being done under one above it as i cant have the cells with different widths

What would be the best way to get around it as i want to keep the almost and the appearance of the real time nature of the cell width equal to the value.

View 2 Replies View Related

Get A Cell To Look At A Row Based On Contents

Dec 24, 2008

I want to have a cell that looks at another cell based on a number that is in another cell. So imagine if you will: A1 contains the number 25. A2 contains the formula I can't work out. I want it to look at the row that is the number contained in cell A1, so in this case it would look at row 25.

Now I want to narrow it down further to a cell in that row but this will be a constant so lets say column F. So if A1=25 then A2 would display what ever is in F25. If cell A1 were 16 it would display the contents of F16 etc. The best I got was something like =IF(A1=>1,(F(A1))) but obviously that is wrong as it doesn't work.

View 2 Replies View Related

Add Row Based On Cell Contents

Apr 25, 2006

I want to add a row underneath a row if the value in the in B cell is not = "". Then i want to write a value in the C cell of the new row.

View 2 Replies View Related

Name 1 Cell Based On Contents Of Another Cell

Jun 25, 2009

Step 1 - Find the column named "Date Closed" (it will always be in row 1)

Step 2 - Find the column named "Status" (it will always be in row 1)

Step 3 - If the "Date Closed" column has a date in it, add text to the cell in the "status" column in the same row that says "Closed".

Step 4 - If the "Date Closed" column does NOT have a date in it and is blank, add text to the cell in the "status" column in the same row says "Pending".


NOTE: Cell format in the "Date Closed" column is set this way...
Number tab; Category is "Date", Type is *6/25/2009

Example 1 below, shows how data currently is...

View 6 Replies View Related

Worksheet Name Based On Contents Of Cell

Oct 13, 2013

Is it possible to dynamically chance the name of at a worksheet that already exists based on the contents of a cell on another sheet?

I.e. If the name of a sheet is linked to the value of the Cell A1 in the "Data" sheet and I change A1 to read "Group1" it changes the name of the sheet as well?

View 10 Replies View Related

Contents Of Cells Appear Based On Another Cell

Dec 14, 2013

I am working on a sheet where I want the contents of cells D1:D10 appear if cell A1 has a text in it and the contents of cells D1:D10 disappear if cell A1 is blank. What is the right conditional formatting formula for this.

View 3 Replies View Related

Lock Row Based On Cell Contents

Oct 17, 2008

I wish to be able to lock an entire row after an entry has been made in a certain cell.

e.g. let's say i want to lock row 9 in its' entirety following entry of "Yes" in cell G9. If there is no entry in G9 the the entire row should be still unlocked. Likewise with any row with a corresponding entry in column G.

View 14 Replies View Related

Print Based On Cell Contents

Aug 28, 2009

I have put together a spreadsheet to help with the creation of work orders. I have a summary page that the user can define which category the work order falls under along with this I have a couple of macros set up that will print a specific work order pertaining to the category they selected. I have run in to a few problems (mostly training users) that I would like to eliminate up front.

I am looking for a macro that I can use to look at the contents of a cell (contains a word that corresponds directly to the name of one of the sheets in the work book) and print the worksheet that has the same name as the contents in the referenced cell.

View 3 Replies View Related

Sound Based On A Cell Contents

Jan 11, 2010

I have thousands of items each with a unique barcode(column A) and each is catorgorized either KEEP or DISCARD (column B). What I want to do is scan the barcode (column A), Vlookup and return KEEP or DISCARD (column B). Now based on what the vlookup returns can I have a sound file play for KEEP and a different one for DISCARD.

View 11 Replies View Related

Copy Based On Cell Contents Macro

May 13, 2013

Basically lets say I have in cell A1 to A10 floor access data i.e. Users who used their ID cards to access a room.

Now the data always starts with either Rejected or Admitted then the user's name and then the card number and the access floor etc. the card currently has.

What I am looking for is that the macro should first check if the cell has Rejected or Admitted written in it - this I can do myself using =Left(A1,8) which should give me the helper column I need.

Then based on this I want it to only copy the name of the individual i.e. it should look in the cell and only copy whatever is written between "Rejected" and "(Card". The cell data is something like this:

Rejected Doe, John (Card #123456) at ABC 123 Floor1/Floor2/Floor3/Floor4 Door 1 [In] [Clearance]

View 4 Replies View Related

Break Contents Of A Cell Based On Character?

Dec 18, 2013

We do large loads of new part #'s/descriptions/UPC's/etc from excel into our database. The fields are limited on character count and I would like writing a formula to break the contents of a cell based on character count without breaking a word.

Examples

Column A Current Data
Row1: 12Pt HD Offset Striking Wrench 1-3/8" & 35MM (44 characters)
Row2: 12Pt 1/2" Drv Socket 27MM (25 characters)
Row3: 12Pt Satin Combo Wrench 11/16" (30 characters)
Row4: 12Pt 1/2" Drv Deep Socket 1-1/8" (32 characters)

Results I'm Looking For

Row1: Column B-12Pt HD Offset Striking Wrench Column C- 1-3/8" & 35MM
Row2: Column B-12Pt 1/2" Drv Socket 27MM Column C-No Data Returned
Row3: Column B-12Pt Satin Combo Wrench 11/16" Column C-No Data Returned
Row4: Column B-12Pt 1/2" Drv Deep Socket Column C-1-1/8"

View 8 Replies View Related







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