CODEWITHSUNDEEP
excelcellsvba
Rainer Lara
Rainer Lara

Reputation: 21

Create a range based on the values of two non-adjacent cells in Excel

This is the example: On Sheet 3

   A   B   C
1  A
2  13
3  190

These numbers are generated based on data from different Sheets. How could I create a formula on sheet3 that uses a range based on these numbers. Something like: c= SUM Sheet1!(A1&A2:A1&A3) to sum the range A13 to A190 that is on sheet 1. The reason I want to do this is because the number stored in A3 is dynamically generated. Please, help. Thanks in advance.

Upvotes: 2

Views: 809

Answers (2)

Rainer Lara
Rainer Lara

Reputation: 21

Got it! The way to solve the problem is to add a reference to the name of the other workbook on the same page. Jeeped solution worked great, but I needed more info in the formula. I changed my data like this:

<table style="width:50%">
  <tr>
    <th></th>
    <th>SHEET1</th> 
    <th>SHEET1</th>
  </tr>
  <tr>
    <td>COLUMN</td>
    <td>F</td>
    <td>G</td>
  </tr>
  <tr>
    <td>FIRST CELL</td>
    <td>2</td>
    <td>190</td>
  </tr>
  <tr>
    <td>LAST CELL</td>
    <td>160</td>
    <td>300</td>
  </tr>
</table>

So, the formula ended up like this:

=SUM(INDIRECT("'"&B2&"'!"&B3&B4&":"&B3&B5))

Where B2 was the reference to the cell with the sheet name from which the data was retrieved.

Thank you Jeeped for your help. You were great and I admire your professionalism and speed to point me on the right path.

Upvotes: 0

user4039065
user4039065

Reputation:

try,

=sum(indirect(a1&a2&":"&a1&a3))

INDEX would be better but A1 should contain 1, not A. Example,

=sum(index(A:Z, a2, code(upper(a1))-64):index(A:Z, a3, code(upper(a1))-64))

Upvotes: 2

Related Questions