A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

make formula look at diff sheet



 
 
Thread Tools Display Modes
  #1  
Old December 26th, 2009, 06:09 PM posted to microsoft.public.excel.misc
wx4usa
external usenet poster
 
Posts: 121
Default make formula look at diff sheet

I have this formula that works great if the data is on the same
sheet......=SUM(INDIRECT(ADDRESS(2,MATCH(A1,B1:M1, 0)+1)&":"&ADDRESS
(2,MATCH(A2,B1:M1,0)+1)))

I changed it to this on sheet2......=SUM(INDIRECT(ADDRESS(3,MATCH($C
$9,Sheet1!B1:M1,0)+1)&":"&ADDRESS(3,MATCH($C$10,Sh eet1!B1:M1,0)+1)))

And it returns the 2nd row on the sheet the formula is on rather than
Sheet1.

Any help would be appreciated. Not exactly sure how this bulletin
board works. Thanks.
  #2  
Old December 26th, 2009, 06:37 PM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,759
Default make formula look at diff sheet

Try it like this...

=SUM(INDIRECT(ADDRESS(3,MATCH(Sheet1!$C$9,Sheet1!B 1:M1,0)+1,,,"Sheet1")&":"&ADDRESS(3,MATCH(Sheet1!$ C$10,Sheet1!B1:M1,0)+1)))

This will do the same thing and doesn't use the volatile function INDIRECT:

=SUM(INDEX(Sheet1!B3:M3,MATCH(Sheet1!$C$9,Sheet1!B 1:M1,0)):INDEX(Sheet1!B3:M3,MATCH(Sheet1!$C$10,She et1!B1:M1,0)))

--
Biff
Microsoft Excel MVP


"wx4usa" wrote in message
...
I have this formula that works great if the data is on the same
sheet......=SUM(INDIRECT(ADDRESS(2,MATCH(A1,B1:M1, 0)+1)&":"&ADDRESS
(2,MATCH(A2,B1:M1,0)+1)))

I changed it to this on sheet2......=SUM(INDIRECT(ADDRESS(3,MATCH($C
$9,Sheet1!B1:M1,0)+1)&":"&ADDRESS(3,MATCH($C$10,Sh eet1!B1:M1,0)+1)))

And it returns the 2nd row on the sheet the formula is on rather than
Sheet1.

Any help would be appreciated. Not exactly sure how this bulletin
board works. Thanks.



  #3  
Old December 26th, 2009, 06:41 PM posted to microsoft.public.excel.misc
Lars-Åke Aspelin[_2_]
external usenet poster
 
Posts: 722
Default make formula look at diff sheet

On Sat, 26 Dec 2009 09:09:44 -0800 (PST), wx4usa
wrote:

I have this formula that works great if the data is on the same
sheet......=SUM(INDIRECT(ADDRESS(2,MATCH(A1,B1:M1 ,0)+1)&":"&ADDRESS
(2,MATCH(A2,B1:M1,0)+1)))

I changed it to this on sheet2......=SUM(INDIRECT(ADDRESS(3,MATCH($C
$9,Sheet1!B1:M1,0)+1)&":"&ADDRESS(3,MATCH($C$10,S heet1!B1:M1,0)+1)))

And it returns the 2nd row on the sheet the formula is on rather than
Sheet1.

Any help would be appreciated. Not exactly sure how this bulletin
board works. Thanks.



In the first ADDRESS function you will need the sheet_text parameter,
like this:

=SUM(INDIRECT(ADDRESS(2,MATCH(A1,Sheet1!B1:M1,0)+1 ,,,"Sheet1")&":"&ADDRESS(2,MATCH(A2,Sheet1!B1:M1,0 )+1)))

Hope this helps / Lars-Åke
  #4  
Old December 26th, 2009, 07:08 PM posted to microsoft.public.excel.misc
wx4usa
external usenet poster
 
Posts: 121
Default make formula look at diff sheet

On Dec 26, 12:37*pm, "T. Valko" wrote:
Try it like this...

=SUM(INDIRECT(ADDRESS(3,MATCH(Sheet1!$C$9,Sheet1!B 1:M1,0)+1,,,"Sheet1")&":"&ADDRESS(3,MATCH(Sheet1!$ C$10,Sheet1!B1:M1,0)+1)))

This will do the same thing and doesn't use the volatile function INDIRECT:

=SUM(INDEX(Sheet1!B3:M3,MATCH(Sheet1!$C$9,Sheet1!B 1:M1,0)):INDEX(Sheet1!B3:M3,MATCH(Sheet1!$C$10,She et1!B1:M1,0)))

--
Biff
Microsoft Excel MVP

"wx4usa" wrote in message

...

I have this formula that works great if the data is on the same
sheet......=SUM(INDIRECT(ADDRESS(2,MATCH(A1,B1:M1, 0)+1)&":"&ADDRESS
(2,MATCH(A2,B1:M1,0)+1)))


I changed it to this on sheet2......=SUM(INDIRECT(ADDRESS(3,MATCH($C
$9,Sheet1!B1:M1,0)+1)&":"&ADDRESS(3,MATCH($C$10,Sh eet1!B1:M1,0)+1)))


And it returns the 2nd row on the sheet the formula is on rather than
Sheet1.


Any help would be appreciated. Not exactly sure how this bulletin
board works. *Thanks.


Thanks Biff, I like the IndexMatch better it works great and will
allow me to drag down easier for more data. Why would one use the
Indirect function?
  #5  
Old December 26th, 2009, 07:28 PM posted to microsoft.public.excel.misc
excelent
external usenet poster
 
Posts: 388
Default make formula look at diff sheet

=SUM(INDIRECT("Sheet1!"&ADDRESS(2,MATCH(Sheet1!A1, Sheet1!B1:M1,0)+1)&":"&ADDRESS(2,MATCH(Sheets1!A2, Sheets1!B1:M1,0)+1)))


"Lars-Ã…ke Aspelin" skrev:

On Sat, 26 Dec 2009 09:09:44 -0800 (PST), wx4usa
wrote:

I have this formula that works great if the data is on the same
sheet......=SUM(INDIRECT(ADDRESS(2,MATCH(A1,B1:M1 ,0)+1)&":"&ADDRESS
(2,MATCH(A2,B1:M1,0)+1)))

I changed it to this on sheet2......=SUM(INDIRECT(ADDRESS(3,MATCH($C
$9,Sheet1!B1:M1,0)+1)&":"&ADDRESS(3,MATCH($C$10,S heet1!B1:M1,0)+1)))

And it returns the 2nd row on the sheet the formula is on rather than
Sheet1.

Any help would be appreciated. Not exactly sure how this bulletin
board works. Thanks.



In the first ADDRESS function you will need the sheet_text parameter,
like this:

=SUM(INDIRECT(ADDRESS(2,MATCH(A1,Sheet1!B1:M1,0)+1 ,,,"Sheet1")&":"&ADDRESS(2,MATCH(A2,Sheet1!B1:M1,0 )+1)))

Hope this helps / Lars-Ã…ke
.

  #6  
Old December 26th, 2009, 08:07 PM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,759
Default make formula look at diff sheet

Why would one use the Indirect function?

Sometimes you can use INDEX to "build" a range reference. This is one of
those times.

Sometimes you can only use INDIRECT to reference a range. One of the best
examples is when you have a named range or a sheet name in a cell:

A1 = MyRange

Assuming MyRange refers to Sales!A1:A10 and you want to sum that range:

=SUM(INDIRECT(A1))

Which would be the equivalent of:

=SUM(Sales!$A$1:$A$10)

Or, when you want to reference a cell/range on a variable sheet:

A1 = Region 1 (a sheet name)

=INDIRECT("'"&A1&"'!X100")

Which would be the equivalent of:

='Region 1'!X100

--
Biff
Microsoft Excel MVP


"wx4usa" wrote in message
...
On Dec 26, 12:37 pm, "T. Valko" wrote:
Try it like this...

=SUM(INDIRECT(ADDRESS(3,MATCH(Sheet1!$C$9,Sheet1!B 1:M1,0)+1,,,"Sheet1")&":"&ADDRESS(3,MATCH(Sheet1!$ C$10,Sheet1!B1:M1,0)+1)))

This will do the same thing and doesn't use the volatile function
INDIRECT:

=SUM(INDEX(Sheet1!B3:M3,MATCH(Sheet1!$C$9,Sheet1!B 1:M1,0)):INDEX(Sheet1!B3:M3,MATCH(Sheet1!$C$10,She et1!B1:M1,0)))

--
Biff
Microsoft Excel MVP

"wx4usa" wrote in message

...

I have this formula that works great if the data is on the same
sheet......=SUM(INDIRECT(ADDRESS(2,MATCH(A1,B1:M1, 0)+1)&":"&ADDRESS
(2,MATCH(A2,B1:M1,0)+1)))


I changed it to this on sheet2......=SUM(INDIRECT(ADDRESS(3,MATCH($C
$9,Sheet1!B1:M1,0)+1)&":"&ADDRESS(3,MATCH($C$10,Sh eet1!B1:M1,0)+1)))


And it returns the 2nd row on the sheet the formula is on rather than
Sheet1.


Any help would be appreciated. Not exactly sure how this bulletin
board works. Thanks.


Thanks Biff, I like the IndexMatch better it works great and will
allow me to drag down easier for more data. Why would one use the
Indirect function?


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:04 PM.


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