Returning the value of cell to link to a worksheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Help Please - And thanks in advance to anyone who can and does! :)

My worksheets are named by member_id (a number), on my first worksheet, the summary worksheet, there is a row for each member's subsequent worksheet. On my summary, I'm doing a "sumif" formula and linking to each member's worksheet. I want to be able to look in the member_Id column rather than type each member's id in the formula (twice in this particular formula). Is there a way I can refer to the Member_Id column and return it's value in my formula? I've messed with "index", "address", "choose". I'm now stumped.

Thanks so much,
Kathi J.
 
Assuming in your sheet: Summary
Member ids are in col A, A2 down, and
the SUMIF criteria are placed in B1, C1, D1 etc

Try something along these lines:

Put in B2: =SUMIF(INDIRECT(A2&"!A:A"),B$1,INDIRECT(A2&"!B:B"))
(assuming col A = range, col B = sum_range in all the member id sheets)

Copy B2 down col B until the last member id row
then copy across as many cols as you may have SUMIF criteria

--
hth
Max
-----------------------------------------
Please reply in thread
Use xdemechanik <at>yahoo<dot>com for email
---------------------------------------------
Kathi said:
Help Please - And thanks in advance to anyone who can and does! :)

My worksheets are named by member_id (a number), on my first worksheet,
Click to expand...
the summary worksheet, there is a row for each member's subsequent
worksheet. On my summary, I'm doing a "sumif" formula and linking to each
member's worksheet. I want to be able to look in the member_Id column
rather than type each member's id in the formula (twice in this particular
formula). Is there a way I can refer to the Member_Id column and return
it's value in my formula? I've messed with "index", "address", "choose".
I'm now stumped.
 
Back
Top