Using calculated column and row values in a formula

berryalan

New Member
Joined
Jul 16, 2013
Messages
6
HI,

I have a workbook which is populated by a query. Due to the nature of the data, both the width and the length of the data changes. I have created an "Admin" work sheet in which the column headers of Sheet1 are searched to identify the one I need in this particular case as well as finding the last row of populated data in a column. So in my admin page I now, for example, have a cell that contains "AG" and a cell that contains "813". What I now want to do is in another worksheet, refer to the value in this cell I thought I could do it along the lines of "=Sheet1!INDIRECT(B5 & B6)" where B5 is the cell that contains the value "AG" and B6 is the one that contains the value "815 in my Admin worksheet. I get an error. I have tried different parenthesis combinations but again, no joy. I also tried creating a Named Range using =INDIRECT(Admin!b5&815). When I run this on the admin page it goes to the correct cell reference but in the Admin worksheet.


So the question is how do I uses a calculated cell reference contained in a cell in a subsequent formula?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,854
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Try:
=INDIRECT("Sheet1!"&B5 & B6)
 

Forum statistics

Threads
1,144,239
Messages
5,723,192
Members
422,482
Latest member
MacSapper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top