multi sheet reference for 1 cell/Impossible??

[August]

I have an excel doc with several worksheets
1 summary sheet
The rest are all different addresses, the address in each sheet is in cell B2
I would like a formula in the summary sheet the give the address in cell B2 and be able to drag the formula down to give the addresses for each different sheet

There is a add on for excel called Kutools that does it and I'm prepared to pay for that but would rather be able to do it and understand it myself

Thanks in advance

 

[Rick Rothstein]

If I understand your request correctly, you want a formula that displays the contents of cell B2 on any given sheet. If that is correct, you can use this UDF (user defined function) to do it...

Function GetB2(SheetNum As Long) As String
  On Error GoTo NoSuchSheet
  GetB2 = Sheets(SheetNum).Range("B2")
NoSuchSheet:
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetB2 just like it was a built-in Excel function. For example, assuming your summary sheet is the first sheet so that you want to start listing from the second sheet onward, put this formula in your starter cell and then copy it down...

=GetB2(ROWS($1:2))

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[August]

Wow, that works perfectly
Thank you so much for taking the time to help

If I understand your request correctly, you want a formula that displays the contents of cell B2 on any given sheet. If that is correct, you can use this UDF (user defined function) to do it...

Function GetB2(SheetNum As Long) As String
  On Error GoTo NoSuchSheet
  GetB2 = Sheets(SheetNum).Range("B2")
NoSuchSheet:
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetB2 just like it was a built-in Excel function. For example, assuming your summary sheet is the first sheet so that you want to start listing from the second sheet onward, put this formula in your starter cell and then copy it down...

=GetB2(ROWS($1:2))

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.