Formula to reference column heading
 

I am trying to create a spreadsheet that collects data from a number of
similarly formatted worksheets. Since I don’t want to have to manually
enter each sheet name into the main collection worksheet formulas I
would like the formulas in each column to reference data from the
worksheet bearing the name of that formulas’ column header.

For instance, in a column labeled “03” I want to reference a worksheet
named “03” in this formula but if I copy this formula across to
multiple rows I need to edit each row and change the “03” to “02”, “01”
etc…I know I can edit and replace but my main worksheet collects data
from many worksheets.

=SUMPRODUCT(--(MONTH('03'!$D$6:$D$76)=1),'03'!$J$6:$J$76)


--
JimDandy
------------------------------------------------------------------------
JimDandy's Profile: http://www.excelforum.com/member.php...o&userid=16578
View this thread: http://www.excelforum.com/showthread...hreadid=314401

Hi
have a look at the INDIRECT function for this

"JimDandy" wrote:


I am trying to create a spreadsheet that collects data from a number of
similarly formatted worksheets. Since I dont want to have to manually
enter each sheet name into the main collection worksheet formulas I
would like the formulas in each column to reference data from the
worksheet bearing the name of that formulas column header.

For instance, in a column labeled €œ03€� I want to reference a worksheet
named €œ03€� in this formula but if I copy this formula across to
multiple rows I need to edit each row and change the €œ03€� to €œ02€�, €œ01€�
etc€¦I know I can edit and replace but my main worksheet collects data
from many worksheets.

=SUMPRODUCT(--(MONTH('03'!$D$6:$D$76)=1),'03'!$J$6:$J$76)


--
JimDandy
------------------------------------------------------------------------
JimDandy's Profile: http://www.excelforum.com/member.php...o&userid=16578
View this thread: http://www.excelforum.com/showthread...hreadid=314401