Query #4

Hello,

I'm sure there must be a way to do this.

I have a group of sheets selected. I copy from one cell to another cell, the 
ref transfers to each sheet with the sheet it belongs too. However I need to 
change the cell to a different cell. eg at moment it something like, sheet 
one has  ='sheet one' H4, on sheet two it will have ='sheet two' H4, but I 
need to change the H4 to say H15, but I still want it to relate to the 
correct sheet. When I have all the sheets selected, and go onto the cell to 
change H4 to H15, it changes all the sheets to the one that is in front?? 
How do I just change the cell reference without having to go to each sheet 
individually, if its possible.

Thanks Mel 


0
mgsg (58)
6/13/2009 6:27:58 AM
excel 39879 articles. 2 followers. Follow

2 Replies
529 Views

Similar Articles

[PageSpeed] 19

Hi,

It sounds like you answered your own question.  Select first sheet, hold 
down the Shift key and click the tab for the last sheet you want modified.  
Select the cell with the formula and change it from H4 to H15.  It will be 
changed on all selected sheets and it didn't matter on which sheet you made 
the change.  Note at the top of the screen Excel is in [Group] mode.

Right-click the a sheet tab and choose Ungroup... to continue normal work.

-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Mel" wrote:

> Hello,
> 
> I'm sure there must be a way to do this.
> 
> I have a group of sheets selected. I copy from one cell to another cell, the 
> ref transfers to each sheet with the sheet it belongs too. However I need to 
> change the cell to a different cell. eg at moment it something like, sheet 
> one has  ='sheet one' H4, on sheet two it will have ='sheet two' H4, but I 
> need to change the H4 to say H15, but I still want it to relate to the 
> correct sheet. When I have all the sheets selected, and go onto the cell to 
> change H4 to H15, it changes all the sheets to the one that is in front?? 
> How do I just change the cell reference without having to go to each sheet 
> individually, if its possible.
> 
> Thanks Mel 
> 
> 
> 
0
6/13/2009 4:34:01 PM
Sorry it took  a while to come back.

I have  a group of sheets that relate to another group of sheets.

eg
Mary hours, Gill hours, tony hours, penny hours etc and then they need to 
feed certain information to a second group of sheets, eg Mary sales, Gill 
sales, Tony sales, penny sales etc. by selecting a group and putting on say 
Mary in Cell P3, =Mary C3, then I need all the other sheets to be the same 
but relate to the named sheet in the first group. I have worked out how to 
do this, but its when I need to change the one thing that I cannot work out 
like I want to change the =Mary C3 to C45, and want all the sheets to 
=respective sheets C45, but when I select the group they all become =Mary 
C45. Its a long process to do each sheet by itself.

I have managed a bit quicker by using the edit, replace but its still longer 
and I have to be careful not to change another function with 3 in it.

Thanks anyway.

"Shane Devenshire" <ShaneDevenshire@discussions.microsoft.com> wrote in 
message news:0D92925E-B664-4DFE-90A2-4A5C1314A0C5@microsoft.com...
> Hi,
>
> It sounds like you answered your own question.  Select first sheet, hold
> down the Shift key and click the tab for the last sheet you want modified.
> Select the cell with the formula and change it from H4 to H15.  It will be
> changed on all selected sheets and it didn't matter on which sheet you 
> made
> the change.  Note at the top of the screen Excel is in [Group] mode.
>
> Right-click the a sheet tab and choose Ungroup... to continue normal work.
>
> -- 
> If this helps, please click the Yes button.
>
> Cheers,
> Shane Devenshire
>
>
> "Mel" wrote:
>
>> Hello,
>>
>> I'm sure there must be a way to do this.
>>
>> I have a group of sheets selected. I copy from one cell to another cell, 
>> the
>> ref transfers to each sheet with the sheet it belongs too. However I need 
>> to
>> change the cell to a different cell. eg at moment it something like, 
>> sheet
>> one has  ='sheet one' H4, on sheet two it will have ='sheet two' H4, but 
>> I
>> need to change the H4 to say H15, but I still want it to relate to the
>> correct sheet. When I have all the sheets selected, and go onto the cell 
>> to
>> change H4 to H15, it changes all the sheets to the one that is in front??
>> How do I just change the cell reference without having to go to each 
>> sheet
>> individually, if its possible.
>>
>> Thanks Mel
>>
>>
>> 


0
mgsg (58)
6/19/2009 12:36:54 PM
Reply:

Similar Artilces:

Windows Update Agent 7.4.7600.226
My Dell Inspiron 1520 laptop with Windows Vista Home Premium has failed to load the subject update on 20 different occasions since 13Nov09. The "Error Details" is Code 80073712, which is nothing more than a general error code with no specific fixes recommended. Anybody have an idea as to how to resolve this issue? "BigRedWingsFan" <me@privacy.net> wrote in message news:uA$ddLKbKHA.3768@TK2MSFTNGP04.phx.gbl... > My Dell Inspiron 1520 laptop with Windows Vista Home Premium has failed to > load the subject update on 20 different occasions s...

Query Performance
Query Performance Hello, Using Access =9203=85 For those who know/understand how queries are processed=85 Is there a performance difference between these two queries? E.g., 1. Select * From myTable Where FirstName =3D =93*=94 And LastName =3D =93Doe=94 2. Select * From myTable Where LastName =3D =93Doe=94 I=92m building a query in code and am wondering if I should keep the asterisks or remove them when they stand alone like example 1. Does it matter if I=92m using Jet, Sql Server, or ORACLE with an Access fe? Thanks, alex On Tue, 16 Feb 2010 16:10:2...

Money 2004 #4
I see that some are using this latest version. So where is the Microsoft info on this product? http://www.microsoft.com/money/default.asp only lists 2003 versions. http://shop.microsoft.com/ only shows 2003 versions http://shop.microsoft.com/special/itsnew/ only says "Coming Soon" Where can I get a list of features so I can make an informed choice between Quicken products and MS Money products? Thanks Brian W In microsoft.public.money, Brian W wrote: > >Where can I get a list of features so I can make an informed choice between >Quicken products and MS Money prod...

How to select fields using either query criteria or a macro
I have created a database which logs operational data for an industrial water plant. I want to output a table of the data, based on inputs from the user. I've created a form where the user can enter a date range for the data they want to view, and a series of check boxes which correspond with the fields in the underlying table. In order to restrict the output data to the date range and fields required by the database user, I have created a query. My problem is that I can't seem to find a way to link the check boxes on my form to the 'show' checkbox in the query. ...

query for values not in use
I've got a table (tblSeat) which just holds the numbers associated with seats. There are 200 seats. Record #1 holds Seat 1, #2 2, and so on. I've then got another table (tblEvents) that holds events associated with people listed in a third table (tblMain). Each person in tblMain is also associated with a reference number (tblRefNum); there is one RefNum for a group of people in tblMain. I record into tblEvents that a person in tblMain who is associated with a particular RefNum was just seated in seat #. There are many events in tblEvents for each person in tblMain (one:Man...

RE: printerror: "unable to load client print control" 4.0 report
im getting this error operating with SQL Estandar Edition 2008 SP1 and Windows Server 2008 Sp2 anyone now how to solve this? Hi Erika, Please check following article for the same.. http://edwardsdnadynamicscrm.spaces.live.com/blog/cns!D5F22FDCDD6519D5!466.entry -- Regards, Mayank Pujara "Erika Ontiveros" wrote: > im getting this error operating with SQL Estandar Edition 2008 SP1 and > Windows Server 2008 Sp2 > > anyone now how to solve this? ...

Queries with Linked Tables
I have created two tables that are imported to an excel spread sheet which I have linked to an Access Database. I have created a field that links both tables and when I create a query for the two joined tables The joined table doesn't return any information. My question centers around what am I missing? What seems like a simple select query does not return values. My tables both have a distinct ID number that are joined when the primary table matches only the secondary table where the joined fields are matched. On Fri, 15 Jan 2010 12:13:02 -0800, PeterPfaffJCI <PeterPf...

printerror:"unable to load client print control" 4.0 report
I am using CRM 4.0 with SQL Server 2008, is there a fix for this? I did remove the KB956391 update on the client but that only allowed me to try to install the print control. When I try to install the control it looks like it finishes and then I get the same "Unable to load client print control". I am using Windows XP Prof. The server is Server 2003. RisingSunND, I'm going to give credit to my colleague, Dave Ireland, on this answer. --- • install the SRS redistributable package on the CRM server • install SQL SP3 on the SQL database and the SSRS server (if they are diff...

Combining Date queries
Ok, being new to Access 2007 I am floundering on how to solve this: I have a "Patient" table wich contains: ID Referral Source Status Start Date I want to produce a report that pulls a count of the Status grouped by Referral Source, for the month and the year. So the Report should look like: Referral Status 1 1 for the month 5 for the year Status 2 3 for the month 4 for the year.... I have approached this from several directions and reached a wall. I query the ID's for the patients with...

Including query dates in report
I have a report based upon a query. The query selects records based upon the value of a date field using a criteria: "Between [Type the beginning date:] And [Type the ending date:]" Is there any way I can collect these dates so they can be output on the report? IMO, you should be using controls on forms rather than parameter prompts. However you can set a text box control source to something like: ="Dates Between " & [Type the beginning date:] & " And " & [Type the ending date:] -- Duane Hookom Microsoft Access MVP "DJ...

Help with a Query 02-29-08
Let me paraphrase a query I want to do. First, I have a table called 'Components' This table has these fields: RecNo (Primary key) FileRec (Foreign key to Files Table) StandardRec (Foreign key to StandardTypes table) RefDes (String) AssemRec (Self-join to a RecNo) Here is my paraphrase: UPDATE Components SET AssemRec = the primary key in same table where in the refdes concatenation '<$-' + Refdes + '>' = RefDes AND the FileRec's are the same. So as an example, here is sample data. RecNo = 5 FileRec = 10 StandardRec = 17 RefDes = <$-156AF> AssemRec ...

Show only queries w/table source combo box
Is it possible to display only query names in a combo box where the queries source contains the table [Mstr List]? Thank you very much for any help. On Thu, 3 Jun 2010 05:13:46 -0700, Billy B <BillyB@discussions.microsoft.com> wrote: To do that you would have to write some VBA code to iterate over the Querydefs collection, inspect the SQL property of each Querydef object, and add the ones you like to the dropdown. Here is an off-the-cuff example. You would put this code in the Form_Load event: dim qd as dao.querydef for each qd in currentdb.querydefs if instr(qd.sql, &q...

range names #4
Hi everyone Suppose this example: a1;a2;A3 equals 5, 7, 9 b1;b2;b3 equals 2, 3,8 Name range for first range abc Name range for second range def There's a formula on cell c1 : Sum(abc) What i would like is : on cell d1, i input abc or def or another range name and on cell c1, without any macro but a formula, it sums what i input on cell d1, abc or def. Can you help me please? Tks in advance Pedro in cell C1 =sum(indirect(d1,0)) >-----Original Message----- >Hi everyone >Suppose this example: >a1;a2;A3 equals 5, 7, 9 >b1;b2;b3 equals 2, 3,8 >Name range for first range a...

Run macro for each record in a query
Hello All I have a table [practices], which contains details of 'client organisations'. From time to time I need to send an email to certain 'client organisations'. I have a macro called 'send_emails' which uses the SendObject command, which I use to send the emails (a button on a form runs the macro). The emails include a report that is specific to the 'client organisation'. Currently I select the 'client organisation' with a combobox, run the macro, select another 'client organisation', run the macro again, etc. etc. It would be extrem...

Graphical Union-Query Builder?
I have two moderately complex queries (each one has at least 7-tables and several more joins) that must be combined via a Union query. The results will be used in subsequent queries. Writing and maintaining SQL for this Union query is a last resort. How can I keep my application as intuitive and as low-maintenance as possible? 1. Does Microsoft or anyone else have a graphical user interface for building Union queries yet? 2. Does any tool exist that could read user-specified Select queries in Access and create the corresponding Union query? I've been considering writing such...

Query Criteria is not working
I am having an issue with the criteria of a query. I am limiting the data by a date range, an account code, and the number of loads. The date uses BETWEEN @FromDate AND @ToDate The account code uses NOT = 4125 OR NOT = 4121 The loads is > 0 With the above scenerio, I get no loads which are 0, but I still get account codes 4125 and 4121. If I put the account criteria in seperate columns, then I remove those account codes, but then I get loads that have 0. Any ideas? Cahd my mistake. I needed to use NOT = 4125 AND NOT = 4121 thanks Chad "Chad Came...

Email router 4.0 with 3.0 Server
Is it possible to use Email Router 4.0 - because of Exchange Server 2007 - with CRM Server 3.0? Thanks, Mikl=F3s On Thu, 10 Jan 2008 01:11:27 -0800 (PST), miklos <mikloshoffmann@gmail.com> wrote: >Is it possible to use Email Router 4.0 - because of Exchange Server >2007 - with CRM Server 3.0? >Thanks, Mikl�s Simple answer - No -- Julian Sharp Vigence http://mscrm.spaces.live.com ...

Query Criteria 04-15-10
Hi, Some of the fields I have in a table which is imported from a csv file are: Salesperson (has up to 20 names in it) Status (has Won,Lost,Submitted,Prospect,Did Not Proceed,Estimation) Status Date (has date) Value ($ value) What I am getting stuck on is a query to get the total value per Salesman for each status and also the count of each status, I would like it to show in datasheet form something like below. Salesman $won No. Won $lost No.Lost A $100000 5 $150000 3 B ...

Wave 4 uhh ohh
Ain't that funny, in WLM MS first got rid of all icons and now with Wave 4 they put back in tons of fancy icons and ribbons and such. What change in direction... Mail: Looks good to me at first sight . Photo Gallery: Nice improvements, seems to be pretty cool. Sync: It can't sync with the old sync. So how will I be able to sync between my old XP and my new W7? This feature is essential to me. Messenger: For me too much integration with all this "social networks" stuff. Need to find a way to switch that off completely. Yep.. they got a lot ...

In Access 2010, how do I create a SQL query?
In older versions of Access, there was a View -> SQL View menu button. How do I get the SQL View with Access 2010? I need to create a query which contains a join operation. I don't think I can do this with the Query Design view. ...

Server Memory and The Query Optimiser
Hi guys, Can anyone tell me if the query optimiser takes available system memory into account when deciding how to perform a given query. To take an extreme example, could it potentially come up with a different plan for a server with 256G or RAM versus a server with 2GB of RAM - simply as a result of the amount of extra RAM available? Similarly, is the relative load on the server at any given moment ever used to modify how the server will carry out a query at run time? Many thanks Simon AFAIK memory is not a factor in deciding the initial plan. But things like avail...

Query-based Distribution Lists not showing up in GAL
I've created several new query-based distribution lists (replacing standard DL's), and none of them are showing up in the GAL. Any thoughts on this? TIA. Anyone? I've got the same problem. Did you ever find a solution? Yes. Open Exchange System Manager, locate to Recipients\All Global Address Lists, right click Default Global Address List and click Properties. On General tab, click Preview… Is this query-based distribution group shown in the preview window? "ren14" wrote: > I've got the same problem. Did you ever find a solution? "ren14&q...

Manipulating or comparing xs:dateTime values in xpath queries with SelectNodes
Is there a simple way of compare dates? I am trying to create a list of perishable stock in an inventory that is near or past its expiration date. I've tried telling it to compare a node's value with a DateTime string, but that doesn't work. I've assumed that it is easier to manipulate today's date instead of the date in my ExpireDate element. Thankyou in advanced for taking the time to read this and possibly helping me Echo 8 ---------------------------------------------------------------------------- -------------------------------- My Code: Public Function FromElem...

Help with expression builder in a Query to retrieve wanted dates
I have 4 columns/fields of dates in a table: Field A, Field B, Field C & Field D. These fields can be either filled with a date or left blank. ABCD are all "Dates". I want to design a query or report to retrieve dates from Field A. The criteria is: Field A must be filled (i.e. there is a date, not a blank); and when dates in any field of B or C or D are not blank, A must be more recent than this/those date(s). How do I write it? I made "A is not null" in the first criteria. And then I think I should write something like when B or C or D are not b...

Updating multiple fields in one table via Update query
Hi, Have a table designed as follows equip1 text equip2 text equip3 text equip4 text equip5 text equip6 text equip7 text equip8 text equip9 text equip10 text What I need to do is to check each field and if the field is = "Tennessee" then update it to read "TN" Is there a way to do this in one update query vs having to write 10 different update queries? Thanks You've just discovered yet another problem with not properly normalizing tables. Having fields like that is referr...