Greyed-out Columns Based On Cell From Specific Column In Active Row Being Edited
Jul 7, 2014
I am making a spreadsheet in which I have been asked to have certain column greyed-out based on an answer that was given in a dropdown menu. Specifically, a dropdown menu in column D asks if the client was a Youth or an Adult. Depending on what the anwer is, other columns in the spreadsheet will be greyed-out; different ones for each answer. My problem is, I need this to occur for the active row being editted. For example: The first row that the user can input data into is row 4. If D4 is answered with "Adult" then certain other columns are greyed-out. However, if the next time the user updates the spreadsheet with information for a different client (now entering information in row 5) and they select "Youth" for column D5, then different columns get greyed-out. The columns that are greyed-out will depend upon the answer for the dropdown menu in column D for whatever row is actively being editted.
View 5 Replies
ADVERTISEMENT
Dec 2, 2009
I have a spreadsheet with my Periods along row 10. e.g. C10: "1", D10: "2", E10 "3", F10: "4", G10: "5" etc. (green on the attached sheet). I have my departments along column B, e.g. B11: "Baked" B12: "Fresh" B13: "Frozen" (yellow on the attached sheet)
what I need and cannot work out is some VBA code that will populate two variables (lets call them Period & Department) when I click on one of the figures. For example if I click on cell: if I click E14: Period would have the contents of cell E10, and Department the contents of cell B14.
if i click G14: Period would have the contents of G10, and Department the contents of cell B14 again. I know how to get the click on the cell to work properly etc, and I have code to slot these variables into that works very nicely, I just can't get this bit to work!!!!
View 2 Replies
View Related
Aug 7, 2008
I have columns in my spreadsheet that will be getting updated periodically with a number. 1 week it might be 24, the next it might be 26.... and they would go in January's price, February's price....
The problem is, if a price is entered into the column, we don't know when it was entered other than the fact that it was entered in that month.
Is there a way to show the date and time of when a number gets entered or edited in a cell? This date/time could perhaps show up in a column right next to it.
View 10 Replies
View Related
May 16, 2013
How to find the column based on my active cell...if my active cell is in A1 then it pops up saying your in column A.
View 3 Replies
View Related
Jun 22, 2006
I am attempting to write an Excel Macro that would allow me to Click on a particular row and have the fields in my form fill with the data in the columns of the row.
I am fairly new to Macros and VBA, but know that this has got to be a possibility.
View 9 Replies
View Related
Feb 25, 2014
I work on an excel spreadsheet all day and I'm constantly cuting and pasting a value to filter another column. I would love to have a macro button that would automatically do this.
The job card sheets are labled 'Page 1' through to 'Page 175'. As I am working on each job card sheet I need to filter column on another sheet within the same workbook. This is indicated on the attached picture.
So in a perfect world as I would enter in the PLANT ID number on to any job card, then hit a macro button on the toolbar and the Pole No column would then be filtered by the value in the PLANT ID cell.
As there are multiple job cards the macro would probably need to use the 'active cell' value to filter by. But as you can probably tell I'm no expert so you judge the best way to do it.
MWTS034G22 Job Card Sheet
[URL] .....
Windows 7 (32bit)
MS EXCEL 2010
View 2 Replies
View Related
Oct 8, 2013
I need a macro that will select row 15 in the active column. (basically returns to the top of the data where rows 1-14 are frozen in place, only in the current column).
I have been able to accomplish the opposite (skip to specific column while maintaining active row) by using the code below:
Range("V" & (ActiveCell.Row)).Select
But it does not work when I try the reverse:
Range((ActiveCell.Column) & "15").Select
View 3 Replies
View Related
Jun 12, 2007
I'm trying create a macro to enter a series of forumula's in a series of rows in whatever column is currently selected (or column which has a cell selected). IE if the active cell is C5 I want "=A1+B1" copied to C10 of it was AA43 selected I'd want "=A1+B1" copied to AA10. Have done this with setting a row as a variable, but whenever I've defined the column as one it comes out as a numeric value. and gives me "method range of object global failed"
View 3 Replies
View Related
May 5, 2006
I have a cell that I am needing to allow users to edit the data but not the format.
The users edit the data by copying & pasting from their own personal spreadsheet. The source spreadsheet formatting can be varied. Is there a way to allow them to be able to edit the data inserted but not the format?
View 3 Replies
View Related
Oct 29, 2007
how do I restrict editing to certain columns/Rows and allow some to be edited by users?
View 2 Replies
View Related
Jul 30, 2014
I have a spreadsheet with four columns of text.
In column A, i have multiple levels followed by a letter (i.e. Level 1A, Level 1B etc).
In column B, i have some other details and then so on and so forth.
In column C/E/G lets say, i want to copy the information from column A to show only items that appear as "Level 1" (not "Level 1A", i only want it to check for things without the letter at the end). Then the same in column E but with "Level 2" and so on and so forth.
Column A...Column B-Column C...Column D--Column E...Column F--Column G...Column H
Level 1A....Metals----Level 1A....Metals ---Level 2A....Integral---Level 3A....Television
Level 1B....Energy----Level 1B....Energy--- Level 2B....Flowers---Level 3B....Kitchen
Level 1C....Synergy---Level 1C...Synergy--Level 2C....Full
Level 2A....Integral---Level 1D....Orders
Level 2B....Flowers
Level 1D....Orders
Level 3A....Television
Level 3B....Kitchen
Level 2C....Full
I also have data in Column B that is to do with column A (i.e "Level 1A" - "Metals") and so on with the following columns. I want the items that are in column B to also move over to column D when the things from Column A move to Column C, so at the end it will appear as below so it appears as above.
View 1 Replies
View Related
May 20, 2008
I've searched for a solution on the board and the internet too but to no avail; I am trying to create a macro that will select the entire column based on a cell reference G1 which is infact a Date.
Column G to Column S are pre-defined with a specific Date in row 8. If the Date in one of these columns (G8 to S8) match with the Date in cell G1, say it finds a match with H8, then I want to copy the range F14:F2000 and paste as values in range H14:H2000. The possibility of a match found is limited to a single columns from G to S.
View 9 Replies
View Related
Jul 2, 2014
I have a form that loads and depending on the word selected in the drop down the following code loops through cells Q2:AC2 until it finds the word in one of those cells (the word will always be in one of the cells)
Code:
For Each c In Range("Q2:AC2").Cells
If c = period Then
c.Select
[Code]...
The active cell it finds will always change, i know I need something to code the active cell back but I don't know what it should be.
View 5 Replies
View Related
Jun 4, 2014
Once an individual selects "NO" in column "O" I would like the rest of the columns to be grayed out so the no other information can be entered except for the last column for notation purposes.
View 3 Replies
View Related
Feb 9, 2013
In long worksheets where I have to edit each cell, I would like it if the cell that is being edited is always on top of the screen. That way I wouldn't have to scroll as much.
View 1 Replies
View Related
Oct 25, 2013
Is it possible to find the unsaved data in a spreadhseet/workbook? Or the last cell was edited?
After working on multiple workbooks when I try closing them excel confirms if the changes should be saved or not. I was wondering if there was a way to check what those changes were. If there was a way to find address of the cell that was edited last.
View 3 Replies
View Related
Sep 3, 2013
I was handed a pdf of some leads by a co-worker. Finally, I got the data into excel but it's in a pretty useless format... 30,000 rows of contact info with no rhyme or reason. I want to upload this into our CRM, but I need to get all the different bits of info into separate columns. I'm no excel pro, but I managed to use an Excel plugin called ASAP tools to bring some order to this chaos... All names have a blue cell color, all titles have a green cell color, work phone is red, and so on.
Does anyone know a way that I can sort these into separate columns? I've trying playing with the Filter function, but it hides rows which makes the output useless for my purposes.
View 1 Replies
View Related
Sep 11, 2013
I am putting together a procedure to calculate a value based on all of the active cells in column "A" and a couple other conditions. "k" is my variable to put a value into (k,8) until all the active cells in column A have a corresponding value in (k,8)
I have my scenarios all built out into If statements and the code runs....but no value is being added to cell (k,8) for any of the rows with entries in column "A" when the procedure is finished running. Depending on the values in (k,2) and (k,3) there are four different ways the value in (k,8) needs to calculate as I have built out below. Why my values aren't being added to (k,8)?
Code:
Sub CalcSpecialFee()
Dim k As Integer
Dim cell As Range
k = 4
Application.ScreenUpdating = False
Sheets("Summary").Select
[Code] .........
View 1 Replies
View Related
May 27, 2014
I have a log book that gets modified through out the day, and an auto run macro that runs at specific times to save the data to a database type sheet, and clear the form for the next shift. some of the operators are double clicking in the cell so they can type their comments. If they do not hit tab or enter when they are done, and the save time comes, the macro is not running, and I'm not collecting the data that I need. Is there a way to force the macro to run, or to force a tab/enter after 3 minutes of inactivity?
View 4 Replies
View Related
Feb 6, 2008
I have various lists of data (using Data > List > Create List...) that do not always have the first cell in the list in column A. I have spent quite a bit of time searching the internet to try and find a macro that will work the same as... Data > Sort > Sort By: Col xx > Then by: Col xy.
Dave supplied me with a macro that will sort a range by the first column:
Sub SortThis()
With ActiveCell. CurrentRegion
.Sort Key1:=. Cells(1, 1), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub
I have recorded macro that does the same and looked at the code but this uses Range and specifies the cells to be sorted. I have not been able to work out how to modify the code Dave posted to do a minor sort by the second column. I am after a macro that will determine the range (could be a varying number of columns and rows in different places). For however many columns there may be the macro will sort the full width and height by the first column and then the second column, with all the data within a particular row not being split.
View 2 Replies
View Related
Mar 19, 2008
Im writing a macro and have a cell activated (using ActiveCell). If I want to select this cell (the activated one - i.e., k3) along with the 2 cells next to it (i.e., l3, and m3), how would i go about doing that?
I'm wanting to shift 3 cells downward and I know how to shift them down, but dont know how to select the cells i want.
View 5 Replies
View Related
Apr 30, 2008
Continuing with a large Macro I am trying to peice together, I have reached a hurdle where I need to hide all columns to the left of the ActiveCell (which varies week to week) back to column B.
I have the following code which selects the correct number of cells to the left of the active cell but doesn't highlight the whole columns - just 3 cells for each column (very odd)
ActiveCell.Offset(0, -1).Range("A1").Select
Range(Selection, "B" & Selection.Column).Select
I'm sure it must be something to do with the
Selection.Column
part as when I write
Selection.Row
it just does 1 cell for each column (as to be expected)
I should mention I already have the coding to hide the columns, just need a way to highlight the relevant ones.
View 3 Replies
View Related
Apr 24, 2014
Trying to get a record of who authorized spending in one of my worksheets and when they did it
I had some VBA code in one of my worksheets which added a time stamp and a user ID to two different cells after the user enters their name. Why it is no longer working, though I suspect after moving the worksheet around.
I would like my users to type their name into cell G65 (which is actually merged from G65-K65). Once the user enters their name, cell L65 (which is actually merged L65-O65) populates with the current time stamp. Also, when the user enters their name into cell G65, I would like cell P65 (which is actually merged into P65-S65) to auto populate. I would like this all of this to go down through line 70.
View 1 Replies
View Related
Oct 16, 2009
I am making a workbook where the G column is drop-down menus that are populate a drop down on the H column. Whenever someone changes the selection of the G column I was to erase what is in the corresponding H column. I have this code that works, but I need to do this for 100 lines. Is there any way to put this into a loop instead of writing 100 if statements?
View 6 Replies
View Related
Jan 20, 2009
I have number in cell (A1) = 100. when I enter number in any cell of column(B) for example (B1)=10. then in cell (C1) the result of (A1) - (B1) = (90) and if I add in cell (B2)=10 then in cell (C1) the result of (A1) - (B1+B2) = 80 .accumulatively in cell (C1). and any number in column (B), the result will be (A1) minus any number in column(B) accumulated in (C1)
second question
I have number in cell (A1) = 100. when I enter number at cell (B1) = 10 then the result would be in the adjacent cell (C1) = (A1) - ( B1) = 90 and If I enter a new number in cell (B2) = 10 then the result would be in the adjacent cell (C2) = (A1) - (B1+B2) = 80 and If I enter a new number in cell (B3) = 10 then the result would be in the adjacent cell (C3) = (A1) - (B1+B2+B3) = 70 and so on. I want the result to be add automatically to adjacent cell in column (C)
View 3 Replies
View Related
Dec 17, 2009
I have this model I created where I have two tabs. One tab is an input tab using validation and drop down menus and the other is a display tab. I simply want to hide certain QTRs based on the value of one of the drop down menu results. I tried writing the VBA code below but am a novice when it comes to code. Can somebody please help me fix the below code so that it works properly.
Public Sub hide()
If Worksheets("input").Range("b14") = "Q1" Then
Worksheets("Group P&L").Columns(c, d, e, h, i, j, m, n, o, r, s, t, w, x, y).Hidden = True
ElseIf Worksheets("input").Range("b14") = "Q2" Then
Worksheets("Group P&L").Columns(c, d, h, i, m, n, r, s, w, x).Hidden = True
ElseIf Worksheets("input").Range("b14") = "Q3" Then
Worksheets("Group P&L").Columns(c, h, m, r, w).Hidden = True
ElseIf Worksheets("input").Range("b14") = "Q4" Then
Worksheets("Group P&L").Columns.Hidden = False
End If
End Sub
View 9 Replies
View Related
Jan 28, 2010
=(SUMIF($J:$J, "Player1",$K:$K)+SUMIF($L:$L,"Player1",$M:$M)+SUMIF($N:$N,"Player1",$O:$O))-SUM($I:$I)
This looks up if Player 1 was in the top 3 scoring zones and adds up the players total score.
I have another column in this table of data labelled 'Number of Players'. I was wondering how to lookup the total score for this player in the games he played under 6 players for example and the games he played in where there were other six players.
I've tried using that formula ^^ with variations of IF and SUMIF formulas to try and figure it out, but I keep getting formula errors or it just returns with the total score, and not just with the total score under 6 players.
I am also trying to create a graph of date on the x axis against score to date on the y axis - everytime I try, the x axis is always numbers instead of the date?
View 8 Replies
View Related
Jul 16, 2009
I would like to start off in col A, do an offset until a certain value is reached:
View 2 Replies
View Related
Apr 7, 2009
I have a macro where I sum a large number of cells in column AZ. How can I have the Macro end in the last cell of column AZ where the sum is located? The length varies in each file.
View 3 Replies
View Related
Mar 25, 2014
I have been trying to find a way of formatting an entire row based on the contents of cells in each column. However i come unstuck when trying to make the column dynamic. Below is an example:
Month
2010
2011
2012
[Code]....
The idea is that i say current year is 2011 and all rows where C contains an a will turn green. What i would like to achieve is that when i change current year to say 2012 the conditional formatting adjusts so that it looks at D instead of C.
View 3 Replies
View Related
