Hide Columns On Sheet2 Based On Cell On Sheet1?
This is what I would like to do if possible: If E9 on Sheet1 is 1, then hide columns F-L on Sheet2.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Copy Script From Sheet1 To Sheet2
i have a sheet called Template with this code Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 And Target.Row = 2 Then If Target.Cells.Count 1 Then Exit Sub boo = Target.Value boobs = Target.Offset(-1, 0).Value nocold = 4 WO_update_cust End If If Target.Column = 2 And Target.Row = 3 Then If Target.Cells.Count 1 Then Exit Sub boo = Target.Value boobs = Target.Offset(-2, 0).Value nocold = 5 WO_update_cust End If End Sub i have another script than unhides a sheet and copys whats in the template to it. Simply put i want to copy this script to it too. i used a copy and add sheet method but i need to be able to do this in a shared workbook, hence the hidden sheets instead.
View Replies!
View Related
Find A Value On Sheet1 And Input It On Sheet2
If I have 2 sheets names 'User Info' and 'Labels' what i'm looking to do is on the 'Labels' sheet under cell J2 to be able to enter a number, 505 in this case which searches for this number under column B of sheet 'User Info' finds it, looks to the entry in column D (the name Ken) and in turn puts the name into cell J2 in sheet 'Labels'. The real life use for this is to search a phone extension number in User Info sheet, and use the user name at that extension number and place it in a label on the Label sheet for a reception phone. The Reception phone can have multiple extensions on it made up of any 3 digit number on any where from 3 keys to 12+. So the ability to search for 100-999 is needed.
View Replies!
View Related
Dde Data Copied From Sheet1 To Sheet2
I am enlcosing a file that I partially acquired (the macro portion) and I am trying to add to that. The workbook has a dde link in sheet 1, the macro will write the data to sheet2 at set times. I am creating a report of the data in sheet 2 in sheet3. at this point it is very rudimentary. I need some assistance to clean up the macro and the post of the dat in sheet2. I have created a detailed (probably over detailed) explanantion in sheet 1 in a text box.
View Replies!
View Related
Coppying Data From Sheet1 To Sheet2 Using (VLOOKUP)
Can anyone tell me if is possible to copy data from sheet1 to sheet2 using VLOOKUP? I had an interview where they asked me to copy data from sheet1 to sheet2. I used the function (=Sheet1!A1:...) and for me it was right, but they told me that it was wrong since I did not use the (VLOOKUP) function. Please confirm if VLOOKUP can be used in this regard.
View Replies!
View Related
Pulling Of Data From Sheet1 To Sheet2 Or Any Backup
I have some data in sheet1 (as per attached sheet), every month i have to enter this data more than 1000 rows..i want a formula so that automatically this data should go to sheet2 and get a seperate report shape (as mentioned in sheet2) and also add a row below of each report in case ColumnA (Shipp) data is increased for some shipp name and also report heading shoudl be change according to columnA (shipp) and renumbering it.
View Replies!
View Related
Transpose Values All Cells From Sheet2 To Sheet1
on the sheet I wanted to transpose to,Starting w/ the first cell, click, in the formula box, type ,= then goto the other sheet w/data and click. Excel will autofill the formula. Then, using that newly transposed cell , copy and paste to all the other cells. This will auto transpose the rest of the cells in direct relationship to Cell column and row of the other sheet. Next, I went back to and did copy and pasted the formats of the cells. After completion, I've got a mirror copy of my sheet. There's still probably a simpler way of doing this. But this worked for me. Need a Simple VBA code or excel formula to display values and cell coloring generated from the formulas or vba code from sheet2 onto sheet1.
View Replies!
View Related
Copying Updated Data From Sheet1 To Sheet2
I have workbooks which have data broken out of a larger file, I have solved the issue of updating the "Master List" from these broken out sheets. My problem is updating sheets. This has been done cut and paste manual style for some time and I am trying to streamline the work since being assigned to this project. I am trying not to change the workflow so I have to keep the sheets and work around the setup. Sheet1 contains identical data as sheet2 with the exception of the completed work which is removed. In other words sheet2 contains an "assigned" list of work maybe 200 rows, this includes any work not yet completed in sheet1 as well as all the work previously completed but removed from sheet1 which may only contain 50 rows. Very simply, I want to parse through column A in both lists containing a specific ID number and match accross sheets, if column G and H on sheet1 match my needed criteria and column A from sheet 2 then copy that row from columns G:AT or 7 to 46, but only the values since there are formulas and formats I don't want in the updated sheet2 because the script I have for pulling from these sheets to the MasterList would fail. As a bonus to eliminate the need to run another bit of code if I could delete the copied rows from sheet1 after they have been sent over to sheet2 would be great. Headings for all the sheets are the same so the data is identical. Column A is a customer ID number, G assumes initials from the clerk, and column H is date work completed. So if record A found and G=XX and H=Date then copy this rows columns G to AT values only. After which rows copied over will be deleted from sheet1. Headings are in row one and data begins on row two accross all sheets.
View Replies!
View Related
Inserting 1 Line In Sheet1 To Enter 5 Lines In Sheet2
One has around 129 lines for the first years 2006 in sheet1. The other links to the first 129 rows. Then I've linked the top 129 in sheet2 130 down for 2007 and so on for 5 years. How would I add a line in Sheet1 then would insert 5 lines in sheet2 keeping the same order. e.g Sheet1 Name Rev Year John £120 2006 Row 5 Jack £150 2006 Row 6 Sheet2 (linked) Name Rev Year John £120 2006 Row 5 Jack £150 2006 Row 6 John £130 2007 Row 134 Jack £160 2007 Row 135 John £140 2008 Row 263 Jack £160 2008 Row 264 John £150 2009 Row 392 Jack £170 2009 Row 393 John £155 2010 Row 521 Jack £180 2010 Row 522 The Rev changes by formulas in Sheet 1
View Replies!
View Related
Change Value In Sheet1 And Refresh Data Of Pivot Table In Sheet2
I can't understand why my rate1() function (my macro) doesn't work properly. While I'm dragging M5 cell in Sheet2 down to M14 cell it gives the wrong result. Even when I change value in Sheet1 and refresh data of pivot table in Sheet2, my function rate1() isn't updated. But my problem is solving (getting the correct result) when I'm going Edit Mode (pressing function key F2) in active Cell M5 or M6 or M7..so on and pressing Enter each time.
View Replies!
View Related
Hide/Unhide Columns Based On Cell Value
I've attached one speadsheet :- "VBA.xls". The following conditions are to be done:- 1. When D2=1, Column "F","G" are visible & Column "I","J","L","M" are hidden. 2. When D2=2, Column "I","J" are visible & Column "F","G","L","M" are hidden. 3. When D2=3, Column "L","M" are visible & column "F","G","I","J" are hidden.
View Replies!
View Related
Hide Columns Based On A Cell Link
I need to hide columns in a spreadsheet without using the hide/unhide columns option. I found an article which suggested setting up a combobox from the forms toolbar, this is populated by an input range and linked to a cell for a numeric value. The article although using a macro to hide rows suggested this code Private Sub ComboBox1_Change() If ComboBox1.Value = "A" Then ActiveSheet.Range("1:3").EntireRow.Hidden = True ActiveSheet.Range("4:6").EntireRow.Hidden = False ElseIf ComboBox1.Value = "B" Then ActiveSheet.Range("1:3").EntireRow.Hidden = False ActiveSheet.Range("4:6").EntireRow.Hidden = True End If End Sub I want to amend this code so that when the number in my linked cell changes the various columns are hidden. For example if the cell link number = 1 hide columns e to az if the cell link number = 2 hide columns d and f to az etc. I have had varying success with amending this code to entirecolumn.Hidden and changing the Combobox1. to dropdown1. but am unable to get it to work, I also stupidly deleted the code that was partially working and am now unable to remember what I'd done.
View Replies!
View Related
Hide/unhide Number Of Columns Based On Cell Value
If I have a value in A1 and run a macro [button], I want to have it hide a number of rows. Each number is one column. eg if A1 = 2 then B:C are visible, D:IV Hidden if A1 = 3 then B:D are visible, E:IV Hidden if A1 = 4 then B:E are visible, F:IV Hidden up to 200 columns. I tried it as a select case, but it is limited to just 22 cases which obviously not enough.
View Replies!
View Related
Automatically Hide/Unhide Columns Based On Cell Text
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column <> 13 Then Exit Sub If InStr(Target.Value, "Other (specify in next column)") Then Columns("N").Hidden = False ElseIf WorksheetFunction. CountIf(Columns("M"), "Other (specify in next column)") = 0 Then Columns("N").Hidden = True End If End Sub but I have a lot of columns that I need to perform as above and I have put the code together as below Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Column = 13 And InStr(Target.Value, "Other (specify in next column)") Then Columns("N").Hidden = False ElseIf WorksheetFunction.CountIf(Columns("M"), "Other (specify in next column)") = 0 Then................. Using the above code, when I selected more than one cell anywhere in my workbook and pressed delete I was bugging out with a runtime error 13 message. You can see from the above code that I inserted "On Error Resume Next" - this got rid of the runtime error 13 message, but now when I select more than one cell and press delete, hidden columns are incorrectly revealed in my worksheet. how I can extend the working code at the top of this posting so that it works for a number of different columns in my Worksheet i.e. without the runtime error 13 occurring and without columns being incorrectly revealed.
View Replies!
View Related
Hide Unwanted Columns & Clear Ranges Based On Cell Value
I have written a VBA procedure which is supposed to hide unneeded columns (based on the a number entered in a certain cell. This is working so far. Since I need to sum only the visible cells in a row i need to clear the contents of a range of cells if they will be hidden. Unfortunately the code i have written runs only to the ClearContents and then starts over from the beginning. If i take out the .clearcontents and put .Select for example instead, the code runs perfectly. Here is the Sub worksheet_calculate() 'Hide columns that are not needed Sheet1. Unprotect Application. ScreenUpdating = False Dim i As Integer Dim r As Integer Dim s As Integer Dim rngRange As Range i = 2
View Replies!
View Related
Loop To Compare Cell In Sheet2 To 2 Columns In Sheet 1
I need a macro that will look - this is hard to explain so will prob do wee diagram too but basically the macro nees to start looking at sheet2 A5 (the numbers start at A5 before that is text) see if it is greater than Sheet1 ColumnA & Less than Sheet1 columnB if the number is then I need Sheet2 ColumnD inserted into sheet 1 ColumnG. There is the text END on all columns on both sheets for the loop, the reason for the loop of course is there could be 100 lines in sheet2 ColumnA & I must look at all lines in sheet1 to see if it is greater than columnA and Less than ColumnB and if it is populate Column G. Sheet1 Column A ColumnB ColumnG Row 11 4000 4999 Row 12 5000 6099 Row 13 6100 7400 ETC END END Sheet2 ColumnA ColumnD Row5 5130 134.58 Row6 6900 6400.00 ETC END END After the macro the result would be Sheet1 Column A ColumnB ColumnG Row 11 4000 4999 Row 12 5000 6099 134.58 Row 13 6100 7400 6400.00 END END END Sheet2 ColumnA ColumnD Row5 5130 134.58 Row6 6900 6400.00 END END
View Replies!
View Related
Copy Data From Sheet2.xls To Sheet1.xls?
I have: - sheet1.xls, this is the source sheet. column A = model numbers - sheet2.xls, have the data that I need to copy to sheet1.xls column C = product description text and in column D = product price column G = product description text and in column H = product price What I need to do is to write a Macro that: open each row in column A in sheet1.xls and search in sheet2.xls in column C if it finds this text (model number) then it should copy row D (product price) and paste it to column E in the right row in sheet1.xls. Also search in sheet2.xls in column G if it finds this text (model number) then it should copy row H (product price) and paste it to column E in the right row in sheet1.xls. I hope please that you understand me. And will be happy if someone could help me please to do that
View Replies!
View Related
Copy Row From Sheet1 To Sheet Based On Cell Value
i'm trying do is have information from sheet1 copy to sheet2 upon selecting a value from a DV list. Based on my attached sample, in Column J, upon selected "Yes", I would like to get the information from A6:H6 copied to sheet2 in the same row. I've tried it with the "if" formula and writing a macro for it.
View Replies!
View Related
VBA Lookup Function: Paste Data Into A Cell In Another Worksheet, Based On Criteria Specified In Sheet1
I'm looking to create a macro that will take data from an input sheet, and paste it into a cell in another worksheet, based on criteria specified in sheet1. Specifically in the attached example, the macro would copy the data in cells C8:C10 of sheet 1, then paste them into sheet 2 based on the data specified in cell B3 i.e. it would paste them into the column headed Mar-09. I intend to make this cell a drop down, so that the user can then select the next reporting month and run the macro again to paste the data into the Apr-09 column.
View Replies!
View Related
Hide Columns Based On Selection
I have a worksheet that lists the months of the year on row 3 from columns C through N. What I am trying to do is to be able to select the month I want from a drop down list or combobox and then hide the remaining columns based on the selection. For example if JAN is selected the months FEB through to DEC would be hidden.
View Replies!
View Related
Hide Columns Based On A User Entry
Is there a way of a user can add a number in a cell and this would hide a different amount of columns. OR : Ideally I would prefer a drop down with dates and the user could select a forward date and all the columns with dates up to that would appear. This would mean they could look as far our as required and all unwanted data would be hidden.
View Replies!
View Related
Unhide/hide Multiple Columns Based On Date
I need to show hidden columns based on the date I entered. For example, if I entered "1/1/1990" on a1 as the starting date and "4/30/1990" on b1 as the ending date. I want Excel to show the columns that are covered by the date, thus it shows Jan, Feb, March and April. How do I do that? Here's an example attachment. In here Sheet 1 is the starting point, the highlighted cells is where I enter the date. the Result sheet shows what I want Excel to show me when I have a date entered.
View Replies!
View Related
Hide/unhide Columns Based On Dropdown List Answer
I am a newbie to Excel. I really appreciate if someone could help me here and this is very urgent as I have a project going on. I have a cell say B3, which is a dropdown list I created using Data Validation. It only lists down Yes or No. What I need is when you click on Yes in B3, i need columns C to I to unhide. If answer is No, column C to I should be hidden. By default, the columns will be hidden. Can you please help? I tried many times but not successful. Appreciate if someone will be able to walk me through details. If you want to see the sample spreadsheet, I can sent it to you by email. I tried Data> Outline group but they are not happy with this.
View Replies!
View Related
Hide Columns With VBA When Cell Value Changes
I have a list of 24 columns (12 months each for forecast and actual data). I want to put VBA code in the worksheet that will hide a column based on a cell value in each column. On line 4 there is a formula that returns either "show" or "no". The columns are AS:BP. The code needs to run on a change in any of the cells AS4:BP4.
View Replies!
View Related
Hide Columns Starting From Active Cell
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 Replies!
View Related
Formula To Calculate Price Based On Sheet2
I am looking for a formula that can change the price of some of the items on sheet1 in column C by the amount found on Sheet2 in Column D. I would like it to base the calculation on column C in sheet2 (so I can choose if I want to add, subtract, multiply, divide, or make the price exact). I would like all prices that don't match these UPC codes to remain unchanged. Intended Results can be seen on Sheet1 in Column E. Not sure if I should a formula with the Vlookup function or a macro, or maybe there is even a better solution. Example spreadsheet may be viewed at http://spreadsheets.google.com/ccc?k...WLMyhNJLiPLTfA.
View Replies!
View Related
Hide/Unhide Columns Depending On A Specific Cell Value
I am looking or a code, (Or formula if one exists) to hide or un-hide columns depending on what is entered into a cell. I have attached an example to help clarify what i mean. Basically i want: If A2 is empty, hide columns C, D, E & F If A2 = Apple, Unhide columns C & D, but keep E & F hidden If A2 then becomes Banana, rehide C & D and unhide D&F
View Replies!
View Related
Hide Columns If Cell In Specific Row Is Blank
I have a row in a table from columns C to CZ. All the cells in the row contain a formula, between 1 and 204 cells in the row will have a value (i.e. will not be blank). The cells with a value will start at column C and may or may not have a blank cell before no more values and blank cells to the end (Col GZ). Example: C D E F G H I J K >..........................GZ 23 34 67 74 2 34 6 2 56 all blank ("") to end or: C D E F G H I J K >..........................GZ 23 34 67 "" "" 34 6 all blank ("") to end I need to hide the entire columns when the cells in this row are blank but NOT if the blank cell has valued cells after it (i.e. do not hide columns F and G in the second example. I can do this by looping back from col GZ and hiding the columns one at a time, which is very slow. I am stuck on the code to select all the relavent columns and hide together.
View Replies!
View Related
Insert Rows Based On Criteria Match From Sheet2
There are many columns in Sheet1 and more will be added for titles of the columns remains unchanged. One of the Col. in Sheet1 is 'Part Number'. Part Numbers are defined as 'Configurations', and each Configurations has additional parts and peices which are broken down in Sheet2. Back in Sheet1, I also have a Col. for QTY. So, if Sheeet1 has a part number that matches in Sheet2, then I would like Excel to copy all 'parts and pieces' and QTY from Sheet2 to Sheet1. Please make sure that I want the QTY to be multiplied if applies. Please see below for an example: Sheet1 looks like this:...................
View Replies!
View Related
Hide Rows Based On Multiple Columns Formula Returning Empty Text ""
I would like to rows based on multiple column conditions criteria. ie., if the columns N, O, P values are "", then hide the particular row. The logic given in the website here, i tried But, it is not 100% working. It works for a few rows at the start of the database & it works for the rows at the end of the database. In between, for a few rows, even if the column values are "" it does not hide those rows.
View Replies!
View Related
Hide Columns & Hide X-axis Labels
I am filtering the data displayed in a chart by hiding columns. I would also like to filter the X-Axis labels by hiding columns. If I do this manually I have no problems but when I run the following macro the chart gives a reference error for the X-axis labels. Sub ShowA2() Application. ScreenUpdating = False num = Sheets.Count Sheets("X-Axis").Activate Range(Columns(1), Columns(256)).Select Selection.EntireColumn.Hidden = False For a = 1 To 5 Sheets(num - a).Activate If ActiveSheet.Name = "A2 Data" Then Columns("A:Q").Select Range("A10").Activate Selection.EntireColumn.Hidden = False Sheets("X-Axis").Activate Columns("A:E").Select......................
View Replies!
View Related
Way To Hide Row Based On The Value Of A Particular Cell
I have a worksheet with an age row of 20 - 99 (and a bunch of other data) A user enters the starting age, and then the sheet fills in the rest of the cells. If the user enters 50, the age fills from 50 to 130. Before printing, I manually hide the rows (using the hide row command) over age 99. Is there an easy way to do this without macros? I am Excel impaired! I tried using Auto filter - but I have to rerun the filter each time (it is not automatic).
View Replies!
View Related
Hide Row Based On Cell Value ..
I would like a macro where I it will examine Cells A8 down to A27 to see if there is a value. If there isn't a value, I want that row hidden. I would like for it to always run (i.e. if a value is put into that cell, the row reappears, otherwise it is always hidden).
View Replies!
View Related
Hide Object Based Cell Value
I have 2 equation objects created with microsoft equation 3.0. I have named the equation objects Eqn9 and Eqn10. I also have an if function, what i would like to do is if a number in cell AC58 is greater than 0.3 a macro runs and hides Eqn9 and shows Eqn10, similarly if the number in cell Ac58 is less than or equal to 0.3 the macro hides Eqn 10 and shows Eqn9.
View Replies!
View Related
VBA Macro To Filter Rows By Cell Value & Hide Blank Columns
i have created a spreadsheet to simplify our work flow, I am stuck on what is probably the easiest of the commands. basically have rows dedicated to specific codes and the colums represent values relating to each code, all codes have a different set of values, the attached example only has a few variables but the actual worksheet will have several hundred. the idea is the user will input the code they wish to get details on in A2 and then press the command button and it will then show (as per the after sheet in the attachment) just the relevant information for that code, so filter the code in column A and hide the columns which hold no value. where i am getting stuck is I am not sure the best way to proceed, is it best to create the macro button to do the filter and hide or is there a better way using vlookup and a pop up window asking for the relevantcode to be inputted to to retrive the information, again understand there will be hundreds of colums and hundreds of rows and the values may be 20 or 30 colums apart for some of the Codes so this simplification is really saving the user a lot of time.
View Replies!
View Related
How To Hide/unhide Rows Based On Cell Value
I'm using column A as a reference. My data starts in cell A3. Whenever theres a zero in any cell in column A I would like that row to hide. If the row was hidden and the value changes to any number greater than zero I need the row to unhide. I can record two macros for the hide/unhide part of this but I dont understand how to run them based on cell value.
View Replies!
View Related
Hide Or Show An Image Based On A Cell Value
I have a spreadsheet with two bitmap images inserted into it. In cell E2, I will enter a number, either a 1 or a 2. if I enter a 1, I want only the first image to be visible. If I enter a 2, I only want the second image to be visible. Is there a way to accomplish this (hopefully without the need for macros)? I've attached a spreadsheet as an example of what I'm trying to do. Also, note that I'd like the images to be stacked on top of each other so that they show up in the same place regardless of wether there's a 1 or a 2 in cell E5
View Replies!
View Related
Hide Rows Based On Cell Value Being Empty
I have read other posts about this but I am still a little confused as most macros that have been made are tweaked to suite the users individual needs. What I want is something like this to work in the active sheet: If cells D2:D55 = "" Then Hide.EntireRow If cells D2:D55 = "has any value" Then Show.EntireRow The values in D2:D55 are populated by a VLOOKUP depending on what someone chooses in a drop down validation list, however not all the rows are always required so I would like to hide them to save some space on my form.
View Replies!
View Related
|
TRACKER
