Syntax to input variable column references into formula

Sebh007

New Member
Joined
Jan 29, 2013
Messages
1
Hi. I have an Excel 2007 workbook and on one worksheet it includes a formula all the way down Column H as follows:

{=MAX(IF(NOT(ISBLANK('ReferencedSheet'!F$2:F$62)),ROW('ReferencedSheet'!F$2:F$62),0))-1}

The worksheet it is on is not 'ReferencedSheet' but BaseSheet. I have tested it and it works beautifully! (It finds the last blank cell in column F and then displays the row number of the last 'filled' cell which is in the row above it).

It is only a stepping stone however. What I really need to do is to substitute the references to Column F with a reference to a variable column which I have already determined in column G, so column G currently contains: N, AA, P, G, BX etc.

I tried to use INDIRECT but of course that only works for a complete cell reference so it isn't happy. Please can someone tell me how I get the equivalent of where the column letters are being picked up from my existing Column G:

{=MAX(IF(NOT(ISBLANK('ReferencedSheet'!N$2:N$62)),ROW('ReferencedSheet'!N$2:N$62),0))-1}
{=MAX(IF(NOT(ISBLANK('ReferencedSheet'!AA$2:AA$62)),ROW('ReferencedSheet'!AA$2:AA$62),0))-1}
{=MAX(IF(NOT(ISBLANK('ReferencedSheet'!P$2:P$62)),ROW('ReferencedSheet'!P$2:P$62),0))-1}...etc?

Many thanks in advance!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Forum statistics

Threads
1,181,322
Messages
5,929,281
Members
436,660
Latest member
Mouseinalabyrinth

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