excel formulas

A

Annerobbo

Hi

I need to write a formula that if a cell on a worksheet contains a number a
cell on a different worksheet within the workbook returns a different number.
There are 5 different numbers - can someone help please.

Thanks in advance

Anne
 
B

Bob Phillips

Bit light on detail. Essentially it is

=(A1=1,Sheet2!A1,IF(A1=2,Sheet2!B1,IF(...)))
 
A

Annerobbo

:

Hi I probably wasn't very clear!!!

On sheet 1 in cell E7 to E700 it may have A0014A to A0014G. Depending what
is in that cell on sheet 3 in the same workbook I need to convert this to a
different number eg A0014A will become W123, A0014B will become ENG, A0014C
will become FF37, A0014D will become FF46, A0014E will become Route 3, A0014F
will become Piccadilly and A0014G will become SWIM 111. Hope this is more
explainatory.

Thanks
 
B

Bob Phillips

=IF(Sheet3!E7="A0014A","W123",IF(Sheet3!E7="A0014B,"ENG", ...

and so on, copy down to E700
 
A

Annerobbo

Tried that

Like this??
=IF(Timesheets!E7="A0014A","W123",IF(Timesheets!E7="A0014B","ENG",IF(Timesheets!E7="A0014C","FF37",IF(Timesheets!E7="A0014D","FF46,IF(Timesheets!E7="A0014E","ROUTE 3",IF(Timesheets!E7="A0014F","PICCADDILLY")

And all I got was

The formula you typed contains an error.

Should I have more ) in??
 
R

Ron Rosenfeld

:

Hi I probably wasn't very clear!!!

On sheet 1 in cell E7 to E700 it may have A0014A to A0014G. Depending what
is in that cell on sheet 3 in the same workbook I need to convert this to a
different number eg A0014A will become W123, A0014B will become ENG, A0014C
will become FF37, A0014D will become FF46, A0014E will become Route 3, A0014F
will become Piccadilly and A0014G will become SWIM 111. Hope this is more
explainatory.

Thanks
Click to expand...

Try this:

=IF(COUNTIF(ConvTbl,Sheet1!E7)=0,"",VLOOKUP(Sheet1!E7,ConvTbl,2,0))

and copy down 693 rows.

ConvTbl is a named range which looks like:

A0014A W123
A0014B ENG
A0014C FF37
A0014D FF46
A0014E Route 3
A0014F Piccadilly
A0014G SWIM 111


You may substitute a range reference, or you may substitute the array constant.

Also, depending on your regional settings, you may need to substitute
semicolons for the commas in the above formula.




--ron
 
M

Max

Perhaps try a VLOOKUP ?
(averts the nested IF limit, and easier to maintain)

In say, Sheet1
Set-up a reference table
in cols A and B, from row1 down:

A0014A W123
A0014B ENG
A0014C FF37
etc

Then we could use in say, Sheet2's B2:

=IF(ISNA(MATCH(Timesheets!E7,Sheet1!$A:$A,0)),
"",VLOOKUP(Timesheets!E7,Sheet1!$A:$B,2,0))

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Annerobbo said:
Tried that

Like this??
Click to expand...
=IF(Timesheets!E7="A0014A","W123",IF(Timesheets!E7="A0014B","ENG",IF(Timeshe
ets!E7="A0014C","FF37",IF(Timesheets!E7="A0014D","FF46,IF(Timesheets!E7="A00
14E","ROUTE 3",IF(Timesheets!E7="A0014F","PICCADDILLY")
 
B

Bob Phillips

Like this

=IF(Timesheets!E7="A0014A","W123",IF(Timesheets!E7="A0014B","ENG",IF(Timeshe
ets!E7="A0014C","FF37",IF(Timesheets!E7="A0014D","FF46",IF(Timesheets!E7="A0
014E","ROUTE 3",IF(Timesheets!E7="A0014F","PICCADDILLY"))))))

--
HTH

Bob Phillips

Annerobbo said:
Tried that

Like this??
Click to expand...
=IF(Timesheets!E7="A0014A","W123",IF(Timesheets!E7="A0014B","ENG",IF(Timeshe
ets!E7="A0014C","FF37",IF(Timesheets!E7="A0014D","FF46,IF(Timesheets!E7="A00
14E","ROUTE 3",IF(Timesheets!E7="A0014F","PICCADDILLY")
 
Top