Grouping & Formula

Here is a good one...I've been stuck on this one for a while now...

I currently have two worksheets open. 
On Sheet1 in cells A1:A6 I have a bunch of serial numbers (one serial # per 
cell).
On Sheet2 in cells A1:A4 I have the following formulas, 
=INDIRECT("SHEET1!A1) through A4.

SHEET 1         
A1 = 100        
A2 = 200        
A3 = 300        
A4 = 400        
A5 = 500        
A6 = 600        

SHEET2
A1 =INDIRECT("SHEET1!A1) the value is 100
A2 =INDIRECT("SHEET1!A2) the value is 200
A3 =INDIRECT("SHEET1!A3) the value is 300
A4 =INDIRECT("SHEET1!A4) the value is 400

Now if I were to group cells A2:A3 on Sheet1, how do I get A2:A4 on Sheet2 
to read correctly? Or group with sheet1?

SHEET1
A1 = 100
A4 = 400
A5 = 500
A6 = 600

SHEET2
A1 =NOCLUE(!?!?) the value is 100
A2 =NOCLUE(!?!?) the value is 400
A3 =NOCLUE(!?!?) the value is 500
A4 =NOCLUE(!?!?) the value is 600

Any help would be greatly appriciated. Thanks.
0
Gabe (34)
1/5/2006 9:18:05 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
579 Views

Similar Articles

[PageSpeed] 4

I can't duplicate your problem.

Of course, I also can't duplicate your posted formulas.
I assume you have a typo, and left out the second set of quotes:

=INDIRECT("SHEET1!A1)
=INDIRECT("SHEET1!A1")

With the correct formula:
=INDIRECT("SHEET1!A1")
And grouping the rows using
<Data> <Group&Outline> <Group>
My Sheet2 *doesn't* change at all!

The display is identical ... before and after grouping rows 2 and 3.

What exact formulas are you using?
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"Gabe" <Gabe@discussions.microsoft.com> wrote in message
news:19CA49FA-88C8-4C7E-AA4D-1248A863039B@microsoft.com...
> Here is a good one...I've been stuck on this one for a while now...
>
> I currently have two worksheets open.
> On Sheet1 in cells A1:A6 I have a bunch of serial numbers (one serial #
per
> cell).
> On Sheet2 in cells A1:A4 I have the following formulas,
> =INDIRECT("SHEET1!A1) through A4.
>
> SHEET 1
> A1 = 100
> A2 = 200
> A3 = 300
> A4 = 400
> A5 = 500
> A6 = 600
>
> SHEET2
> A1 =INDIRECT("SHEET1!A1) the value is 100
> A2 =INDIRECT("SHEET1!A2) the value is 200
> A3 =INDIRECT("SHEET1!A3) the value is 300
> A4 =INDIRECT("SHEET1!A4) the value is 400
>
> Now if I were to group cells A2:A3 on Sheet1, how do I get A2:A4 on Sheet2
> to read correctly? Or group with sheet1?
>
> SHEET1
> A1 = 100
> A4 = 400
> A5 = 500
> A6 = 600
>
> SHEET2
> A1 =NOCLUE(!?!?) the value is 100
> A2 =NOCLUE(!?!?) the value is 400
> A3 =NOCLUE(!?!?) the value is 500
> A4 =NOCLUE(!?!?) the value is 600
>
> Any help would be greatly appriciated. Thanks.

0
ragdyer1 (4059)
1/5/2006 11:50:15 PM
Sorry about that little typo, you are correct the formula is 
=INDIRECT("SHEET1!A1").

See when I group rows 2 and 3 on sheet1, I actually need sheet2 to reflect 
the changes. For instance:

SHEET 1    (Notice how A2 & A3 are grouped) 
A1 = 100   
A4 = 400   (Now the value of 400 is next, this value should equal A2 on 
sheet2)
A5 = 500   (This value should equal A3 on sheet2)
A6 = 600   (This value should equal A4 on sheet2)

SHEET 2
A1 = 100  (A1 on sheet1)
A2 = 400  (A4 on sheet1)
A3 = 500  (A5 on sheet1)
A4 = 600  (A6 on sheet1)

What formula(s) could I use instead of =INDIRECT("SHEET1!A1") to make that 
happen? Or is there another way I could have excel do that?

Any help is greatly appriciated. Thanks.




"RagDyer" wrote:

> I can't duplicate your problem.
> 
> Of course, I also can't duplicate your posted formulas.
> I assume you have a typo, and left out the second set of quotes:
> 
> =INDIRECT("SHEET1!A1)
> =INDIRECT("SHEET1!A1")
> 
> With the correct formula:
> =INDIRECT("SHEET1!A1")
> And grouping the rows using
> <Data> <Group&Outline> <Group>
> My Sheet2 *doesn't* change at all!
> 
> The display is identical ... before and after grouping rows 2 and 3.
> 
> What exact formulas are you using?
> -- 
> Regards,
> 
> RD
> 
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> 
> 
> 
> "Gabe" <Gabe@discussions.microsoft.com> wrote in message
> news:19CA49FA-88C8-4C7E-AA4D-1248A863039B@microsoft.com...
> > Here is a good one...I've been stuck on this one for a while now...
> >
> > I currently have two worksheets open.
> > On Sheet1 in cells A1:A6 I have a bunch of serial numbers (one serial #
> per
> > cell).
> > On Sheet2 in cells A1:A4 I have the following formulas,
> > =INDIRECT("SHEET1!A1) through A4.
> >
> > SHEET 1
> > A1 = 100
> > A2 = 200
> > A3 = 300
> > A4 = 400
> > A5 = 500
> > A6 = 600
> >
> > SHEET2
> > A1 =INDIRECT("SHEET1!A1) the value is 100
> > A2 =INDIRECT("SHEET1!A2) the value is 200
> > A3 =INDIRECT("SHEET1!A3) the value is 300
> > A4 =INDIRECT("SHEET1!A4) the value is 400
> >
> > Now if I were to group cells A2:A3 on Sheet1, how do I get A2:A4 on Sheet2
> > to read correctly? Or group with sheet1?
> >
> > SHEET1
> > A1 = 100
> > A4 = 400
> > A5 = 500
> > A6 = 600
> >
> > SHEET2
> > A1 =NOCLUE(!?!?) the value is 100
> > A2 =NOCLUE(!?!?) the value is 400
> > A3 =NOCLUE(!?!?) the value is 500
> > A4 =NOCLUE(!?!?) the value is 600
> >
> > Any help would be greatly appriciated. Thanks.
> 
> 
0
Gabe (34)
1/18/2006 9:46:02 PM
AFAIK, it can't be done!

Try posting to the programming group to see if it can be done with code.
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gabe" <Gabe@discussions.microsoft.com> wrote in message
news:C0969B1D-14E7-4ACA-8EE9-411115A67388@microsoft.com...
> Sorry about that little typo, you are correct the formula is
> =INDIRECT("SHEET1!A1").
>
> See when I group rows 2 and 3 on sheet1, I actually need sheet2 to reflect
> the changes. For instance:
>
> SHEET 1    (Notice how A2 & A3 are grouped)
> A1 = 100
> A4 = 400   (Now the value of 400 is next, this value should equal A2 on
> sheet2)
> A5 = 500   (This value should equal A3 on sheet2)
> A6 = 600   (This value should equal A4 on sheet2)
>
> SHEET 2
> A1 = 100  (A1 on sheet1)
> A2 = 400  (A4 on sheet1)
> A3 = 500  (A5 on sheet1)
> A4 = 600  (A6 on sheet1)
>
> What formula(s) could I use instead of =INDIRECT("SHEET1!A1") to make that
> happen? Or is there another way I could have excel do that?
>
> Any help is greatly appriciated. Thanks.
>
>
>
>
> "RagDyer" wrote:
>
> > I can't duplicate your problem.
> >
> > Of course, I also can't duplicate your posted formulas.
> > I assume you have a typo, and left out the second set of quotes:
> >
> > =INDIRECT("SHEET1!A1)
> > =INDIRECT("SHEET1!A1")
> >
> > With the correct formula:
> > =INDIRECT("SHEET1!A1")
> > And grouping the rows using
> > <Data> <Group&Outline> <Group>
> > My Sheet2 *doesn't* change at all!
> >
> > The display is identical ... before and after grouping rows 2 and 3.
> >
> > What exact formulas are you using?
> > -- 
> > Regards,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> >
> >
> >
> > "Gabe" <Gabe@discussions.microsoft.com> wrote in message
> > news:19CA49FA-88C8-4C7E-AA4D-1248A863039B@microsoft.com...
> > > Here is a good one...I've been stuck on this one for a while now...
> > >
> > > I currently have two worksheets open.
> > > On Sheet1 in cells A1:A6 I have a bunch of serial numbers (one serial
#
> > per
> > > cell).
> > > On Sheet2 in cells A1:A4 I have the following formulas,
> > > =INDIRECT("SHEET1!A1) through A4.
> > >
> > > SHEET 1
> > > A1 = 100
> > > A2 = 200
> > > A3 = 300
> > > A4 = 400
> > > A5 = 500
> > > A6 = 600
> > >
> > > SHEET2
> > > A1 =INDIRECT("SHEET1!A1) the value is 100
> > > A2 =INDIRECT("SHEET1!A2) the value is 200
> > > A3 =INDIRECT("SHEET1!A3) the value is 300
> > > A4 =INDIRECT("SHEET1!A4) the value is 400
> > >
> > > Now if I were to group cells A2:A3 on Sheet1, how do I get A2:A4 on
Sheet2
> > > to read correctly? Or group with sheet1?
> > >
> > > SHEET1
> > > A1 = 100
> > > A4 = 400
> > > A5 = 500
> > > A6 = 600
> > >
> > > SHEET2
> > > A1 =NOCLUE(!?!?) the value is 100
> > > A2 =NOCLUE(!?!?) the value is 400
> > > A3 =NOCLUE(!?!?) the value is 500
> > > A4 =NOCLUE(!?!?) the value is 600
> > >
> > > Any help would be greatly appriciated. Thanks.
> >
> >

0
ragdyer1 (4059)
1/19/2006 5:56:12 AM
Reply:

Similar Artilces:

formula similar to "countifs" function in excel 2007
I need a formula similar to the countifs function offered in excel 2007. I'm using excel 2003 and can't upgrade to 2007. I want to add together cars at a particular location. The location names are in one column and the type of car is in another. In 2007, the formula would be =countifs(a2:a5,"new york", b2:b5,"ford"). What can I do to perform the same function in 2003? Please help. Try this: =3DSUMPRODUCT(--(A2:A5=3D"new york"),--(B2:B5=3D"ford")) Hope this helps. Pete On Apr 19, 5:13=A0pm, embee <em...@discussio...

Need a formula to calculate Average Sales Commission
Haven�t had any luck with this. Could someone lead me in the righ direction. I am building a spread sheet for my company that can calculate salespersons Average Sales Commission based on a Graduated Scale If a sales person earned $125,000 a year, based on the followin conditions, what is their average commission rate for the year. Their first $30,000 is based on 50% of gross revenue 0-$30,000 is 50% From $30,001 to $60,000 they are paid 65% of gross revenue From $60,000 to $90,000 they are paid 80% of gross revenue From $90,000 up they are paid 90% of gross revenue I am looking for a...

Grouping data
I am trying to group data for a specific event and have not been successful, so I am hoping someone can help. ID Date Value 174 12/3/2007 102 174 12/3/2007 909 174 12/11/2007 405 174 12/11/2007 701 206 12/6/2007 1001 173 12/3/2007 500 I want to group by ID, the MAX Date for that ID and the Max Value for the ID & Max Date The desired result would be: ID Date Value 174 12/11/2007 701 206 12/6/...

Extend Rows and related formula downwards using functions not macros
I have a simple worksheet to amortise a loan. I would like to use a formula to extend the rows downwards to the number of payments (Number of years x Number of payments per year) using a formula. For example, if there were 26 payments per year over 1 year, there should be 26 rows. Now if I change the number of years to 2 years, two things should happen: 1. The number of rows should expand to 52 from 26 2. The sum of the interest paid should include these extra rows.i.e =Sum(rownumber 1 to row number 52) instead of Sum (rownumber 1 to rownumber 26). Currently I am using a simple formula alon...

MATCH formula #3
Thanks. I'll try out those suggestions. De -- da ----------------------------------------------------------------------- dah's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=649 View this thread: http://www.excelforum.com/showthread.php?threadid=27191 ...

Group Footer at Bottom of Last Page of Group
I would like for a group footer to appear at the bottom of the last page of a group -- either hide the page footer on pages if it is not the last page of the group or have the group footer print at the bottom of the page. I have used the instructions to reset the page number and the total page count for each group. (http://support.microsoft.com/kb/841779/en-us) I was hoping to be able to set it where if the page equals the total pages of the group, it will make the page footer visible. For some reason, if there is more than one page, it doesn't work properly. I have also used the i...

query based distribution group
Hello all running Exchange 2007 sp2, I need to modify an existing query based DL so it finds mailboxes and if the account has two different email address stamped on it. For example if the mailbox has test1.com and test2.com and it's a mailbox then make it a member of the query based DL Thanks for any help If it has those two e-mail addresses? That should return just one recipient. Or do you mean a recipient with any two proxy addresses? I don't believe that's possible with LDAP. Or do you mean a mailbox-enabled user with proxy addresses in two domai...

formulas #3
As you know a formula will ask for the value in a particular cell. My problem is that I have a very long spreasheet with several different columns with formulas. Sometimes I want to delete a cell but I am not sure how many formulas will be affected because this cell may be involved in 10 different formulas. Is there a way to click on a particular cell and find out all the formulas in the spreadsheet that asks for the value in that cell. Thank you. "Flavio Tinoco" <tinoconj@optonline.net> wrote in message news:121c01c38cd2$9f605fe0$a101280a@phx.gbl... > As you know a...

Creating Universal Groups with Distribution Group members?
Hello all. We have a Windows 2003 Active Directory with Exchange 2003, and I don't really want to duplicate work, but I think I may have to (ugh!). Here is the deal. I need to create an All Managers Universal Group with every group that has managers in it. So, what I tried doing, was I created a Universal Group and added the _All Managers DL into it -- but that didn't seem to work, according to my boss who asked me to create this UG for him. So, my question is, am I not allowed to add DL members to a Universal Security Group? And if now, is there any way that I can take...

Advice needed on clearing cells without removing formulas
I would like to enter the letter "Y" in cell A1, and if I enter "Y" in B1, the "Y" is automatically removed from A1. And if I enter "Y" in C1, "Y" is cleared from B1. Is there a formula that can achieve this and allow me to enter data in the cell without removing the formula? Please help, And Thanks, Digital2k Hi Digital2k, I think you will have to use a WorksheetChange Event Procedure to achieve that. Something like... Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("A1:C1"), Target) Is Nothing T...

Distribution Group list
I have a problem with the distribution group list. Here it goes: I removed and deleted the account from this group list but whenever I send a task, it always tries to reach this deleted account and give me a undeliverable error message. Please help. Thanks Restart Exchange services. Linh wrote: > I have a problem with the distribution group list. Here > it goes: I removed and deleted the account from this > group list but whenever I send a task, it always tries to > reach this deleted account and give me a undeliverable > error message. Please help. Thanks ...

setup formula to calculate random dates
Hi all, I am trying to setup a formula to calculate employees terminated each months. I hate doing this and it sucks. I have a employee spreadsheets and it contains all the users (hire date, termination date). the data is enter in different columns. At the end of the month, I need to know how many employees are teminated. I have another column by months (Jan, Feb, .... December) I need to calculate how many people were layed off each month. Thanks for all your help. Try Sorting by the Month Column and then using Data | Subtotals to Count the Entries in the Terminated Column every time the...

Data, Group got error message Cannot shift object off sheet
I created a command grouping several columns together by going to Data, Group. When I go to click on the "-" sign to group. It gives me an error message;"Cannot shift object off sheet". Why? and how can I fix it. Maybe you have some objects that get rearranged when you hide the columns using your grouping symbols. This may have some suggestions that work for you. It's kind of a similar problem. XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns http://support.microsoft.com/default.aspx?scid=kb;en-ca;211769 Remember to look for comments and merged ...

Local Data Groups
Hi, Does anyone know if its possible to create local data groups for offline clients and somehow push the groups out to all users, rather than going to each outlook laptop client? Jo Savidge You can deploy the SFO client using group policies. When the user next logs onto the domain the SFO client will be installed ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm http://www.crowechizek.com/microsoft "Jo Savidge" <JoSavidge@discussions.microsoft.com> wrote in message news:CAE65DA4-C2E3-4F50-A78B-39C98940A129@microsoft.c...

graphing by group
I have 10 individuals in 5 groups. I would like to graph the individuals but keeping them within their groups. When I try to graph it ends up as 10 groups. I would also like to make each group of individuals a different colour. Thanks, Tim Hi Tim In Step 2 of Chart Wizard make sure you have Series by Rows or Series by Columns correctly selected. If you groups are in different columns us Series by Columns other wise use Series by Rows -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail address "Tim Pasma" <anonymous@discussions.microsoft.com> wrote in m...

converting text to a formula
is it possible to convert a text string to a formula? I have built a series of cells using CONCATENATE to produce a text string for a vlookup formula - is there a way to now use this text as a working VLOOKUP formula? Thanks. West. -- West ------------------------------------------------------------------------ West's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16123 View this thread: http://www.excelforum.com/showthread.php?threadid=277050 West Wrote: > is it possible to convert a text string to a formula? > > I have built a series of cells u...

Passing option group value to vba function
I'm sure I've done this before but can't for the life of me figure it out now, and hours of research online haven't helped. I'm simply trying to pass an option group value from a form to a vba function. I'd like the user to choose option1 or option2 and when they click a button, the function will run an if statement like "if option1.value=true then...." I've got 2 options named option1 and option2 and a frame called frame20, what else do I need? I've also tried bounding the selection to a textbox, but keep getting errors. Any help on the process? Tha...

managing distribution groups
Hello all, I am running exchange 2003 and I would like to know is there a way and I can have a regular user maintain my distribtion groups. If so how would I go about that? Thanks so much Mike You can grant permissions at the disty group level to allow ordinary members to add and remove users. Be careful to only do disty groups as security groups changed by users in outlook is a touch insecure. "Mike B" <MikeB@discussions.microsoft.com> wrote in message news:0F2F3709-0900-46B1-B87B-A2449FD44B75@microsoft.com... > Hello all, > > I am running exchange 2003 and...

save .xls w/o the formulas just the numbers
i have a .xls file that has been layed out very nicely (and i wasnt supposed to change it), but the silly bugger who designed it, didnt put it the formulas to relate various cells where totaling stuff. i thought i had seen someway to save the .xls with only the 'results' of the calculations rather than w/ all the formulas in it i put in. was that a figment of my imagination? thanks. jim Jim, What you are after is a copy of the values rather than the formula that sit behind the calculations. To do this is easy - Open the file and select all by clicking on an cell on the pag...

DSum by group
I have a textbox with a DSum function, but would like it to query only within each group. It seems that most other functions (e.g Avg) automatically calculate by group, but DSum doesn't seem to be. =DSum([MKTCAP],"QryFILTER",[MKTCAP]>100) The text box is within the Group Footer, and I'd like it to return a different value below each group. Thanks! "RussG" <RussG@discussions.microsoft.com> wrote in message news:697878DC-521A-4423-BB7F-97089D536EF2@microsoft.com... >I have a textbox with a DSum function, but would like it to query only >within...

Re: Protecting worksheet with grouped cells
I have a worksheet which has rows as well as cloumns grouped. After protecting the worksheet I am unable to expand and collaspe my groups. error:"You cannot use this command on a protected sheet..." Is there a work-around/solution to this problem? You can only enable outlines and have worksheet protection through a macro: With Worksheets("Sheet1") .EnableOutlining = True .Protect UserInterfaceOnly:=True End With The EnableOutlining property (and UserInterfaceOnly) is not saved with the workbook unfortunately, so this code would have to be run ...

Partially hidden Excel formula bar
In Mac OS 10.5, Excel 2008 won't show a complete formula bar. The left side is cut off. I've tried adjusting my display settings, deleting plist files, and uninstalling/reinstalling Office 2008. IF I log in as another user, I get a clear view of the whole formula bar. Any help with this will be greatly appreciated. Thx. In article <65B2FAAD-8DD3-4D0C-8AF6-DC5EF62C71E6@microsoft.com>, Eqbal00 <Eqbal00@discussions.microsoft.com> wrote: > In Mac OS 10.5, Excel 2008 won't show a complete formula bar. The left side > is cut off. I've tried ad...

Please help with Formula #2
Hello, Could someone please help me with the following? I'm trying to com up with a formula that will total specific cells if certai conditions exist For example Column D = Account No Column E = Amount (in Dollars Column I = Total for the Mont Everytime Column D has the Account Number 4 placed in it with th dollar amount in Column E, I need a formula in Column I that wil total the dollar amounts (of Column E) each time the Account Numbe is 4 (in Column D). Keeping in mind that Column D will have othe Account Numbers other then the number 4 throughout the month. Tha why I'm think...

Grouping and grouping within groups
I really need help, and help fast, so thank you ahead of time! I nee to know how to group and group within a group. I have been able to d a group, but once I group within the group and try to group anothe group withing that group it combines them. I know I can put a spac between the information so it doesn't combine them but that doesn' seem to be the right way to do things. For instance I have thre different area codes and I want to group them, then within those group I have five different colors of houses in that area code. I want t group the different colors within their area ...

Show Groups as Page Header
I used the report wizard to create a report based on a table. The table has 252 records. When I preview the report, it only shows 242 records. The records are sorted / grouped by district. In the table, the fields are District Business Name DBA Name Amount The wizard placed the District and field names into the District Name Header. To make the report more readable, how can I get the group headers to be the page headers? That is, if a group prints over more than one page, how can I get the page header to show the group name? You can set the "Repeat Section" property of the Gro...