Hiding Column based on If Condition

[asr]

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?

[Rajni Ravindran]

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" <[email protected]> wrote in message
news:<[email protected]>...

>

> 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...o&userid=23751

> View this thread: http://www.excelforum.com/showthread...hreadid=374122

>

"asr" <[email protected]> wrote in message
news:[email protected]...
>
> 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...o&userid=23751
> View this thread: http://www.excelforum.com/showthread...hreadid=374122
>

[Dave Peterson]

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...o&userid=23751
> View this thread: http://www.excelforum.com/showthread...hreadid=374122

--

Dave Peterson