IF HELP

M

MIKE F

I am trying to write a formula looking at the last value of an number, and if
that
number equals a certain value, copy a different reference cell into a new
cell.

Example
A B C D E
32002.001 ACT A 3858233.29 =IF(A1.....) =IF(A1.....)
32002.002 ACT B 2864212.29

I AM TRYING TO WRITE A FORMULA THAT WILL ONLY LOOK AT THE LAST NUMBER OF THE
A COLUMN VALUE, AND IF THAT VALUE IS MET, WILL COPY THE VALUE FROM THE C
COLUMN INTO THE FORMULA CELL OR OTHERWISE LEAVE THE FORMULA CELL BLANK.
 
F

FSt1

hi
formulas return values. they cannot perform actions like copy, delete, other.
my guess is that you are looking at a macro of some sort. rethink you
project and
repost.

regards
FSt1
 
S

Sandy Mann

If you mean the last digit in the cell in Column A then try in Column D:

=IF(RIGHT(A2)="1",D2,"")

I don't understand what you are trying to do in Column E

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

I just noticed that you want the value in Column C copied, change the D2
into C2

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
M

MIKE F

Thank you for your help. What I am doing in columns F thru N is the same
formula looking and a different ending number, 2,3..... The copy cell will
always be the same column.

What i have written is
=IF(RIGHT($A2)="1", $C2,"") IN COLUMN C IN THE EXAMPLE SHOWN
AND THEN IN THE NEXT COLUMN IT WOULD READ
=IF(RIGHT($A2)="2",$C2,"")

I NEED TO APPLY THIS FORMULA TO COLUMNS F THRU N IN MY ACTUAL WORKBOOK AND
ROWS 295 THRU 3559. AM I USING THE CORRECT SYMBOLS?
 
S

Sandy Mann

Yes that will work. An alternative in F2 would be:

=IF(--RIGHT($A2)=COLUMN()-5,$C2,"")

which can then be dragged to all other cells.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
M

MIKE F

Thank you so much for your help. It was much easier to write the correct
formula than to go through all 3500 individually.
 
S

Sandy Mann

You are very welcome. I assume that you adjusted the formula to suit your
data because looking at it again I see that when I imported your data, *ACT
A* in Column B became split between Columns B & C so the formula does not
suit your data.

With the first formula in D2 then the formula should be:

=IF(--RIGHT($A2)=COLUMN()-3,$B2,"")

If there is any chance that you will insert a new column before column D
then the formula:

=IF(--RIGHT($A2)=COLUMN()-COLUMN($C$1),$B2,"")

Will guard against the formula having to be changed.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
Top