Assuming your row number is in `B1`, you can use `INDIRECT`:

    =INDIRECT(&quot;A&quot; &amp; B1)

This takes a cell reference as a string (in this case, the concatenation of `A` and the value of `B1` - 5), and returns the value at that cell.

This should do the trick! :)

    B1 =ROW(A5)

http://www.techonthenet.com/excel/formulas/row.php

An alternative is to use OFFSET:

Assuming the column value is stored in B1, you can use the following

    C1 = OFFSET(A1, 0, B1 - 1)

This works by:

a) taking a base cell (A1)&lt;br&gt;
b) adding 0 to the row (keeping it as A)&lt;br&gt;
c) adding (A5 - 1) to the column

You can also use another value instead of 0 if you want to change the row value too. 

I want to be able to create a new instance of an object by calling a method on an already instantiated object. For example, I have the object:

`organism = Organism()`

I want to be able to call `organism.reproduce()` and have two objects of type Organism. My method at this point looks like this:

    class Organism(object):
        def reproduce():
            organism = Organism()

and I&#39;m pretty sure it doesn&#39;t work (I&#39;m not really even sure how to test it. I tried the gc method in [this post][1]). So how can I make my object create a copy of itself that&#39;s accessible just like the first object I created (with `organism = Organism()`)?


  [1]: https://stackoverflow.com/questions/328851/printing-all-instances-of-a-class

Create new class instance from class method

In Ruby, is there a way to &#39;override&#39; a constant in a subclass in such a way that calling an inherited method from the subclass results in that method using the new constant instead of the old one? For example:

    class SuperClass
      CONST = &quot;Hello, world!&quot;
      def self.say_hello
        CONST
      end
    end
    
    class SubClass &lt; SuperClass
      override_const :CONST, &quot;Hello, Bob!&quot;
    end
    
    SuperClass.say_hello # =&gt; &quot;Hello, world!&quot;
    SubClass.say_hello   # =&gt; &quot;Hello, Bob!&quot;

If not, is there perhaps a way to do something like this instead?

    class SuperClass
      CONST = &quot;Hello, world!&quot;
      def self.say_hello
        CONST
      end
    end

    SubClass = SuperClass.clone
    SubClass.send(:remove_const, :CONST)
    SubClass.const_set(:CONST, &quot;Hello, Bob!&quot;)
    
    SubClass.say_hello # =&gt; &quot;Hello, Bob!&quot;

----------

Note: I tried my second example out in irb, and it seems to work except that class methods can&#39;t seem to access CONST after I clone the class:

    irb(main):012:0&gt; SubClass.say_hello
    NameError: uninitialized constant Class::CONST
            from (irb):4:in `say_hello&#39;
            from (irb):12
            from C:/Ruby193/bin/irb:12:in `&lt;main&gt;&#39;

Override ruby constant in subclass so inherited methods use new constant instead of the old?

Suppose I have: 

 1.  a value of 5 in `B1`
 2.  I want to pass the number (5) in `B1` as a row variable, which will be read in conjunction with column `A` into another cell (say `C1`) as &quot;=A(B1)&quot; i.e. &quot;=A5&quot;
   
How would I do this?

Pass row number as variable in excel sheet

<p>Suppose I have:</p>
<ol>
<li>a value of 5 in <code>B1</code></li>
<li>I want to pass the number (5) in <code>B1</code> as a row variable, which will be read in conjunction with column <code>A</code> into another cell (say <code>C1</code>) as "=A(B1)" i.e. "=A5"</li>
</ol>
<p>How would I do this?</p>


I have a column of values that often appear as duplicates. I need to create a new column, of unique values based on the first column, as follows:

    Column A   Column B  
    a          a
    a          b
    b          c
    c
    c

This Column B will actually need to appear on a different sheet, within the same workbook, so I assume it will need to work with the `sheet2!A1` style format.

I have not had any luck with the Data/Filter menu options as this only seems to work on command. I need column B to update automatically whenever a new value is entered into column A. 

Ignore Duplicates and Create New List of Unique Values in Excel

Why does Excel ignore the time component of datetime values in a chart. For example, the following data :

    10/10/2010 9:30   1 
    10/10/2010 9:40   2
    10/10/2010 9:50   3
    10/10/2010 10:00  4 
    10/10/2010 10:10  5
    10/10/2010 10:20  6

excel plots it all values on a same x-value 10/10/2010.
If I format the values as time, it works fine. How do I tell excel to display datetime and interpret it as datetime not date..


excel plot against a date time x series

Via VBA how can I check if a cell is empty from another with specific information?

For example:

&gt; If A:A = &quot;product special&quot; And B:B is null Then
&gt;
&gt; C1 = &quot;product special&quot;

![Illustration of example][1]


  [1]: http://i.stack.imgur.com/lurRH.png

Additionally, how can I use a `For Each` loop on the`Range` and how can I return the value in the other cell?

Excel: How to check if a cell is empty with VBA?

I recently upgraded to SQL2012 and am using Management Studio. One of my columns in the database has a `CHAR(13) + CHAR(10)` stored in it. 

When I was using SQL Server 2008, this would copy and paste completely fine into Excel. Now, however, copying and pasting the same data creates a new line/ carriage return in the data I have in Excel.

Is there a setting I missed in SQL2012 that will resolve this issue? I don&#39;t want to simply `REPLACE(CHAR(13) + CHAR(10))` on every single database selection, as I would have to go from using `SELECT *` to defining each individual column.



New Line Issue when copying data from SQL Server 2012 to Excel

I am new to Python. I need to write some data from my program to a spreadsheet. I&#39;ve searched online and there seem to be many packages available (xlwt, XlsXcessive, openpyxl). Others suggest to write to a .csv file (never used CSV and don&#39;t really understand what it is).

The program is very simple. I have two lists (float) and three variables (strings). I don&#39;t know the lengths of the two lists and they probably won&#39;t be the same length.

I want the layout to be as in the picture below:

![Layout sample][1]


  [1]: http://i.stack.imgur.com/Ih3fa.png

The pink column will have the values of the first list and the green column will have the values of the second list.

So what&#39;s the best way to do this?

P.S. I am running Windows 7 but I won&#39;t necessarily have Office installed on the computers running this program.


    import xlwt

    x=1
    y=2
    z=3

    list1=[2.34,4.346,4.234]

    book = xlwt.Workbook(encoding=&quot;utf-8&quot;)

    sheet1 = book.add_sheet(&quot;Sheet 1&quot;)

    sheet1.write(0, 0, &quot;Display&quot;)
    sheet1.write(1, 0, &quot;Dominance&quot;)
    sheet1.write(2, 0, &quot;Test&quot;)
    
    sheet1.write(0, 1, x)
    sheet1.write(1, 1, y)
    sheet1.write(2, 1, z)

    sheet1.write(4, 0, &quot;Stimulus Time&quot;)
    sheet1.write(4, 1, &quot;Reaction Time&quot;)

    i=4

    for n in list1:
        i = i+1
        sheet1.write(i, 0, n)
    


    book.save(&quot;trial.xls&quot;)

I wrote this using all your suggestions. It gets the job done but it can be slightly improved.

How do I format the cells created in the for loop (list1 values) as scientific or number?

I do not want to truncate the values. The actual values used in the program would have around 10 digits after the decimal.

Writing to an Excel spreadsheet

I have a very large excel spread sheet that has a column of time stamps. Does anyone know convert that over to a date? Is there a function I can use? I tried format cell date but that doesn&#39;t work. My file is 91,568 KB. If there is a simpler way to this that would be great. I&#39;m open to ideas.

Thank you in advance :)

P.S.
I don&#39;t know any programming languages

Converting time stamps in excel to dates

Consider the following worksheet:

         A       B        C        D
    1 COMPANY  XVALUE   YVALUE   GROUP
    2 Apple     45       35       red
    3 Xerox     45       38       red
    4 KMart     63       50       orange
    5 Exxon     53       59       green

I have used the scatterplot function in Excel to create the following chart:

![enter image description here][1]


  [1]: http://i.stack.imgur.com/pHAEp.png

However, each point in the chart has an additional property: `GROUP`. There are four groups: `red`, `orange`, `black` and `green`. I would like to color each dot accordingly, so that I could perhaps see a pattern (group `green`being almost always on the left side of the chart, for instance). Because my list is 500 rows long, I cannot do this manually. How can I do this automatically?

How can I color dots in a xy scatterplot according to column value?

Is there an in-built function to check if a cell contains a given character/substring?  

It would mean you can apply textual functions like `Left`/`Right`/`Mid` on a conditional basis without throwing errors when delimiting characters are absent.

Check whether a cell contains a substring

Is there an efficient way to identify the last character/string match in a string using base functions?      I.e. not the last character/string **of** the string, but the position of a character/string&#39;s last occurrence **in** a string.  `Search` and `find` both work left-to-right so I can&#39;t think how to apply without lengthy recursive algorithm.  And [this solution][1] now seems obsolete.


  [1]: http://www.mrexcel.com/archive/General/30895.html

Content Type	Original Author	Original Content on Stackoverflow
Question	Yashpal Singla	View Question on Stackoverflow
Solution 1 - Excel	RocketDonkey	View Answer on Stackoverflow
Solution 2 - Excel	CustomX	View Answer on Stackoverflow
Solution 3 - Excel	Mischinab	View Answer on Stackoverflow

Pass row number as variable in excel sheet

Excel Problem Overview

Excel Solutions

Solution 1 - Excel

Solution 2 - Excel

Solution 3 - Excel

Override ruby constant in subclass so inherited methods use new constant instead of the old?

Create new class instance from class method

Attributions