yogi_Set Up In Column D 50 URLs Based On Sheet Numbers In Column B And Spreadsheet Keys In Column C

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #590   Jun 11, 2012     www.energyefficientbuild.com.
user tabus said:
multiple if-clause problem
Hi everyone,

The problem I encountered seems ridiculously easy, but after tens of tries, I still can't figure out how to fix it:(

Here we go:

col.A - empty

col.B - consecutive numbers: 1 onwards, achieved through "arrayformula"; ocassionally it gets text value instead of number

col.C - depending on neighbouring cell's value in col.B

           if 0<value<51 then "text 1"

           if 50<value<101 then "text 2"

           if 100<value<151 then "text 3"

           and so on...

So, the table should look like:

col.B       col.C

   1             text 1

   2             text 1

   ...            text 1

   50            text 1

   51            text 2

   52            text 2

   ...             text 2

   100           text 2

   101           text 3

Looks really simple, right?

So I use this formula in col.C:

=arrayformula(if(ISNUMBER(B3:B)=true;if(and(B3:B>0;B3:B<51);"text 1";if(and(B3:B>50;B3:B<101);"text 2";if(and(B3:B>100;B3:B<151);"text 3";"here another if-clause")));"n/a")) 

The issue:

As long as the value in col.B is between 0 and 50 (incl.), the formula works OK, but as soon as it turns into 51, I don't get any the "text 1, 2 or 3" but "here another if-clause" instead.

What's wrong??

It seems as if "B3:B" is read: "check the last value in col.B"

How do I fix it?

Any help appreciated!

----

Thanks for your quick reply,

Although the solution you've come up with does seem to work, it is unfortunately not really what I was trying to achieve:(

Well, probably my description wasn't clear enough, but for the sake of clarity I simplified the formula presented in the 1st post

So, let me now explain the problem in detail.

col.B

-it is filled up automatically with numbers from 1 to ..., they increase gradually by 1

-the values in col.B actually stand for "gid" number, used later on for producing specific urls

col.C

-here I take the corresponding value from col.B and produce an url, according to pattern: "https://docs.google.com/spreadsheet/pub?key="&"...key value..."&"&single=true&gid="&B3:B&"&output=html&gridlines=false"

-due to "importrange" restrictions, specific "...key value..." can only be used 50 times in 50 separate urls

-so my idea was to change "...key value..." (i.e. use another doc) when gid number turns from 50 to 51, then 100/151, and so on

that's why I wanted to make separate "key values" dependent on the value from col.B (i.e. gid number)

and again, the formula I presented above works until the value in col.B is <51 - a nice url is produced, but since 51 it only shows the option "here another if-clause"

I can't get it work on given if-clauses

If it's clearer now, can help me out with that?

-------------------------------------------------------------------------------------------------------

based on my understanding of what the OP is asking for, let us have a look at the following solution