Grabbing Formula Information from Another Cell

[RJB]

In other words... I have a file with a Worksheet for every sales district.

I'd like a combination table on one sheet that sumproducts several rows of
info, with one column per sales district.

Is there a way to write the formula where it will grab the information from
a sheet, if I name my column headings after the sheets?

In other words, my Worksheet tab name is "East", "West", "South", etc.

My formula in B2is =SUMPRODUCT((EAST!J12450:J13450),...etc.)
and in C2
=SUMPRODUCT((WEST!J12450:J13450),...etc.)

Can I set it so if cell B1="EAST", and C1 is "WEST", I can use the exact
same formula in B2 and C2?

 

[Max]

An example taken from my recent response to another OP,
which uses INDIRECT & OFFSET to provide the flexibility:

Illustrated in this sample:
http://www.freefilehosting.net/download/3k364
Summarizing 6 sheets.xls

In Total,
Put in C3:
=SUMPRODUCT((INDIRECT("'"&C$1&"'!A2:A200")=$A3)*(INDIRECT("'"&C$1&"'!B2:B200")=$B3)*OFFSET(INDIRECT("'"&C$1&"'!B2:B200"),,MATCH(C$2,INDIRECT("'"&C$1&"'!1:1"),0)-2,))
Copy C3 across to H3, fill down as far as required. Adapt the ranges to suit.

Notes: As detailed in the sample, a complete listing of codes & descriptions
is assumed in A3:B3 down. Sheetnames are listed in C1:H1, cities in C2:H2
like this:

Jan Feb Jan Feb Jan Feb
Atlanta Atlanta Chicago Chicago Detroit Detroit

------ ---

Adapt the example to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik

 

[RJB]

Alright, will try.

Didn't even know how to Google search for what I was looking for!

Thanks,

rjb

 

[Max]

If you have specifically, your line:

.. =SUMPRODUCT((EAST!J12450:J13450),...etc.)

With B1 containing the sheetname: EAST
you expression should look like this, with INDIRECT applied:

=SUMPRODUCT((INDIRECT("'"&B$1&"'!J12450:J13450"),...etc)

Take a moment to press the "Yes" button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---

 

[RJB]

So I've been playing with INDIRECT and OFFSET - thanks for the advice.

When I grab cells from other files, I get a #REF error unless the file's
open; this does not happen with VLOOKUP, which automatically updates and
repopulates even through closed files.

Is there a way around this?

 

[Peo Sjoblom]

Not by using INDIRECT, you can download Morefunc which is an add-in and it
has a function called INDIRECT.EXT which works on files that are closed.
Harlan Grove also posted a UDF some years ago called PULL (Google search)
which works.


--


Regards,


Peo Sjoblom

 

[RJB]

Oh, that looks fantastic. Thanks!

 

[RJB]

oK, I take this back. I can't get INDIRECT.EXT to work! Will keep plugging
away...