Monday, June 11, 2012

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

 

1 comment:

  1. Hi Yogi, again, many thanks for your next solution.
    I'm sorry to say however it's not what I was looking for.
    I made the worksheet public, so please, have a look at:
    https://docs.google.com/spreadsheet/pub?key=0AhFEFv15COC1dHBYM2czZlV4MEdpUHl3UWltamFMSEE&output=html

    The "url_OK" tab is what it should look like, the "url_notOK" shows when it stops working. The crucial point is between rows 52 and 53.

    Thanks in advance!

    ReplyDelete