Problem With IF Function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SkippyPB - 25 Jul 2007 21:02 GMT

I have a workbook (MS Office Excel 2003 SP2) that contains two
worksheets.

In worksheet 1, I have data in column B that I want to appear in
worksheet 2 column A. As this is a worksheet in which the users will
enter data, I want the cursor to automatically skip to column B on
worksheet 2 when that worksheet is active.

For the first requirement (assuming the worksheet names are WK 1 and
WK 2), I entered the following IF in WK 2 , A2 (A1 has a heading):

=IF('WK 1'!B2="","",'WK 1'!B2)

This did indeed copy the data in WK 1 B2 into WK 2 A2 but it also
replaced the function, thus I can't copy it to the rest of the column
A cells. Can't remember if I've ever seen this happen before and I
certainly don't know how to correct it.

In addition, I have no idea how to make the cursor skip over a column.
I would guess it has something to do with protection, but the HELP
function isn't helping!

Thanks for any advice anyone can give me.

Regards,
////
(o o)
-oOO--(_)--OOo-

** Norm's Greetings on US TV Show "Cheers" **

SAM: "What'll you have Normie?"
NORM: "Well, I'm in a gambling mood Sammy. I'll take a glass of whatever
comes out of that tap."
SAM: "Looks like beer, Norm."
NORM: "Call me Mister Lucky."
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve

Reply to this Message

CLR - 26 Jul 2007 13:24 GMT

It sounds to me like a macro is being triggered and doing a Copy >
PasteSPecial > Values on your cell. Try opening the workbook with macros
disabled and see if it still acts the same.

Vaya con Dios,
Chuck, CABGx3

> I have a workbook (MS Office Excel 2003 SP2) that contains two
> worksheets.
[quoted text clipped - 37 lines]
>
> Steve

Reply to this Message

SkippyPB - 26 Jul 2007 17:05 GMT

>It sounds to me like a macro is being triggered and doing a Copy >
>PasteSPecial > Values on your cell. Try opening the workbook with macros
>disabled and see if it still acts the same.
>
>Vaya con Dios,
>Chuck, CABGx3

Thanks for that tip. That did the trick. However, there is only one
macro in the workbook and it is one I wrote and has nothing to do with
the IF function. I did find that with macroes disabled, I could enter
the IF function, it would perform as it is supposed to and then I
could save the workbook and re-open it with macroes enabled and things
looked alright. Very odd.

>> I have a workbook (MS Office Excel 2003 SP2) that contains two
>> worksheets.
[quoted text clipped - 21 lines]
>>
>> Steve

Regards,

////
(o o)
-oOO--(_)--OOo-

** Norm's Greetings on US TV Show "Cheers" **

SAM: "What'll you have Normie?"
NORM: "Well, I'm in a gambling mood Sammy. I'll take a glass of whatever
comes out of that tap."
SAM: "Looks like beer, Norm."
NORM: "Call me Mister Lucky."
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve

Reply to this Message

CLR - 26 Jul 2007 17:24 GMT

Glad you got it sorted..............thanks for the feedback

Vaya con Dios,
Chuck, CABGx3

> >It sounds to me like a macro is being triggered and doing a Copy >
> >PasteSPecial > Values on your cell. Try opening the workbook with macros
[quoted text clipped - 54 lines]
>
> Steve

Reply to this Message

SkippyPB - 26 Jul 2007 21:49 GMT

>Glad you got it sorted..............thanks for the feedback
>
>Vaya con Dios,
>Chuck, CABGx3

Thanks for your help. Now another question. As the IF function is
copied to the second sheet column a for 65000+ rows, it makes the
spreadsheet pretty huge. So, if possible I'd like to replace all
those IFs with a macro that looks at a cell in sheet 1 and puts data
in sheet 2 when the cursor is in column A. Something like:

Private Sub Worksheet_Change(ByVal Target as Excel.Range)
On Error goto ErrHandler
If Target.Column = 1 Then
Application.EnableEvents = False
Target.Value = ???????
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Where the ??????? are is what I'm unsure of. I want to reference
sheet 1, column B same row as the cursor is in in sheet 2.

Can this be done?

>> >It sounds to me like a macro is being triggered and doing a Copy >
>> >PasteSPecial > Values on your cell. Try opening the workbook with macros
[quoted text clipped - 37 lines]
>>
>> Steve

Thanks.

////
(o o)
-oOO--(_)--OOo-

** Norm's Greetings on US TV Show "Cheers" **

SAM: "What'll you have Normie?"
NORM: "Well, I'm in a gambling mood Sammy. I'll take a glass of whatever
comes out of that tap."
SAM: "Looks like beer, Norm."
NORM: "Call me Mister Lucky."
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve

Reply to this Message

Rate this thread: