Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 28th 06, 10:42 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 92
Default Array as a "named range" - formula ok in cells, but error as "named range"

I want to have array formula as a named range, but Excel doesn't accept
this formula. Maybe it is the "&" sign, or? How should I write? I have
Swedish signs, therefore use ";" instead of "," etc, I guess.

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"}


THIS worksheet formula is accepted into an ordinary cell.. I recieve
"Others" in the cell:

=LOOKUP("Company";{"Telecom";"Public & Healthcare";"Industrial &
Utilities";"Banking & Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"})


So - any idea how it should be as a named range formula?

Happy to all suggestions,
Tskogstrom


  #2   Report Post  
Old December 28th 06, 10:53 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 196
Default Array as a "named range" - formula ok in cells, but error as "named range"

Hi Tskogstrom

If I was doing that in the UK, I would write it as:

={"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others";"Company","Other s"}

You may have to swap the "," and ";" around. Basically, it forms a
grid where the columns are separated by "," and the rows by ";".

Hope this helps!

Richard


tskogstrom wrote:

I want to have array formula as a named range, but Excel doesn't accept
this formula. Maybe it is the "&" sign, or? How should I write? I have
Swedish signs, therefore use ";" instead of "," etc, I guess.

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"}


THIS worksheet formula is accepted into an ordinary cell.. I recieve
"Others" in the cell:

=LOOKUP("Company";{"Telecom";"Public & Healthcare";"Industrial &
Utilities";"Banking & Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"})


So - any idea how it should be as a named range formula?

Happy to all suggestions,
Tskogstrom


  #3   Report Post  
Old December 28th 06, 11:11 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 196
Default Array as a "named range" - formula ok in cells, but error as "named range"

Or possibly this:

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company","Telecom";"P&H";"Indu stry";"B&I";"R&L";"Othe*rs";"Others"}


I've just taken out the };{ from the middle (this was causing it to
error), and replaced the semi-colon between "Company" and "Telecom"
with a comma (",").

Hope this helps!

Richard


RichardSchollar wrote:

Hi Tskogstrom

If I was doing that in the UK, I would write it as:

={"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others";"Company","Other s"}

You may have to swap the "," and ";" around. Basically, it forms a
grid where the columns are separated by "," and the rows by ";".

Hope this helps!

Richard


tskogstrom wrote:

I want to have array formula as a named range, but Excel doesn't accept
this formula. Maybe it is the "&" sign, or? How should I write? I have
Swedish signs, therefore use ";" instead of "," etc, I guess.

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"}


THIS worksheet formula is accepted into an ordinary cell.. I recieve
"Others" in the cell:

=LOOKUP("Company";{"Telecom";"Public & Healthcare";"Industrial &
Utilities";"Banking & Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"})


So - any idea how it should be as a named range formula?

Happy to all suggestions,
Tskogstrom


  #4   Report Post  
Old December 28th 06, 12:42 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 92
Default Array as a "named range" - formula ok in cells, but error as "named range"

Hi,
thanks for trying. I have now changed national settings to UK and this
is OK:
{"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"}

Still my Lookup formula doesn't work - BUT nor in the worksheet:

In the worksheet cell this formula produce "2" as it should:
=LOOKUP("b",{"a",1;"b",2;"c",3})

Shouldn't this formula in that case produce "P&H"? It doesn't, whatever
I write as "lookup_value", it gives "Others2" as result. The same I
recieve from the named range with same syntax:

=LOOKUP("Public & Healthcare",{"Telecom","Telecom";"Public &
Healthcare","P&H";"Industrial & Utilities","Industry";"Banking &
Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"})

Why "Others2" ?What have I missed??

/regards
tskogstrom




RichardSchollar skrev:

Or possibly this:

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company","Telecom";"P&H";"Indu stry";"B&I";"R&L";"Othe*rs";"Others"}


I've just taken out the };{ from the middle (this was causing it to
error), and replaced the semi-colon between "Company" and "Telecom"
with a comma (",").

Hope this helps!

Richard


RichardSchollar wrote:

Hi Tskogstrom

If I was doing that in the UK, I would write it as:

={"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others";"Company","Other s"}

You may have to swap the "," and ";" around. Basically, it forms a
grid where the columns are separated by "," and the rows by ";".

Hope this helps!

Richard


tskogstrom wrote:

I want to have array formula as a named range, but Excel doesn't accept
this formula. Maybe it is the "&" sign, or? How should I write? I have
Swedish signs, therefore use ";" instead of "," etc, I guess.

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"}


THIS worksheet formula is accepted into an ordinary cell.. I recieve
"Others" in the cell:

=LOOKUP("Company";{"Telecom";"Public & Healthcare";"Industrial &
Utilities";"Banking & Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"})


So - any idea how it should be as a named range formula?

Happy to all suggestions,
Tskogstrom


  #5   Report Post  
Old December 28th 06, 12:57 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 196
Default Array as a "named range" - formula ok in cells, but error as "named range"

From Excel help (and I didn't consider this possibility - my apologies)

Important The values in array must be placed in ascending order:
....,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not
give the correct value. Uppercase and lowercase text are equivalent.

Which I presume is why you are getting an incorrect result.

You can correct this by using the VLOOKUP form (at least the following
worked for me):

=VLOOKUP("Public & Healthcare",{"Telecom","Telecom";"Public &
Healthcare","P&H";"Industrial & Utilities","Industry";"Banking &
Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"},2,0)

Richard


tskogstrom wrote:

Hi,
thanks for trying. I have now changed national settings to UK and this
is OK:
{"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"}

Still my Lookup formula doesn't work - BUT nor in the worksheet:

In the worksheet cell this formula produce "2" as it should:
=LOOKUP("b",{"a",1;"b",2;"c",3})

Shouldn't this formula in that case produce "P&H"? It doesn't, whatever
I write as "lookup_value", it gives "Others2" as result. The same I
recieve from the named range with same syntax:

=LOOKUP("Public & Healthcare",{"Telecom","Telecom";"Public &
Healthcare","P&H";"Industrial & Utilities","Industry";"Banking &
Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"})

Why "Others2" ?What have I missed??

/regards
tskogstrom




RichardSchollar skrev:

Or possibly this:

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company","Telecom";"P&H";"Indu stry";"B&I";"R&L";"Othe*rs";"Others"}


I've just taken out the };{ from the middle (this was causing it to
error), and replaced the semi-colon between "Company" and "Telecom"
with a comma (",").

Hope this helps!

Richard


RichardSchollar wrote:

Hi Tskogstrom

If I was doing that in the UK, I would write it as:

={"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others";"Company","Other s"}

You may have to swap the "," and ";" around. Basically, it forms a
grid where the columns are separated by "," and the rows by ";".

Hope this helps!

Richard


tskogstrom wrote:

I want to have array formula as a named range, but Excel doesn't accept
this formula. Maybe it is the "&" sign, or? How should I write? I have
Swedish signs, therefore use ";" instead of "," etc, I guess.

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"}


THIS worksheet formula is accepted into an ordinary cell.. I recieve
"Others" in the cell:

=LOOKUP("Company";{"Telecom";"Public & Healthcare";"Industrial &
Utilities";"Banking & Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"})


So - any idea how it should be as a named range formula?

Happy to all suggestions,
Tskogstrom




  #6   Report Post  
Old December 28th 06, 12:59 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 92
Default Array as a "named range" - formula ok in cells, but error as "named range"

Hi,

I tested with a reference to a two column (Lookup(array)) instead and
it gives the same result. I thought at first it had something to do
with the "&" or blanks, but "Telecom" also give "Others". Also tested
Lookup(vector), definingt the two columns singlehanded but with same
result.

It must have something to do with the usage of Lookup formula, doesn't
it?

/regards
tskogstrom



RichardSchollar skrev:

Or possibly this:

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company","Telecom";"P&H";"Indu stry";"B&I";"R&L";"Othe*rs";"Others"}


I've just taken out the };{ from the middle (this was causing it to
error), and replaced the semi-colon between "Company" and "Telecom"
with a comma (",").

Hope this helps!

Richard


RichardSchollar wrote:

Hi Tskogstrom

If I was doing that in the UK, I would write it as:

={"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others";"Company","Other s"}

You may have to swap the "," and ";" around. Basically, it forms a
grid where the columns are separated by "," and the rows by ";".

Hope this helps!

Richard


tskogstrom wrote:

I want to have array formula as a named range, but Excel doesn't accept
this formula. Maybe it is the "&" sign, or? How should I write? I have
Swedish signs, therefore use ";" instead of "," etc, I guess.

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"}


THIS worksheet formula is accepted into an ordinary cell.. I recieve
"Others" in the cell:

=LOOKUP("Company";{"Telecom";"Public & Healthcare";"Industrial &
Utilities";"Banking & Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"})


So - any idea how it should be as a named range formula?

Happy to all suggestions,
Tskogstrom


  #7   Report Post  
Old December 28th 06, 01:05 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 1,726
Default Array as a "named range" - formula ok in cells, but error as "named range"

It's the order

=LOOKUP("Public & Healthcare",{"Banking &
Insurance","B&I";"Company","Others";"Industrial &
Utilities","Industry";"Other","Others2";"Public & Healthcare","P&H";"Retail
& Logistics","R&L";"Telecom","Telecom"})

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"tskogstrom" wrote in message
ps.com...
Hi,
thanks for trying. I have now changed national settings to UK and this
is OK:
{"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"}

Still my Lookup formula doesn't work - BUT nor in the worksheet:

In the worksheet cell this formula produce "2" as it should:
=LOOKUP("b",{"a",1;"b",2;"c",3})

Shouldn't this formula in that case produce "P&H"? It doesn't, whatever
I write as "lookup_value", it gives "Others2" as result. The same I
recieve from the named range with same syntax:

=LOOKUP("Public & Healthcare",{"Telecom","Telecom";"Public &
Healthcare","P&H";"Industrial & Utilities","Industry";"Banking &
Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"})

Why "Others2" ?What have I missed??

/regards
tskogstrom




RichardSchollar skrev:

Or possibly this:

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company","Telecom";"P&H";"Indu stry";"B&I";"R&L";"Othe*rs";"Others"}


I've just taken out the };{ from the middle (this was causing it to
error), and replaced the semi-colon between "Company" and "Telecom"
with a comma (",").

Hope this helps!

Richard


RichardSchollar wrote:

Hi Tskogstrom

If I was doing that in the UK, I would write it as:

={"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others";"Company","Other s"}

You may have to swap the "," and ";" around. Basically, it forms a
grid where the columns are separated by "," and the rows by ";".

Hope this helps!

Richard


tskogstrom wrote:

I want to have array formula as a named range, but Excel doesn't
accept
this formula. Maybe it is the "&" sign, or? How should I write? I have
Swedish signs, therefore use ";" instead of "," etc, I guess.

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"}


THIS worksheet formula is accepted into an ordinary cell.. I recieve
"Others" in the cell:

=LOOKUP("Company";{"Telecom";"Public & Healthcare";"Industrial &
Utilities";"Banking & Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"})


So - any idea how it should be as a named range formula?

Happy to all suggestions,
Tskogstrom



  #8   Report Post  
Old December 28th 06, 01:17 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 196
Default Array as a "named range" - formula ok in cells, but error as "named range"

That's what I said! In a round about way. Point me to the gin &
tonics...


Bob Phillips wrote:

It's the order

=LOOKUP("Public & Healthcare",{"Banking &
Insurance","B&I";"Company","Others";"Industrial &
Utilities","Industry";"Other","Others2";"Public & Healthcare","P&H";"Retail
& Logistics","R&L";"Telecom","Telecom"})

--
---


  #9   Report Post  
Old December 28th 06, 01:23 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 1,726
Default Array as a "named range" - formula ok in cells, but error as "named range"

Yeah, but I was concocting mine whilst you replied, so I hadn't seen it at
that point.

I'll take mine without the tonic please.

Bob


"RichardSchollar" wrote in message
ups.com...
That's what I said! In a round about way. Point me to the gin &
tonics...


Bob Phillips wrote:

It's the order

=LOOKUP("Public & Healthcare",{"Banking &
Insurance","B&I";"Company","Others";"Industrial &
Utilities","Industry";"Other","Others2";"Public &
Healthcare","P&H";"Retail
& Logistics","R&L";"Telecom","Telecom"})

--
---




  #10   Report Post  
Old December 28th 06, 03:03 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 92
Default Array as a "named range" - formula ok in cells, but error as "named range"

Thank you richard,
everything is rock'n roll now. I'll change to my own national setting
now, wish me luck now it will become right syntax by auto ....

/regards
tskogstrom


RichardSchollar skrev:

From Excel help (and I didn't consider this possibility - my apologies)


Important The values in array must be placed in ascending order:
...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not
give the correct value. Uppercase and lowercase text are equivalent.

Which I presume is why you are getting an incorrect result.

You can correct this by using the VLOOKUP form (at least the following
worked for me):

=VLOOKUP("Public & Healthcare",{"Telecom","Telecom";"Public &
Healthcare","P&H";"Industrial & Utilities","Industry";"Banking &
Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"},2,0)

Richard


tskogstrom wrote:

Hi,
thanks for trying. I have now changed national settings to UK and this
is OK:
{"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"}

Still my Lookup formula doesn't work - BUT nor in the worksheet:

In the worksheet cell this formula produce "2" as it should:
=LOOKUP("b",{"a",1;"b",2;"c",3})

Shouldn't this formula in that case produce "P&H"? It doesn't, whatever
I write as "lookup_value", it gives "Others2" as result. The same I
recieve from the named range with same syntax:

=LOOKUP("Public & Healthcare",{"Telecom","Telecom";"Public &
Healthcare","P&H";"Industrial & Utilities","Industry";"Banking &
Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"})

Why "Others2" ?What have I missed??

/regards
tskogstrom




RichardSchollar skrev:

Or possibly this:

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company","Telecom";"P&H";"Indu stry";"B&I";"R&L";"Othe*rs";"Others"}


I've just taken out the };{ from the middle (this was causing it to
error), and replaced the semi-colon between "Company" and "Telecom"
with a comma (",").

Hope this helps!

Richard


RichardSchollar wrote:

Hi Tskogstrom

If I was doing that in the UK, I would write it as:

={"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others";"Company","Other s"}

You may have to swap the "," and ";" around. Basically, it forms a
grid where the columns are separated by "," and the rows by ";".

Hope this helps!

Richard


tskogstrom wrote:

I want to have array formula as a named range, but Excel doesn't accept
this formula. Maybe it is the "&" sign, or? How should I write? I have
Swedish signs, therefore use ";" instead of "," etc, I guess.

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"}


THIS worksheet formula is accepted into an ordinary cell.. I recieve
"Others" in the cell:

=LOOKUP("Company";{"Telecom";"Public & Healthcare";"Industrial &
Utilities";"Banking & Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"})


So - any idea how it should be as a named range formula?

Happy to all suggestions,
Tskogstrom




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 05:30 AM
Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 05:18 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 03:28 AM
How can I create an array formula for non-sequential cells Sue Excel Worksheet Functions 7 May 21st 05 02:32 PM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 04:37 PM


All times are GMT +1. The time now is 05:22 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017