Hiding Column based on If Condition

Hi, 

I would like to know if it is possible to hide Column/Row based on I
condition. (If the cell A1 has a particular value then hide ColumnB).

Incase this isn't possible, can you help me with this problem - 

I'm having a drop down list with Jan-Dec in Cell A1. In Cells B2:G10, 
need to input a value corresponding to each of the months. This valu
I'm retreiving and using in a different location using If Condition
The problem is that incase I change the month to Feb, the value
inputted for Jan remains the same throughout. Any change made for Fe
gets reflected throughout. I'm not able to tie Jan-Dec to the Cel
values in B2"G10. 

How to retain the values, specific for each of the months. The inputte
data should remain the same, even if I change the value in A1

The simple way out would be to have Jan-Dec in separate cells but 
feel that this would make the data entry screen big and complicated fo
the end user. In case no month is selected a default value is choosen.


Is there any other way out to input the data and retrive it elsewhere

--
as
-----------------------------------------------------------------------
asr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2375
View this thread: http://www.excelforum.com/showthread.php?threadid=37412

0
5/26/2005 10:46:31 AM
excel 39879 articles. 2 followers. Follow

2 Replies
430 Views

Similar Articles

[PageSpeed] 0

Hi!



I can think of a solution like this:

1. On the same sheet where you are trying to input data, mark 12 blocks of 
cells, 1 for each month.

2. Hide all the bloacks at the outset

3. Ask the user to choose the month from the drop down in cell1

4. Unhide the specific area (rows or columns) depending on how you have 
organised the data corresponding to the chosen month

5. So you would not have to store the data elsewhere, but if you wish to do 
that, you could do that too.



You will have to write some code in Visual Basic to accomplish this.



Let me know if this works/ does not work for you



There might be more elegant and easier solutions, but just thought I'd give 
you a start since I hadn't seen any responses to this post yet.



Regards,

Rajni



Ps: this is my first attempt at posting to a newsgroup. So in case I have 
made any mistakes, please feel free to point out



Rajni



"asr" <asr.1pnqr9_1117142111.3768@excelforum-nospam.com> wrote in message 
news:<asr.1pnqr9_1117142111.3768@excelforum-nospam.com>...

>

> Hi,

>

> I would like to know if it is possible to hide Column/Row based on If

> condition. (If the cell A1 has a particular value then hide ColumnB).

>

> Incase this isn't possible, can you help me with this problem -

>

> I'm having a drop down list with Jan-Dec in Cell A1. In Cells B2:G10,

> I need to input a value corresponding to each of the months. This

> value I'm retreiving and using in a different location using If

> Condition. The problem is that incase I change the month to Feb, the

> values inputted for Jan remains the same throughout. Any change made

> for Feb gets reflected throughout. I'm not able to tie Jan-Dec to the

> Cell values in B2"G10.

>

> How to retain the values, specific for each of the months. The

> inputted data should remain the same, even if I change the value in A1

>

> The simple way out would be to have Jan-Dec in separate cells but I

> feel that this would make the data entry screen big and complicated

> for the end user. In case no month is selected a default value is

> choosen.

>

>

> Is there any other way out to input the data and retrive it elsewhere?

>

>

> --

> asr

> ------------------------------------------------------------------------

> asr's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=23751

> View this thread: http://www.excelforum.com/showthread.php?threadid=374122

>

"asr" <asr.1pnqr9_1117142111.3768@excelforum-nospam.com> wrote in message 
news:asr.1pnqr9_1117142111.3768@excelforum-nospam.com...
>
> Hi,
>
> I would like to know if it is possible to hide Column/Row based on If
> condition. (If the cell A1 has a particular value then hide ColumnB).
>
> Incase this isn't possible, can you help me with this problem -
>
> I'm having a drop down list with Jan-Dec in Cell A1. In Cells B2:G10, I
> need to input a value corresponding to each of the months. This value
> I'm retreiving and using in a different location using If Condition.
> The problem is that incase I change the month to Feb, the values
> inputted for Jan remains the same throughout. Any change made for Feb
> gets reflected throughout. I'm not able to tie Jan-Dec to the Cell
> values in B2"G10.
>
> How to retain the values, specific for each of the months. The inputted
> data should remain the same, even if I change the value in A1
>
> The simple way out would be to have Jan-Dec in separate cells but I
> feel that this would make the data entry screen big and complicated for
> the end user. In case no month is selected a default value is choosen.
>
>
> Is there any other way out to input the data and retrive it elsewhere?
>
>
> -- 
> asr
> ------------------------------------------------------------------------
> asr's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=23751
> View this thread: http://www.excelforum.com/showthread.php?threadid=374122
> 


0
6/1/2005 10:10:13 PM
You could use a worksheet_change event (if you're typing the value into A1):

Rightclick on the worksheet tab that should have this behavior.  Select view
code and paste this into the codewindow:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    With Target
        If .Cells.Count > 1 Then Exit Sub 'one cell at a time
        If Intersect(.Cells, Me.Range("a1")) Is Nothing Then Exit Sub
        
        On Error GoTo errHandler:
        
        Application.EnableEvents = False
        .Range("c1").EntireColumn.Hidden = CBool(LCase(.Value) = "hide")
    End With
        
errHandler:
    Application.EnableEvents = True
    
End Sub

Back to excel and type Hide in A1 and then type something else into A1.  Watch
what happens to column C.

asr wrote:
> 
> Hi,
> 
> I would like to know if it is possible to hide Column/Row based on If
> condition. (If the cell A1 has a particular value then hide ColumnB).
> 
> Incase this isn't possible, can you help me with this problem -
> 
> I'm having a drop down list with Jan-Dec in Cell A1. In Cells B2:G10, I
> need to input a value corresponding to each of the months. This value
> I'm retreiving and using in a different location using If Condition.
> The problem is that incase I change the month to Feb, the values
> inputted for Jan remains the same throughout. Any change made for Feb
> gets reflected throughout. I'm not able to tie Jan-Dec to the Cell
> values in B2"G10.
> 
> How to retain the values, specific for each of the months. The inputted
> data should remain the same, even if I change the value in A1
> 
> The simple way out would be to have Jan-Dec in separate cells but I
> feel that this would make the data entry screen big and complicated for
> the end user. In case no month is selected a default value is choosen.
> 
> Is there any other way out to input the data and retrive it elsewhere?
> 
> --
> asr
> ------------------------------------------------------------------------
> asr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23751
> View this thread: http://www.excelforum.com/showthread.php?threadid=374122

-- 

Dave Peterson
0
ec357201 (5290)
6/1/2005 10:29:36 PM
Reply:

Similar Artilces:

summing a column according to criteria of two other columns
I am creating a cash flow forecast sheet and trying to add up the project values of a particular service provided in june this year. i am attempting to do this by referring to a sheet called data which has a column of project values (N), a column with the month and year of the project (V) written as 62004 and a column of services (E) of which i want to select those projects where the service was Translation - Standard. By looking at the help for countif by multiple criteria i altered the formula to a sumif. I am using the formula... =SUM(IF((Data!$E$3:$E$2000='Translation - Standard'...

Hiding "0" values in pivot table
In a worksheet I have a listing of stock records with ItemNumber, Date, StockLocation and NumberOnStock. When I make simple Pivot Table with ItemNumber and StockLocation as rows and select a Sum of "NumberOnStock" I would like to hide rows where the sum is "0". But this does not seem to be possible. Tried to follow the guidelines in the Microsoft article: http://office.microsoft.com/en-us/assistance/HP051998791033.aspx#Hide%20zero%20values%20in%20a%20PivotTable%20report But no luck :-( Mogens You could use conditional formatting on these cells - highlight the cells,...

Making data highlight in bold
Hi all, Thanks for other advice previously. This time I would like to do the following: I have columns of sales revenue data, I would like the rows tha contain the clients details (address, # etc) to highlight in bold whe the sales exceed $30000 in the revenue column. Would I use Conditional formatting for this? Thanks in advance for any help Cheers, Saxte -- Message posted from http://www.ExcelForum.com Hi Saxter! Select the data table Format > Conditional format Formula is: (Example) =$E5>30000 Format button Select format to taste OK OK -- Regards Norman Harker MVP (Excel...

Hiding formuals in cells question
Hi. I was wondering if there was a way to hide the formulas in cells without protecting the sheet. I am making something for other teachers at my school and I want to hide the formulas so they won't mess anything up on the spreadsheet. Any help would be greatly appreciated. Thanks! Hi not without protecting the sheet but why is this a problem for you? -- Regards Frank Kabel Frankfurt, Germany "JP" <JP@discussions.microsoft.com> schrieb im Newsbeitrag news:ABFE07BF-6087-4E24-814A-03AF8863A633@microsoft.com... > Hi. I was wondering if there was a way to hide the fo...

Not allowing Alpha Characters in a Numeric Formatted Column
I have a spreadsheet that we provide to vendors where they have to fill in their product data and send it back to us where we import this data into the products database. I have the spreadsheet set up the way I want it and have drop down boxes where applicable for data validation. I have several fields that i only want #'s. Sometimes integers, sometimes there may be a decimal. I set the formats accordingly. HOwever, you can still type the letter A or B or C etc... in there. I only want them to be able to type in #'s. In access I can use a mask. Is there anything similar in Exce...

Exchange-based List Server
We are looking at MS based list server products for our customer communications. We basically are looking for a solution where we can centrally maintain an email list, send messges with each receipient in TO: field (as opposed to BCC:), and hopefully a facility to purge dead email addresses from the list. Other that ReddFish we haven't found any solutiions. Does anyone on this board have any suggestions? Thanks! Theo ListBuilder? It will prevent your own mailserver from becoming blacklisted. Mass mailings are a dangerous business in that respect. Many ISP's consider emails...

Multiple Conditional Formatting rules
Can anyone tell me how I can set 20 rules for conditional formatting o one sheet? What I need to do is colour a spreadsheet with 15-20 colours accordin to a (different) spreadsheet full of numbers, so that eg 1 = red, 2 pink, 3 = blue, 4 = green..... etc... Any help would be much appreciated. Thanks -- Message posted from http://www.ExcelForum.com Hi this can only be done with VBA. Put the following code in your worksheet module Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Intersect(Target, Me.Range("A1:A100")) Is No...

display columns alphabetically
Columns used to display alphabetically left to right. It changed to now displaying numerically left to right. How do I change back to alphabetically? Go to Tools|Options and then in the General tab, uncheck R1C1 reference style. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=136818 Perfect, thanks for easing my f...

Chart connected to an Access data base
Hi guys, I have a worksheet with data connected to a Access Data base, 3 columns in total, one column with different dates (D/M/Y) and the other 2 with quantities; I want to create a Line chart with this data to compare quantity-1 and quatity-2, the problem is that the data is very detail, in days and I want to create a chart to compare the quantities but monthly, I could aggregate the data manually but since the data is coming from a database, is going to be updated daily, I want to create something more automatic. Second, I was talk that Microsoft Access doesn’t have a tool to crea...

hide my name
When replying to an email or creating one from scratch how do i hide my name in the "from" field...thx fred <fdbjrgetridofthis@wowway.com> wrote: > When replying to an email or creating one from scratch how do i hide > my name in the "from" field...thx Are you saying you want the reply to be anonymous? -- Brian Tillman [MVP-Outlook] no, I don't mind if my email is revealed.. just my name. I have tried by removing it from the "User Name:" field in the email account but it continues to show up. I have also removed from the header informati...

Convert Column Letters to Number
Hi! I'm looking for a function to convert a "large" column letters (up t 10 characters) to it's equivalent in number. e.g. Input=A Output=1 Input=AAA Output=703 Input=EXCEL Output=2,708,874 Any suggestions? Regards -- Message posted from http://www.ExcelForum.com Here's a VBA function that gives the results you want: Option Explicit Function ColumnToNumber(sText As String) As Variant Dim Bytes() As Byte Dim Letter As Long Dim Multiplier As Double Dim N As Double Dim Total As Double ColumnToNumber = CVErr(xlErrValue) If L...

Hiding and Un-Hiding Data fields
I am using a form for people to select data via dropdown menus and then having it run through a query to filter results. Is there a way that I can have certain feilds hidden until a toggle button or something of the like is clicked? Basically I want have three choices of buttons: Year, Quarter, and Month. What I ideally want to happen is when someone selects the button for year, a hidden drop down menu appears where they can then choose from the available years to filter through the query. I would say I am above average with using access, but I have no idea how to hide fields and then...

How do I hide gaps in an Excel 2007 chart?
I have a bar chart that is showing gaps and i don't want them. The select data > hidden and empty cell button does not give an option to hide gaps. I think by default they are hidden but if you even click on this to see what it does the gaps are shown and no way to turn off. Hi, If you hide the actual rows then with the Plot visible cells only setting on the gaps will be removed. In xl2007 the setting is on the Select Data dialog, Hidden/empty cells button. Cheers Andy Waynesch wrote: > I have a bar chart that is showing gaps and i don't want them. The s...

Charts based on 'virtual' names
Hi all, Just upgraded from Office 97 to Office 2007 (big jump!) and trying to fix the things that broke! I routinely make so-called "dynamic" charts in the traditional manner using ranges defined by formula which resolve to ranges of cells e.g. define 'Bias & Precision'!Samples as =OFFSET('Bias & Precision'!$A$2,0,0,COUNTA('Bias & Precision'!$A:$A)-1) I've had a couple of snags with these but it seems to still work as long as I watch the scope of the names. What seems to be very "broken" is the use of names which do not resolve...

Cannot add columns to custom views
I have a problem that is causing a major handicap. I cannot add any columns to any custom leads view that I create. No matter what, the only column that is displayed is the default key column (name) that is there when the view is created. When I click on add columns, the dialog box that is supposed to list columns that I can add to the view has no columns listed at all. I am logged in as the System Administrator when trying to accomplish this. Also, when I create a filter criteria it is never saved. I save it, but when I go back into it, it is gone. Any help would be GREATLY apprec...

Hiding a worksheet #3
I want to hide a worksheet. So, I went to the format menu and the "worksheet" option is grayed out. Why? And, how do I "ungray" it. Hi is your worksheet protected ? -- Regards Frank Kabel Frankfurt, Germany llong wrote: > I want to hide a worksheet. So, I went to the format menu and the > "worksheet" option is grayed out. Why? And, how do I "ungray" it. I think Frank meant to ask about the Workbook--not the worksheet. (Check under Tools|protection|Protect workbook or Unprotect workbook) My question is: Is there another sheet in the w...

VBA write macro change column with 3 number digits to 4 digits the
-- James Do you mean Sub Macro() Range("D1:D10").NumberFormat = "0000" End Sub -- Jacob "James C" wrote: > > -- > James Use the white space (here) to record details of your requirements - don't try to put it all in the message header. Pete On Jan 25, 1:51=A0pm, James C <Jam...@discussions.microsoft.com> wrote: > -- > James Thanks for your response -- James "Jacob Skaria" wrote: > Do you mean > > Sub Macro() > Range("D1:D10").NumberFormat = "00...

Hide images when Hiding Rows
I have inserted images into cells, but when I hide the column the images are not hidden with that column, they just get squished between the two cells next to the hidden cell. Is there a setting or way to tie the image to that cell so that it hides/unhides appropriately with the row? -- kurt ------------------------------------------------------------------------ kurt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35686 View this thread: http://www.excelforum.com/showthread.php?threadid=554712 Kurt You cannot insert an image into a cell. You can overlay ...

Hiding plot Area
I have a formatted output sheet that has a Pie Chart embedded. The data for the pie chart comes from a second sheet using 2 ranges B2:B6 & D2:D6 B2:B6 is always populated and contains labels. What I want to do is, when D2:D6 contains 0 in each cell (or Null), suppress the Pie chart but always show the Legend (which is driven by B2:B6) Many Thanks Adam Adam - All you see of the pie when all values are zero is a single line at the first slice. The legend still appears. To hide the line, double click on one of the wedges and on the Patterns tab, change the Border setting to None. ...

Repeating columns cuts off merged cells
Hi all, I hope you can help because I can't find a solution to this one. I've got a two page spreadsheet (side by side) and I'm trying to repeat columns A & B because their row headings on the left and should only appear when printed (split onto two pages) (as in print setup > columns to repeat at left > $A:$B), ...... but B57:I57 are merged and wrapped (another grief that merged wrapped cells don't auto height, but that's for another day) and so are B58:I58 and B59:I59 (footer things). ...... and when printed, B57:I57, etc. cuts off on the second page and...

how to hide an ActiveX control?
hi there i have an ActiveX control, written in C++, which has the OLEMISC_INVISIBLEATRUNTIME flag set, yet when i display the web page it is attached to, i get the familiar "red cross" graphic in the middle of the screen - how can i hide the control so nothing is displayed at all? tia bhu can u set the size of ur control as 1x1 pixels "bhu Boue vidya" <bhuvidya@yahoo.com.au> wrote in message news:1175703214.292931.252210@e65g2000hsc.googlegroups.com... > > hi there > > i have an ActiveX control, written in C++, which has the > OLEMISC_INVISIBLEATR...

Hiding rows with VBA
Hi all, In my sheet called "insertsheet I have this VBA: (thanks to some users of Google groups) Private Sub Worksheet_Change(ByVal Target As Range) Const nMAX As Long = 10 Dim nRows As Long Application.ScreenUpdating = False With Me With .Range("B8") If Intersect(Target(1), .Cells) Is Nothing Then Exit Sub nRows = .Value End With .Range(.Cells(1, 5), .Cells(1, _ .Columns.Count)).EntireColumn.Hidden = True .Range(Cells(1, 5), .Cells(1, _ ...

hiding rows
I have data in cells A7:DK394 which all have 0 in them until totals are added in other worksheets which then pullthrough. At the end of the week I have to Hide all rows from A7 to A394 that still have 0 in them as no totals have been added in other sheets so the customer has not posted with us. is there any way excell can recognise the cells that have no data against them and hide them automatically by pressing a button. or if they all start off hidden unhide as data is entered. Hopefully this makes sense many thanks for any help with this as again its to save me lots of...

Hide contacts
I must hide some contacts from GAL excepted for 2 user that must to see them and select to send email. If only 2 users need a Contact, why not have them create it in their Contacts folder? -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- "MauroR" <MauroR@discussions.microsoft.com> wrote in message news:DAC0E2E9-7389-49F0-8736-3B9251DBB274@microsoft.com... >I must hide some contacts from GAL excepted for 2 user that must to see >them > and select to send email. Th...

Query condition
Hi All, I have two tables for managing our sales orders. One has the order header info such as customer name, account number, etc and the other stores the order line info such as productID, unit price, qnty, etc. The tables have a one to many relationship and are joined using the OrderID. I need to create a query to list all the sales orders that include both productID 57 and 58 but i can't quite get my head round it! If i put "57 or 58" in the criteria box it gives me all of the orders that include either 57 or 58 whereas i need to know the orders that include both! T...