setting cell values based on a 3rd cell


B

Bob

Hi - can anyone help me with an Excel 2003 question? I'm working with 3 cells:
a1 [format m/d/yyyy h:mm] - contents= 1/1/2010
b1 [format m/d/yyyy h:mm] - contents= null
c1 [format text] - contents "CLOSED"
All column & cell values are loaded in from a csv file. I can't [i.e. don't
want to] place a formula in cell b1 'cause I'll lose it on the next import,
so I'm trying to place a formula in a 4th cell [d1] that will a) see if c1
contains a value of "CLOSED", b) if true, take the date/time from a1, and c)
place it in b1. I've tried variations using IF and EXACT formulae & had no
success; e.g.
=IF(c1="CLOSED",b1=c1
=IF(c1="CLOSED",((VALUE(b1=A1))
Any suggestions on hot to get it done? thanks!!!! - bob
 
Ad

Advertisements

D

Dave Peterson

First, a formula can't change the value in another cell.

But you could create a formula in D1 and then just move (drag) into B1:

=if(c1="closed",a1,"")

=============
Another option would be to record a macro when you import your text file. Then
have that macro add the formula into the cells in column B.


=IF(c1="CLOSED",b1=c1 SHOULD READ =IF(c1="CLOSED",b1=a1

Bob said:
Hi - can anyone help me with an Excel 2003 question? I'm working with 3 cells:
a1 [format m/d/yyyy h:mm] - contents= 1/1/2010
b1 [format m/d/yyyy h:mm] - contents= null
c1 [format text] - contents "CLOSED"
All column & cell values are loaded in from a csv file. I can't [i.e. don't
want to] place a formula in cell b1 'cause I'll lose it on the next import,
so I'm trying to place a formula in a 4th cell [d1] that will a) see if c1
contains a value of "CLOSED", b) if true, take the date/time from a1, and c)
place it in b1. I've tried variations using IF and EXACT formulae & had no
success; e.g.
=IF(c1="CLOSED",b1=c1
=IF(c1="CLOSED",((VALUE(b1=A1))
Any suggestions on hot to get it done? thanks!!!! - bob
 
B

Bob

Thank you, Dave!! sounds like I probably need to go the macro route.....

Dave Peterson said:
First, a formula can't change the value in another cell.

But you could create a formula in D1 and then just move (drag) into B1:

=if(c1="closed",a1,"")

=============
Another option would be to record a macro when you import your text file. Then
have that macro add the formula into the cells in column B.


=IF(c1="CLOSED",b1=c1 SHOULD READ =IF(c1="CLOSED",b1=a1

Bob said:
Hi - can anyone help me with an Excel 2003 question? I'm working with 3 cells:
a1 [format m/d/yyyy h:mm] - contents= 1/1/2010
b1 [format m/d/yyyy h:mm] - contents= null
c1 [format text] - contents "CLOSED"
All column & cell values are loaded in from a csv file. I can't [i.e. don't
want to] place a formula in cell b1 'cause I'll lose it on the next import,
so I'm trying to place a formula in a 4th cell [d1] that will a) see if c1
contains a value of "CLOSED", b) if true, take the date/time from a1, and c)
place it in b1. I've tried variations using IF and EXACT formulae & had no
success; e.g.
=IF(c1="CLOSED",b1=c1
=IF(c1="CLOSED",((VALUE(b1=A1))
Any suggestions on hot to get it done? thanks!!!! - bob
 
Ad

Advertisements

D

Dave Peterson

When I have to do this kind of stuff, I'll record a macro when I open the text
file. This will make sure that I get the right layout/syntax for each of the
fields.

After that, it's pretty straight forward to add the formulas (or even convert
them to values???).

If you need help, you can post the recorded code and a few more details...

Does the file name stay the same or can it vary?

Does the imported data go into a specific location (in the activeworkbook???) or
can it be left in the workbook that was created when you opened the text file?

If the extension is .csv, then it's better to rename it .txt so that your code
can control how each field should be treated.
Thank you, Dave!! sounds like I probably need to go the macro route.....

Dave Peterson said:
First, a formula can't change the value in another cell.

But you could create a formula in D1 and then just move (drag) into B1:

=if(c1="closed",a1,"")

=============
Another option would be to record a macro when you import your text file. Then
have that macro add the formula into the cells in column B.


=IF(c1="CLOSED",b1=c1 SHOULD READ =IF(c1="CLOSED",b1=a1

:

Hi - can anyone help me with an Excel 2003 question? I'm working with 3 cells:
a1 [format m/d/yyyy h:mm] - contents= 1/1/2010
b1 [format m/d/yyyy h:mm] - contents= null
c1 [format text] - contents "CLOSED"
All column & cell values are loaded in from a csv file. I can't [i.e. don't
want to] place a formula in cell b1 'cause I'll lose it on the next import,
so I'm trying to place a formula in a 4th cell [d1] that will a) see if c1
contains a value of "CLOSED", b) if true, take the date/time from a1, and c)
place it in b1. I've tried variations using IF and EXACT formulae & had no
success; e.g.
=IF(c1="CLOSED",b1=c1
=IF(c1="CLOSED",((VALUE(b1=A1))
Any suggestions on hot to get it done? thanks!!!! - bob
 

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