Problem With IF Function

S

SkippyPB

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
 
C

CLR

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
 
S

SkippyPB

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.

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
 
C

CLR

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

Vaya con Dios,
Chuck, CABGx3
 
S

SkippyPB

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?

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top