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 lot of data in three columns like this. The first column is "Name", the second is "Comment" wherein I want the macro to write some comment, the third one is DOB. The problem is that the names in column 1 repeats many times. I want a macro to write in column B "either Old or Older or Oldest" based on the Name and DOB. Thus David with DOB 13 Sep 1982 be marked Oldest in Column B and David with DOB 25 Aug 1988 be marked Older and David with DOB 24 May 1990 be marked Old. Similarly William and Rita should be marked either old or older or oldest. As the data are enormous I do not want to refer the actual name in the programming. I want to call them using a variable in programming.
NameComment DOB
David 25 August 1988
David 13 September 1982
David 24 May 1990
William 24 March 1980
William 25 July 1987
William 13 August 1989
Rita 17 July 1990
Rita 24 April 1989
Rita 13 June 1988
The example file is attached
I want to create a macro that creates a new .xlsx document based on cell values. And input 4 tabs into each document with specified tab names. I have a document that pulls from external sources and fluctuates with amount of data per day. I envision something that will make a new document named for the contents in cell A1, then function as a control+down to create a new document for A2, then A3 until there is no content left (should mention these are lookup formulas, and if there is no data it pulls a value of "0")
Is there a way to put in the code, the tab names it would create in each new document, or would that have to look to a cell value for the naming? The tab names would be "GS", "MYSS", "COLL SHEET", and "WIRE".
I have a macro code for conditional formatting. The first 2 lines of the macro are
Private Sub Worksheet_Change(ByVal Target As Range) ....
I'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 am having trouble creating a macro that renames all excel sheets using cells on Sheet1.
See attached, I want to rename all excel sheets (renamed as "1", "2", "3"....."50") as "9999", "10000", "10001"..."10045", found in Sheet1, renamed as "Base". I will change the cell values everyday (range Base!A5 to Base!50) - and will rename all the sheets again.
Before: before.jpg
After: after.jpg
How to do this:
I have many sheets called;
Input Jan
Input Feb
Input ....
Meaning 1 for each month.
I have another sheet where I want a formula that can
..............................................JUL
CapEx.................................... EBITDA...............................EBITDA %
"=('Input Jul 2014'!$BI$3)/1000"
The "Jul" which stand for July, it is possible to have a formula where I can have certain text in a formula that is based on the value in B1 (In this case, B1 being JUL in bold writing.)
Like this;
=('Input &B1 2014'!$BI$3)/1000
Or must I manually go in and replace all formulas for each month?
i want to update values fon an excel sheet on a month by month basis. So i have jan to dec folders and excel sheets under each folder , i want to update my monthly forecast sheet based on the numbers in the folder I want to just change the cell in my monthly forecast sheet to Jan or Feb and the values should be pulled from the closed excel files in the monthly folders. I tried concatenation but it gave me #REF .. Is there a way to do it other than using "pull" ??
View 1 Replies View RelatedThis is also in sheet1 module ( It cuts the respected row and pastes in sheet2 when date/time is populated in column 14 by double clicking).
[Code]....
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'm creating a workbook to keep track of my utilities payments, one sheet for one utility and so on. I like to copy two cells from each sheet to another one to keep me updated of the amount to pay and the date. an example: column A with text, if text "NEXT" appear in column A, copy the value of two cells (at columns B & C) at the right of "NEXT" to another worksheet, if that possible? Below is a photo as an example:
excel.jpg‎
Currently I have it setup to copy rows to a sheet "Report" based on a single cell value. But now I need the same thing but have it copy the rows based on 2 cells values to sheet "Report". So for example I wanted to copy and paste each row in my workbook that contain values in Columns N:N that contain the value "Test" and in columns AB:AB that contain "1".
Sub copyagain()
Application.ScreenUpdating = False
Dim sh As Worksheet, findThis As String, fAdr As String, fLoc As Range
findThis = "1"
[Code]....
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 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've written a spreadsheet and am wondering if there is a way to permit data entry in a cell, contingent upon another cell being populated.
In simple terms, you can't enter data in cell X, until cell "Y" has been populated with something.
I am attempting to have a user enter a First Name into the first Input Box and a Last Name into the second Input Box.
Within the code, I would like both answers to be returned to cell A1.
For example: A1 would read as Doe, James or James Doe (any variation is fine, as long as its one value in A1).
Sub Name()
y = InputBox("Enter First Name", "Information")
If y = "" Then
MsgBox "You must enter", 16, "Message"
Else
x = InputBox("Enter Last Name", "Information")
If x = "" Then
MsgBox "You must enter", 16, "Message"
Else
Range("A1") = y & & x
End If
End If
End Sub
I need some code to use text boxes to change the data in certain cells. Basically I want 3 text boxes to appear one after another. The first box should add text to cell T1, then 2nd to cell T2 and the 3rd will add text to cell T3. All these values should be added at numbers.
View 2 Replies View RelatedI am using data validation to restrict the number of Characters a user can input in a range of cells. The number of characters in this however can vary based on which selection they selected in a different cell.
To put this as an example, if the user selects "hello" in cell a1, the data validation would allow 10 letters in the cell range c1:c20. If the person selects "goodbye" in cell a1, then the validation would only allow 7 characters in cell range c1:c20. I have been using the worksheet Change function, however it updates the validation regardless of what cell is changed.
Is there a way to create a data table where the input cell is not on the same sheet as the table? I've tried using a named range, and can't make that work.
I usually work around the problem by putting my data tables on my assumptions sheet, or creating a new input cell on the data table sheet, and linking the original input cell to it,
Cell A5 has a Yes_No pull-down list for data validation. In cell B5, I want to display the value 0.002 only if A5 is "Yes". If A5 is "No", I want the user to be able to enter a number into the B5 cell without destroying the formula.
View 4 Replies View RelatedI'm working on a sheet that gets filled in with background colors (red, light blue, and green) and I'm trying to come up with a way to automate the process of inputting the text in the red and blue fields (red always gets PROCESS and light blue always gets SHPD) but they end up in different locations in the same columns. is there a function or macro to make this happen? I have constructed a macro to compile the sheet from my online database as it is a live system.
MODELACTIONxxx1xxx2xxx3xxx4xxx5xxx6xxx7xxx8xxx9xxx10xxx11xxx12xxx13xxx14xxx15xxx16xxx17 This is a simplified version of what I'm working with, and i'm on Excel 2000
I want to track winning football teams by highlighting the victor. So lets say I have a cell that contains
Raiders @ Niners
and another cell containing "home" or "away" indicating the winner.
Based on the home/away cell, I want to either highlight Niners/Raiders.
I am wanting to transfer data from one sheet to another depending on cell input
Example:
If I enter "2" in sheet1 cell B1, then I want a macro to take A1 to sheet2 but place it in A1 & A2.
Then say I enter "1" in sheet1 cell B2 goto sheet2 and put A2 into A3 and so on.
Depending on the number entered on sheet1 column B, the Macro duplicates the info from Column A in that row into column A on sheet2. ??
I request you to write a code for me to fill the cell values as "Not Applicable" in Column "AZ", if the "B" Column cell values = "Justified", "Approved LSAR" & "Approved SDAR".
I have attached the work book of what I am trying to accomplish.
it should compare and see if product, model for same id is the same but brand is different and the quantity of one or more of those brands=0 then the result (In column F)should combine the brands. check the attached image for more details.
View 5 Replies View RelatedTwo formulas which I need in Cells E74 and E75.
Basically the formulas will depend whether the entry in D73 are Yes or No
in cell E74, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF ‘D73’ = ‘Yes’ then E74 = E70
in cell E75, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF D73 = ‘Yes’ then E74 = E70
Excel version 2010
Instructions are in the attached excel as well. Please see attached
If a person puts in a price in column A, that results in column B "Everyday Price Per Unit" being greater than column C "NDP", then highlight column A cell input red.
=Column B>Column C, then turn input cells in Column A Red.
First four highlighted are examples of thise formula scenario
** All based off an input into column
How to use conditional formatting to do so???
Excel Conditional Formatting 06.24.14.xlsx‎
I do data entry for a driveway company. I get addresses and phone numbers, and then call the people to offer services. I keep track of my calls on the spreadsheet, color coded for my results, green for leads, yellow for voicemail/no answer, and red for not interested.
I have formulas in cells M2 through M8 to give me my overall stats for the calls I have made, it filters them by color for leads, missed calls, and rejections, and a couple other stats (mostly for my own curiosity)
What I'm trying to add is the cells L10 to M13, I want to be able to input a date, for instance 8/15, and have it run the same stats, just for that day essentially. So, I need it to search the last 2 columns in the table for the text that I input into cell M10, and then run the color based formula on those cells, and total them up in the appropriate cells, M11, M12, and M13.
The 2 Stats tables are the same at the moment in the sample, as I only included one particular street that I have mapped, my actual table is nearly 3000 rows.
Various Numbers Will Be in a drop-down List eg:
6718
0820
7141
0821
I need it to Be able to, When I Select '6718' from the drop down Menu
"375 CAN 2lB18 C/F D/COKE" will Appear in the Next Cell.