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

Bit light on detail. Essentially it is

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

--
HTH

Bob Phillips

"Annerobbo" wrote in message
...
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

"Annerobbo" wrote:

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

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

and so on, copy down to E700


--
HTH

Bob Phillips

"Annerobbo" wrote in message
...


"Annerobbo" wrote:

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

Tried that

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

And all I got was

The formula you typed contains an error.

Should I have more ) in??

On Wed, 21 Sep 2005 02:52:03 -0700, Annerobbo
wrote:



"Annerobbo" wrote:

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

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

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" wrote in message
...

Tried that

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="A00
14E","ROUTE 3",IF(Timesheets!E7="A0014F","PICCADDILLY")

And all I got was

The formula you typed contains an error.

Should I have more ) in??

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" wrote in message
...

Tried that

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="A00
14E","ROUTE 3",IF(Timesheets!E7="A0014F","PICCADDILLY")

And all I got was

The formula you typed contains an error.

Should I have more ) in??