Excel functions help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'd like to achive an excel functions of the following scenario

Column F (value), Column J (Code) A,O,and P, Column M, Column N, Column

- If Column J with code "A", return value in column "F" TO column "P
- If column J with code "O", return value in column "F" TO column "N
- If column J with code "P", return value in column "F" TO column "M

Thank
Sothea
 
Hi Sothear!

In all cases the formula you want goes in you "TO column.

So the first requirement involves a formula in column P

Example of column P formula
=IF(J1="A",F1,"")

Second:
Example of column N formula
=IF(J1="O",F1,"")

Third:
Example of column M formula
=IF(J1="P",F1,"")

I've assumed that where the conditions are not met you want to return
a "" (making the cell *look" empty)
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Sothear,

You should use three functions to do that:
In P1 place =IF(J1="A",F1,"")
In N1 place =IF(J1="O",F1,"")
In M1 place =IF(J1="P",F1,"")

Then copy down the three formulas as far as you got data in columns F and J.

Kind regards,

Marcel Kreijne
Quandan - Steunpunt voor spreadsheetgebruikers
(Quandan - dutch supportsite for spreadsheetusers)
www.quandan.nl
 
Mark

Addition help..

I'd like to able to type in code: A or O or P in column J, and value in column F would shift to each destination column automatically

Thank
Sothea


----- Mark Graesser wrote: ----

Sothear
You can do this by entering IF statements in the cells in P, M, and

Assuming row

in P2 enter =IF(J2="A",F2,""
in N2 enter =IF(J2="O",F2,""
in M2 enter =IF(J2="P",F2,""

The "" will make the cell appear blank when the condition is not met

Regards
Mark Graesse
(e-mail address removed)

----- Sothear wrote: ----

I'd like to achive an excel functions of the following scenario

Column F (value), Column J (Code) A,O,and P, Column M, Column N, Column

- If Column J with code "A", return value in column "F" TO column "P
- If column J with code "O", return value in column "F" TO column "N
- If column J with code "P", return value in column "F" TO column "M

Thank
Sothea
 
Hello Marcel

Additional help..

I'd like to able to type in code: A or O or P in column J, and value in column F would shift to each destination column automatically

Thank
Sothea


----- Quandan - Marcel Kreijne wrote: ----

Sothear

You should use three functions to do that
In P1 place =IF(J1="A",F1,""
In N1 place =IF(J1="O",F1,""
In M1 place =IF(J1="P",F1,""

Then copy down the three formulas as far as you got data in columns F and J

Kind regards

Marcel Kreijn
Quandan - Steunpunt voor spreadsheetgebruiker
(Quandan - dutch supportsite for spreadsheetusers
www.quandan.n
 
Hello Norman,

Additional help...

I'd like to able to type in code: A or O or P in column J, and value in column F would shift to each destination column automatically?

Thanks
Sothear

----- Norman Harker wrote: -----

Hi Sothear!

In all cases the formula you want goes in you "TO column.

So the first requirement involves a formula in column P

Example of column P formula
=IF(J1="A",F1,"")

Second:
Example of column N formula
=IF(J1="O",F1,"")

Third:
Example of column M formula
=IF(J1="P",F1,"")

I've assumed that where the conditions are not met you want to return
a "" (making the cell *look" empty)
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Sothear
If I read you correctly, this appears to be the same question as before except that you want to clear the value from column F. If you want to actually clear the cell in column F after transfering the data to the other column you will need to use VBA

Regards
Mark Graesse
(e-mail address removed)

----- Sothear wrote: ----


Mark

Addition help..

I'd like to able to type in code: A or O or P in column J, and value in column F would shift to each destination column automatically

Thank
Sothea



----- Mark Graesser wrote: ----

Sothear
You can do this by entering IF statements in the cells in P, M, and

Assuming row

in P2 enter =IF(J2="A",F2,""
in N2 enter =IF(J2="O",F2,""
in M2 enter =IF(J2="P",F2,""

The "" will make the cell appear blank when the condition is not met

Regards
Mark Graesse
(e-mail address removed)

----- Sothear wrote: ----

I'd like to achive an excel functions of the following scenario

Column F (value), Column J (Code) A,O,and P, Column M, Column N, Column

- If Column J with code "A", return value in column "F" TO column "P
- If column J with code "O", return value in column "F" TO column "N
- If column J with code "P", return value in column "F" TO column "M

Thank
Sothea
 
I'd like to achive an excel functions of the following scenario

Column F (value), Column J (Code) A,O,and P, Column M, Column N, Column

- If Column J with code "A", return value in column "F" TO column "P
- If column J with code "O", return value in column "F" TO column "N
- If column J with code "P", return value in column "F" TO column "M

I'd like to able to type in code: A or O or P in column J, and value in column F would shift to each destination column automatically, without having to retype different formula

e.g...with one the same and single formula in three comlumns, type code A, value of column F would automatically shift to column P and soforth....


Thank
Sothea
 
Sothear said:
I'd like to achive an excel functions of the following scenario:

Column F (value), Column J (Code) A,O,and P, Column M, Column N,
Column P

- If Column J with code "A", return value in column "F" TO
column "P"
- If column J with code "O", return value in column "F" TO
column "N"
- If column J with code "P", return value in column "F" TO
column "M"


I'd like to able to type in code: A or O or P in column J, and value
in column F would shift to each destination column automatically,
without having to retype different formula.

e.g...with one the same and single formula in three comlumns, type
code A, value of column F would automatically shift to column P and
soforth.....


Thanks
Sothear
Click to expand...

Hi Sothear
you either have to use the three different formulas (one per column)
the Morman, Mark, etc. suggested or you'll have to use VBA. One
question: What do you mean with 'shift' Do you want to delete the value
in column F. If yes you definetly will need VBA to achieve this. e.g.
process the worksheet change event.

Frank
 
Hello Frank

What I mean by "shift" is the value in column F stay where it is

code: P=Paid O=Outstanding A= Adjustmen

e.g..(1
COD
Column F Column J Column M Column N Column

10.00 P 10.0

If for example I'd like change to code 'O' in an existing cell with same formula, the 10.00 would shift to column

e.g...(2

COD
Column F Column J Column M Column N Column

10.00 O 10.0

e.g...(3

COD
Column F Column J Column M Column N Column

10.00 A 10.0

I understand all three folks are correct, although I've missed inform somewhat. However, the above sample what i'd lik
see. I have this formula that work with number =IF($E1=column()-column($E1)-1,$D1,"") using code: 3, 4,
It works similar sample. The only different is that I'd to use Alpha instead of Number

Hope this helpul..

Many thanks Frank, Mark, Norman and Marcel that helped.
sothea



----- Frank Kabel wrote: ----

Sothear wrote
I'd like to achive an excel functions of the following scenario
column "P
- If column J with code "O", return value in column "F" T
column "N
- If column J with code "P", return value in column "F" T
column "M
in column F would shift to each destination column automatically
without having to retype different formula
code A, value of column F would automatically shift to column P an
soforth....
Sothea
Click to expand...

Hi Sothea
you either have to use the three different formulas (one per column
the Morman, Mark, etc. suggested or you'll have to use VBA. On
question: What do you mean with 'shift' Do you want to delete the valu
in column F. If yes you definetly will need VBA to achieve this. e.g
process the worksheet change event

Fran
 
Sothear
Take another look at the three orgiginal responses. They all seem to be exactly what you are looking for

Looking at the number based formula you posted, it seems you may be looking for one formula that you can put in all of the cells. I'm sure that a single formula can be devised, but there isn't any reason to do so. By using the three different formulas you don't need to play around with the COLUMN function, and you can accomodate text values without needing a MATCH, CHOOSE or LOOKUP

Regards
Mark Graesse
(e-mail address removed)


----- Sothear wrote: ----

Hello Frank

What I mean by "shift" is the value in column F stay where it is

code: P=Paid O=Outstanding A= Adjustmen

e.g..(1
COD
Column F Column J Column M Column N Column

10.00 P 10.0

If for example I'd like change to code 'O' in an existing cell with same formula, the 10.00 would shift to column

e.g...(2

COD
Column F Column J Column M Column N Column

10.00 O 10.0

e.g...(3

COD
Column F Column J Column M Column N Column

10.00 A 10.0

I understand all three folks are correct, although I've missed inform somewhat. However, the above sample what i'd lik
see. I have this formula that work with number =IF($E1=column()-column($E1)-1,$D1,"") using code: 3, 4,
It works similar sample. The only different is that I'd to use Alpha instead of Number

Hope this helpul..

Many thanks Frank, Mark, Norman and Marcel that helped.
sothea





----- Frank Kabel wrote: ----

Sothear wrote
I'd like to achive an excel functions of the following scenario
column "P
- If column J with code "O", return value in column "F" T
column "N
- If column J with code "P", return value in column "F" T
column "M
in column F would shift to each destination column automatically
without having to retype different formula
code A, value of column F would automatically shift to column P an
soforth....
Sothea
Click to expand...

Hi Sothea
you either have to use the three different formulas (one per column
the Morman, Mark, etc. suggested or you'll have to use VBA. On
question: What do you mean with 'shift' Do you want to delete the valu
in column F. If yes you definetly will need VBA to achieve this. e.g
process the worksheet change event

Fran
 
Back
Top