Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grabbing Formula Information from Another Cell
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grabbing Formula Information from Another Cell
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)*(I NDIRECT("'"&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" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grabbing Formula Information from Another Cell
Alright, will try.
Didn't even know how to Google search for what I was looking for! Thanks, rjb "Max" wrote: 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)*(I NDIRECT("'"&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" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grabbing Formula Information from Another Cell
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" wrote: Alright, will try. Didn't even know how to Google search for what I was looking for! Thanks, rjb |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grabbing Formula Information from Another Cell
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grabbing Formula Information from Another Cell
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" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grabbing Formula Information from Another Cell
Oh, that looks fantastic. Thanks!
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grabbing Formula Information from Another Cell
oK, I take this back. I can't get INDIRECT.EXT to work! Will keep plugging
away... "RJB" wrote: Oh, that looks fantastic. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for cell to get information from other worksheets | Excel Worksheet Functions | |||
Grabbing data from a specific cell | Excel Worksheet Functions | |||
IF information is in cell A8 then Formula is active? | Excel Worksheet Functions | |||
Using cell information in formula? | Excel Worksheet Functions | |||
Grabbing the last Non-empty cell in a range | Excel Discussion (Misc queries) |