Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
JimDandy
 
Posts: n/a
Default 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

  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert rows based on specific value bob Excel Worksheet Functions 6 February 29th 08 08:11 PM
incrementing formula reference by 7 Patti Excel Discussion (Misc queries) 2 January 20th 05 09:23 PM
copying a formula, the reference adjusts, but the result does not chezoo Excel Discussion (Misc queries) 1 January 12th 05 02:51 AM
what formula do i put for column m = column k minus column l in e. jenniss Excel Discussion (Misc queries) 5 January 6th 05 09:18 PM
I there an easy way to find out if any formula reference a cell? Marc New Users to Excel 1 December 6th 04 11:41 PM


All times are GMT +1. The time now is 06:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"