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 RepliesI 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.
I am trying to figure out how to do the coding for a command button in a userform I've created. It has 5 textbox fields. I want the command button when clicked to transfer the data in those 5 fields to 5 specific cells in a hidden sheet. Then I also want that button to launch a word document.
Anyone know how I can go about this or where I can go that explains the specific coding?
I've tried searching with mixed confusing results & read through http://www.contextures.com/xlUserForm01.html#Top & http://www.theofficeexperts.com/down...ExcelDownloads examples.
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.
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.
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?
I have a workbook with a hidden sheet ("Template") and a visible sheet("New Job"). I need code so when cell F1 in "New Job" is populated:
1-the sheet is renamed to the value of F1,
2-a new tab is made (a carbon copy of the hidden sheet "Template")
3-the new tab is named "New Tab" and marked as unhidden.
Public Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Name = Range("F1").Value
End Sub
Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function..................
i am trying to merge cells based on the input from another sheet. these input will change from month to month and i need the sheet to cancel the merge and merge again based on the new input.
Example.xlsx the file showing what i need it to do.
Attached is a sample of what i'm trying to do.
I want to use the data in the Setup Sheet to parse data in the Master sheet.
so for each row in the Master sheet, if the Property Column cell matches a Property cell in the Setup sheet, copy that row to a sheet named after the Person Cell (identified in the setup sheet).
Sorry if that's not very clear. I am not proficient at using advanced excel functions. I'm hoping this is an easy one..
I need to make a VBA Macro (Assigned to a button) that will copy the information under the "Cut Length Worksheet" and paste it into the appropriate columns in our "Bill Of Materials Template." Any way to copy the cells, across different rows, into the proper cells.
I have several different Macros and Workbooks made up now, but I am lost here.
I also need the Cut Lengths to add certain info to the cell when it is copied:
8 | 7-15/16 ---> 8'- | 7-15/16"
Here is a Link to the Excel page.
I have 2 worksheets (X and Y) and am trying to write a macro/vba to copy certain cell values from a row if that row has a marked cell. For example, on sheet X, marked cell is column P and when P = "Y", then values from cells B, D, I, J, L need to be copied to sheet Y.
Sheet Y has multiple rows and the values from X need to be copied into appropriate rows. B from X goes into A from Y, D into B, I and J into C, etc.
Im not sure how to go about this other than have every cell copied and then filtering sheet Y. But Id like to have a macro only copy marked rows and appropriate cell values (some contain formulas) as the data is constantly changing and sheetY has to be printed up.
I have a sheet that has 5 columns as shown below, xxx represents the values within the cell that I need in each sql file.
A |B | C| D| E
test1|xxx|xxx|xxx|xxx|
test2|xxx|xxx|xxx|xxx|
test3|xxx|xxx|xxx|xxx|
test4|xxx|xxx|xxx|xxx|
test5|xxx|xxx|xxx|xxx|
What I am trying to do is take the context of each cell starting with B2, and place it in an Individual sql file (or text file with .sql extension) and name it A2 (so test1 in this case).
I tried creating a double for loop, one for last column, one for last row, taking each content and placing in file but I am not sure how to take the column A as the name of the file and to have 1 file per cell.
I run this macro that populates 7 cells based on data in another sheet. It seems that every sheet that have formulas that point to this sheet are being stuck in in the screen updating somehow?
Basically when I press F9 to calculate or change any cell that makes the sheet calculate it has like a screen burn in of all the sheets that are being calculated. I have been able to use this as a workaround:
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Application.ScreenUpdating = True
End Sub
But I shouldn't have to do this and it slows it down as it runs through this specific code about 11 times for each sheet that is re-calculating. Something is getting stuck in memory or something that seems to be causing this issue. This is the code I am using and I am not selecting any cells or sheets, but it appears that when I walk through the code that an image of the sheet comes up on the screen? The code in red seems to be what is causing the issues, but I do not know why? I can pass the 2 workbooks along to someone so they can see the behavior if they would like? Just let me know.
Sub populateEmployeeData()
Dim srcWorkbook As Workbook
Dim foundEmployee As Range
Dim srcWorkbookName As String
srcWorkbookName = "XIP_Employee_Data_" & Left(ActiveSheet.Range("B9").Value, 31) & "_" & Format(ActiveSheet.Range("B10").Value, "MMMDDYYYY") & ".xlsx"
If IsWorkBookOpen(srcWorkbookName) Then..........................
I have this code that first selects a sheet (data) and then imports a csv-fil to the data sheet.
I want to hide the data sheet and the the code doesn't work.
How do I change the import code so that I do not select the sheet?
[Code] ......
I believe it should be something like this: With Sheets("DATA").QueryTables. Add..... but I am not aware of the syntax.
I have a userform which clones the latest sheet and produces copy of it on the next sheet. The first sheet they will be cloning is the sheet called 'template', I however want this to be hidden since I do not want anybody to modify a sheet which basically serves purpose of a template. And once cloned, it is no longer needed.
On the click of the command button, the macro will create sheet1 taking the info from sheet named 'Template' in the same workbook . And now on the next click of the command button, Sheet 2 is created taking the info from sheet1 and Sheet 3 is created taking the info from Sheet 2 and so on. Here is what I currently have, so how can i modify it in order for my scnerio to work?
To select the last sheet in the workbook
Code:
Sheets(Sheets.Count).Select
To create new sheet
Code:
Sheets(Sheets.Count).Copy After:=Sheets(Sheets.Count)Sheets(Sheets.Count).Name = MyEvent & " " & MySCN & "(" & ThisWorkbook.Sheets.Count - 2 & ")"
I have a button on a sheet that runs a macro to unhide another sheet. That works, but I want the sheet made visible to remain forward. Instead, the button unhides the sheet and the sheet the button is on comes forward again. I am unable to figure how to keep the sheet made visible forward. Here is the macro 'as recorded'.
Sub UnhideSheet1()
Sheet2.Visible = True
End Sub
I want to change the fill color of all cells on my sheet, based on the value of one specific cell. In my sheet, I am using cell F1 as the trigger for the change. If the word Blue is in the cell, I want the background color of all cells to be Blue. Likewise for Red and Yellow as well. I don't believe conditional formatting can get this done, as all but the one cell (for this) will be empty.
View 2 Replies View RelatedSheet1 is a report that expresses 90 days of information and Sheet2 has three columns of 365 day information. Sheet1 has a title, a today function for the date (cell B2), two columns (A5:A94 and B5:B94), and then some other info not relevant to the problem. Sheet2 has three columns (A1:A365, B1:B365, and C1:C365) with the date in column A starting at the first of the year and then two lists of information in columns B and C. I am wondering if there is any way excel would be able to recognize the Date (B2) on Sheet1 and be able to pull the information from Sheet2 starting on that date for the next 90 days.
For example:
My report always starts on the day I am accessing it. So... Say it is 1/15/13. My today function in Sheet1 (B2) would express 1/15/13. This date would be equal to A15 on Sheet2. So I would want A5 & B5 on Sheet1 to return B15 & C15 from Sheet2 respectively.
A5 Sheet1 = B15 Sheet2 & B5 Sheet1 = C15 Sheet2
A6 Sheet1 = B16 Sheet2 & B6 Sheet1 = C16 Sheet2
...
A93 Sheet1 = B103 Sheet2 & B93 Sheet1 = C103 Sheet2
A94 Sheet1 = B104 Sheet2 & B94 Sheet1 = C104 Sheet2
With that being said, the next time I open up my report, say a week from the 15th or 1/22/13 (expressed by my today function in B2), I would want A5:A94 & B5:B94 to reference the new set of information:
A5 Sheet1 = B22 Sheet2 & B5 Sheet1 = C22 Sheet2
A6 Sheet1 = B23 Sheet2 & B6 Sheet1 = C23 Sheet2
...
A93 Sheet1 = B110 Sheet2 & B93 Sheet1 = C110 Sheet2
A94 Sheet1 = B111 Sheet2 & B94 Sheet1 = C111 Sheet2
Is this at all possible?
Is there a way to hide a group of cells based on the value of a cell in another sheet?
I have attached my spreadsheet.
So, based on the selection in H5 on the 'Wood Selection' sheet, I would like to hide cells on the 'Moisture Controlled Schedule' sheet, and the 'Time Controlled Schedule' sheet.
If Ponderosa Pine is selected, I want it to hide all the douglas-fir info, and vice versa
I have attached the file with this post.
What I like a macro to do is:
Whenever a 'D' ends in column "Step 5" the last value of 4 cells to the left to be put in sheet 2 in reverse order.
I want to make things easier for myself when filling in my worksheets by having certain cells autofill based on a product number.
So I have my main worksheet and I have my product information sheet. When I type in the product number in cell 13C, I want it to auto fill cell 13B with the product name which is located in collumn A on the product info sheet, but dependent upon that product number. Collumn B in my product info sheet has all of the product numbers. From there I would need 13G on my main worksheet to fill in with Collumn D from the product sheet, but again dependent upon that product number.
There are more that need to fill yet, but I can figure out what to do once I have a starting point with the above I believe.
I want to enter a number in cell, select a value from a drop-down list, and have another sheet updated with that info. For example, on sheet1 cell A1 = $900, A2 = Western Region (drop-down list). Sheet2 would have a list of values that would update based on values entered in sheet1:A1 and sheet1:A2. Is this possible using formulas or do I need to use VBA?
View 1 Replies View RelatedI've an excel sheet...where I want to color the cells on the basis of following condition:
If Cell E is non-empty then cell A, Cell B, Cell C, Cell D, Cell F, Cell G, Cell H and Cell I can not be empty. If any one of the cell is found empty then color the respective cell in red!!If collectively all the cells Cell A, Cell B, Cell C, Cell D, Cell F, Cell G, Cell H and Cell I contains a value and Cell E is empty then highlight the Cell E in red color.
I want to implement this in module section...
I have a set of sheets on a workbook that contain addresses, which are all combined on the first sheet (as you can see in the attached file). I would like to use a formula to compare the addresses on the Main sheet with the other sheets, then return in the D column of Main a colored cell indicating on which sheet the address was found on; different colors for different sheets. I tried to figure out some conditional formatting, but to no avail. Also, on occasion I have spreadsheets with 5 or 6 sheets containing these mixed addresses. Is there a solution that won't be limited to just a few sheets for comparison?
Excel Help.xlsx
On sheet "CoA" i have at table of periodic payments that is created via an input form and what I want to do is have accounting entries automatically generated in my cask book based on dates.
The code for the input form is as follows;
I am trying to auto format some merged cells within a sheet based on the information in them. how to reference the information within theses cells?
View 5 Replies View RelatedI have built conditional formatting into some cells on ' Sheet 1' and would like a message box (saying "Check errors before closing") to appear when the user tries to close the workbook if any of these cells are coloured red (ColourIndex 3).
View 3 Replies View RelatedI have attached a 97-2003 .xls file with data for multiple store locations on sheet 1, and the desired result on sheet 2. I am actually using excel 2007, but I dont think I need any special features that it provides.
I will try to explain the issue here without opening the attachment.
Here is an example of the Data on Sheet1
In the attached worksheet I have UserForm2. When I click on open compare form button on the menu sheet it opens UserForm2, I would like the information I select in the first 7 combo boxes Vegetable - Ball on UserForm2 to loop through the data in the database sheet Columns A:G and compare the entries to the non empty/not blank cells in each row. If the form contain data that matches all the non empty/not blank cells in a row in the database sheet then it is a match and should show the label and display the message. If the form entries does not match to the non-empty/not blank cells in any of the rows on the database sheet then do nothing.
The problem I am having is getting it to loop through the sheet and bring back the right results. It is only matching on row 2 of the database sheet when I select cabbage in the vegetable combo box and apples in the fruit combo box . I cannot figure out how to get it to loop through all the rows for the range I want to compare (A2:G7) - I need this range to be flexible so as data is added it will expand to read all added rows.
The code is on the btnSave_Click() for UserForm2
I attached the spreadsheet and I am explaining what I want to do and the expected result.
Fruit
Fruit Type
Vegetable
Games
Toys
Cereal
Ball
[Code] .....
What I want to do is loop through the Database sheet and if the fields on the form contain all the values in any row of the Database sheet, excluding empty cells in the Database sheet, then display a message.
So if on the form I selected Broccoli fron the vegetable combo box, Cricket from the games combo box, puzzles from the toy combo box, bananna from the fruit combo box, grits from the cereal combobox, and baseball from the ball combo box, in the databse sheet tabel shown above the match would be row 6 since the values for vegetable, game, toy, fruit, cereal and ball on the form matches what is on row 6 of the Database sheet. It does not matter what other fieds are selected /filled in on the form, the match should only take into consideration the populated cell in each row of the database sheet.
So, if the user enters Apples in the fruit combo box and Cabbage in the vegetable combo box but had blank or something other than bike in the toy combo box on form it would be a match to the Database sheet row 2, regardless of what the user enters in the remaining fields on the form
If the user enters Berries in the fruit combo box, Blueberry in the Fruit Type Combo box, Carrot in the vegetable combo box, and Grits in the cereal combo box it would be a match to Database sheet row 3, regardless of what the user enters in the remaining field on the form .
If the user enters Apples in the fruit combo box, Cabbage in the vegetable combo box, and Bike in the toy combo box on form itwould be a match to the Database sheet row 5, regardless of what the user enters in the remaining field on the form .
If the user enters Grape in the fruit combo box, Carrot in the Vegetable combo box, Cards in the game combo box, and football in the ball combo box on the form it would be a match to Database sheet row 7, regardless of what the user enters in the remaining field on the form.
If the user enters Kiwi in the fruit combo box, Cabbage in the vegetable combo box, and Bike in the toy combo box on form it would NOT be a match to the Database sheet because the Database sheet does not have a row that contain Kiwi, Cabbage, and Bike.
So basically, if the entries on the form match the exact values for all the non-empty (blank) fields for any row in the Database sheet, then it is a match.
-If the entries on the form do not contain an exact match to all the non-empty (blank) fields for any of the rows in the Database sheet, then it is not a match.
-If it is a match show the label and display the message box
-If it is not a match the do nothing
I am having trouble with my VBA code, it hides groups of rows (projects) based on the contents of corresponding cells above. these have either 'yes' or 'no' in them (indicating if the person is active in that project). The Yes/No is formula based from another sheet.
what I am trying to do, is have particular cells, withing the projects rows, locked (as they are also populated by formulas) but have other cells open for editing.
when I try and do this i get an error with my VBA, i am assuming that as cells are locked, the VBA is prohibited form hiding inactive projects/rows. i tried an addition at the bottom but this hasn't worked
Code:
Sub Worksheet_Calculate()
Dim i As Long, StartRow As Long, EndRow As Long
StartRow = 13
[Code]....