Table of contents
Funkyfido
I want the contents to be on the first sheet and pick up the tab name and
cell d5 in each tab. How would I go about doing this?
Thanks
Arvi Laanemets
Below is an UDF. Copy this into your workbook's module. Then on 1st sheet,
p.e. into A1 enter the formula
=IF(ISERROR(TABI(ROW(),NOW()),"",TABI(ROW())
, and copy down at least for as much rows as you have sheets in workbook.
Into B1 enter the formula
=IF($A1="","",INDIRECT("'" & $A1 & "'!D5"))
and copy it down too.
--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )
**********
The UDF is here!
Option Explicit
Public Function TabI(TabIndex As Integer, Optional parVolatile As Date) As
String
TabI = Sheets(TabIndex).Name
End Function
***********
"Funkyfido" <Funk...@discussions.microsoft.com> wrote in message
news:4285BB5F-4B12-4FC4...@microsoft.com...
Funkyfido
Arvi Laanemets
Open your Excel file
Press Atl+F11 - the VBA window openes - there are 2-4 child windows
(Project, the biggest one without special name, and maybe also Propertries
and Watch)
In Project window, you current project (i.e. workbook) and its objects are
diaplayed. When you have there container Modules with one or several Module
objects (i.e. you did have some UDF's or Procedures in this workbook
earlier), you can activate some existing module (double-click on it).
When you didn't have any modules, or you decided to use a new one, select
from menu Insert>Module, and then activate it.
Copy the 1st row (Option Explicit) at top of module
Copy the rest of code below it and close the VBA window. Now you can use the
function TABI() as any regular one - in Function Wizard it is displayed in
User Defined categorie. Proceed with entering formulas on 1st sheet as I
described in previous post.
--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )
"Funkyfido" <Funk...@discussions.microsoft.com> wrote in message
news:090B402D-8CF9-446B...@microsoft.com...