Possible to have a block of cells not affected by sorts or cursor movements, etc.

I don't know what to call what I'm trying to do so will try to briefly
explain.  All these years, I've placed 1 static results cells in the
header row so that freeze panes would always show them.  So, the
descriptive cell and "total" dollar or figure amounts for the sheet
would be located in 2 cells in this frozen header.

Needless to say, this is not ideal.  It works because I'm forced to
keep stats down to just showing a global total so only 2 cells
affected.  I lose so much valuable sheet real estate as it is for the
one totals formula since the rows below these 2 cells can't be used.
Yet creating a whole separate sheet to show results not ideal, either.
It would mean toggling back and forth all the time and would mean
wouldn't just get 1 sheet for both the main sheet and one that shows
totals and subtotals figures, etc., for various categories.

Was hoping that the experts here knew of something in Excel  that I
obviously don't to get the best of both worlds - that would allow me
to have show more totals somehow yet still be able to add rows as
needed and sort any rows yet when moving around sheet can still
everything all the time.

Thanks.  :)
0
7/20/2008 3:03:56 PM
excel 39879 articles. 2 followers. Follow

2 Replies
577 Views

Similar Articles

[PageSpeed] 57

I don't understand what you mean by:

<<<"I lose so much valuable sheet real estate as it is for the one totals
formula since the rows below these 2 cells can't be used.">>>

If Row1 contains Text headers,
and Row2 contains totaling formulas,
*AND* Rows 1 & 2 are "Frozen Panes",
all the rest of the screen is available to display the entire rest of the
sheet when you scroll.
You're *only* losing 2 rows of screen display.

What am I missing, or what are you leaving out?
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"StargateFan" <IDon'tAcceptSpam@NoJunkMail.com> wrote in message
news:kck684hmb8e24kp48v87ivojma2ct0u9h5@4ax.com...
> I don't know what to call what I'm trying to do so will try to briefly
> explain.  All these years, I've placed 1 static results cells in the
> header row so that freeze panes would always show them.  So, the
> descriptive cell and "total" dollar or figure amounts for the sheet
> would be located in 2 cells in this frozen header.
>
> Needless to say, this is not ideal.  It works because I'm forced to
> keep stats down to just showing a global total so only 2 cells
> affected.  I lose so much valuable sheet real estate as it is for the
> one totals formula since the rows below these 2 cells can't be used.
> Yet creating a whole separate sheet to show results not ideal, either.
> It would mean toggling back and forth all the time and would mean
> wouldn't just get 1 sheet for both the main sheet and one that shows
> totals and subtotals figures, etc., for various categories.
>
> Was hoping that the experts here knew of something in Excel  that I
> obviously don't to get the best of both worlds - that would allow me
> to have show more totals somehow yet still be able to add rows as
> needed and sort any rows yet when moving around sheet can still
> everything all the time.
>
> Thanks.  :)

0
ragdyer1 (4060)
7/20/2008 4:43:53 PM
On Sun, 20 Jul 2008 09:43:53 -0700, "Ragdyer" <RagDyer@cutoutmsn.com>
wrote:

>I don't understand what you mean by:
>
><<<"I lose so much valuable sheet real estate as it is for the one totals
>formula since the rows below these 2 cells can't be used.">>>
>
>If Row1 contains Text headers,
>and Row2 contains totaling formulas,
>*AND* Rows 1 & 2 are "Frozen Panes",
>all the rest of the screen is available to display the entire rest of the
>sheet when you scroll.
>You're *only* losing 2 rows of screen display.

Yes, I suppose I could lose 1 more row to totals of some kind.  I
don't total in the way this colution suggests, but put a couple of
global total at the very end of the the print area at row A.

But adding a 2nd "header" row that isn't a header row but that shows
totals, well, that might work.  The cell merges might not always work
out but I'd gain extra info.  Hmmm, thanks.  Must mull this one over.
:)

>What am I missing, or what are you leaving out?
>-- 
>Regards,
>
>RD
>
>---------------------------------------------------------------------------
>Please keep all correspondence within the NewsGroup, so all may benefit !
>---------------------------------------------------------------------------
>"StargateFan" <IDon'tAcceptSpam@NoJunkMail.com> wrote in message
>news:kck684hmb8e24kp48v87ivojma2ct0u9h5@4ax.com...
>> I don't know what to call what I'm trying to do so will try to briefly
>> explain.  All these years, I've placed 1 static results cells in the
>> header row so that freeze panes would always show them.  So, the
>> descriptive cell and "total" dollar or figure amounts for the sheet
>> would be located in 2 cells in this frozen header.
>>
>> Needless to say, this is not ideal.  It works because I'm forced to
>> keep stats down to just showing a global total so only 2 cells
>> affected.  I lose so much valuable sheet real estate as it is for the
>> one totals formula since the rows below these 2 cells can't be used.
>> Yet creating a whole separate sheet to show results not ideal, either.
>> It would mean toggling back and forth all the time and would mean
>> wouldn't just get 1 sheet for both the main sheet and one that shows
>> totals and subtotals figures, etc., for various categories.
>>
>> Was hoping that the experts here knew of something in Excel  that I
>> obviously don't to get the best of both worlds - that would allow me
>> to have show more totals somehow yet still be able to add rows as
>> needed and sort any rows yet when moving around sheet can still
>> everything all the time.
>>
>> Thanks.  :)

0
7/22/2008 3:43:32 AM
Reply:

Similar Artilces:

blocking blank cells until other cells are compiled
Hello everyone. I'm working on several columns. Each column corresponds to a certai date. I would like the fields under the date cell to be "available only if the date cell is filled. How do I do this? Say in cell "A1" I have today's date and in "B1" tomorrow's (and s on). In cell "A2" I want to put the number of dollars I have spen during lunch and in cell "B2" I will want to do the same, but onl tomorrow of course. I would like to "compile" the whole month in terms of columns but would like NOT to be able to write in the...

Is it possible to detect...
Dear Sir/Mam Is it possible to detect that "the text in a MS-Word 2003 document has not been typed manually" or copied from other sources like notepad or clipboard, etc., Could it be possible to find, actually what i mean to say is when someone copied some text in notepad it will be stored in clipboard temporarily right? and now if they pasted that text into a MS-Word 2003 document, actually they didn't typed it manually right. At this situation is there any process to find that the text they just pasted into the word doc has not been typed manually rather it is brou...

Possibly going back to MM 2004, any advice?
Ever since I got Money 2005 I have had some big concerns about this software. The bugs are so many and obvious that I really can't believe they released it this way. However, I am a pretty tolerant person and can put up with a lot so I kept using it (about 2 or 3 months now). I decided not to return within the 30 days because even if I don't use it I felt the free year subscription to Experian Credit Manager and Money magazine was worth it. I am now seriously thinking of going back to Money 2004 but was wondering if there was a less painful way than opening up the old file a...

only the first 5 columns of a 10 column excel spreadsheet sort
How do I get the whole spread sheet to sort? There is a blue lox for the first 5 columns that limits the range of the sort. How do I remove it? Using Office 2003. Maybe if you remove the Data|list Select a cell in that blue box. Data|list|convert to range jrw562 wrote: > > How do I get the whole spread sheet to sort? There is a blue lox for the > first 5 columns that limits the range of the sort. How do I remove it? > Using Office 2003. -- Dave Peterson ...

Cell selection
Hi, Can anyone tell me why, when I select a cell near the bottom of a page, 3-4 rows seem to be sected as well? Thanks in advance Jason Hi, You are probably in Extend mode. Look at the bottom right of the Status Bar, look for Extend, its location and wording depend on version. If it is on press Esc or F8. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Boenerge" wrote: > Hi, > Can anyone tell me why, when I select a cell near the bottom of a page, 3-4 > rows seem to be sected as well? > Thanks in advance > Jason I am having this p...

Select a certain number of cells in a row
Hi, I'd like to have the macro to select row 5 to 10 in the active column. May I know what is the VB code to write? Regards, Valerie maybe... dim myRng as range dim myCol as long with activesheet mycol = activecell.Column set myrng = .range(.cells(5,mycol),.cells(10,mycol)) end with myrng.select ======= or with activesheet .cells(5,activecell.column).resize(6).select end with I'm not quite sure why you want to select that range. But for the most part, if you act directly on the range (and avoid .selects), you're code will work faster and be easier to modify. Dolph...

Cannot hide cells
I have a spreadsheet that is setup columns A-IV and need to hide columns but after a while get the message "cannot shift cells off sheet" any ideas its driving me nuts Regards Craig How are you hiding the columns? It sounds like you might be adding columns or moving cells to the right to hide them from the view. If you have items in Column IV you can not do this. If you are doing it as<format><columns><hide> about howmany times do you do it before you have the problem. (I cant make it happen on my computer) If you are doing it another way, What is it? &q...

text in cell shows up as ####
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Text in cells in a coumn shows up as ####, apparently because there is a large amount of text. When I mouse over, the text shows. It also shows up in the formula bar. How can I copy and or print the text in these cell? <br><br>When I try to save these cells as text and transfer to Word, they continue to print as ###. text in cell shows up as #### <br> I am totally new to this whole environment! A new iMAC and this software. <br> However, in trying to learn how to use EXCEL, I found that...

Printing large cells
I have a spreadsheet in which many of the cells are very large. When the spreadsheet is printed, not all of the large cell contents print. How do I get all of the cell contents to print? ...

copying text on sheet 1 to corresponding cells on sheets 2 to 5
I want the text entered in A8 on sheet 1(named Productivity) to automatically transfer to A8 on sheets 2 thru 5. I used the simple formula =Productivity!a8 in the corresponding cells but when there is no data in Productivity A8 I get a 0 in the corresponding cells. I would like the corresponding cells to remain blank if there is not text in Productivity A8. I am really new to excel, like only used it two weeks! Is there a simple fix to this? -- newbie at large! Hi A formula will always return 0 if it's refering to an empty cell. This formula will let the cell look empty if no...

Excel limited cell formats
I have run into Excel's limit on 4000 cell foremats. I have since split the excel workbook into two separate workbooks. My question is, how can I launch these from a template and have the two workbooks link with each other, My original workbook has 25 separate sheets within the workbook for a total of 3.25mb size. I have a whole slew of macros assigned to different command buttons. Mike Johnson ...

Adding Hyperlink to multiple values within a cell
My spreadsheet contains a list of people. The cell next to each nam contains multiple numeric values for identifying a specific piece o information. I would like to be able to click on one of those number (value) and a comment window pop up with the information associate with it, or be hyperlinked to the information further down th speadsheet. I want to avoid using multiple cells for this. Is this possible? Thank -- t2tru ----------------------------------------------------------------------- t2true's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=387 View this...

addressing envelope from data input form
I don't know where to start to try this. I have the following fields that make up the entire address: Fname Lname Fname2 Lname 2 Address City, State Zip Is there any way to "push a button" on a form and have it print an envelope for that record? Thanks in advance for any help. -- Posted via a free Usenet account from http://www.teranews.com Have you thought of using an MS template as a starting point? http://office.microsoft.com/en-us/templates/CT101172481033.aspx Dave Eliot wrote: >I don't know where to start to try this. > >I have the following fields ...

can i see the date the last time a cell was changed?
I am trying to figure out a formulla to make the date appear in one cell everytime anouther cell's data is chaged. Use a worksheet_change event to copy the cell address and put in a date stamp. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "JohnNuTek" <JohnNuTek@discussions.microsoft.com> wrote in message news:5085A544-CBF4-4B81-A244-B03DE9A0E9E6@microsoft.com... >I am trying to figure out a formulla to make the date appear in one cell > everytime anouther cell's data is chaged. There really isn't a worksheet formula to do that. Typicall...

can cells apply conditional formatting using the internal clock?
I am using excell to keep track of my production schedule and I wanted to know if there was a way to tie the cells in a worksheet to the internal date and time in the computer,so that the cells will update automatically. Example: Row A10 would be my production start date, Row A1 would be my projected finish date, I would like the cells in between to go from green to red as I near the finish date without manually inputting the date in each cell. Can you help me? Thyanks Set the normal format as desired (I selected a Pattern of Green). Select A1:A10, then select Format | Conditional Fo...

Splitting excel cells based on content
I have an excel workbook with a worksheet created by a dump from a database (DOORS in this case). The first column is unique, the second not. The second column may contain 0 to n identifiers that I want to look up on a separate sheet. The lookup is easy when the cell in the 2nd column has either 0 or 1 identifiers in it but when there are 2 or more (separated by a carriage return I think) my lookup fails as lookup is using the enitre contents of the cell. What i'd like to be able to do is to automatically (there's some 900+ rows in the worksheet) is to automatically insert additio...

Simple question about text within a cell
I can't seem to find the option that prevents text from covering the adjacent cell. For example if I type the folling in cell A1: All I want for Christmas is my two front teeth. Obviously that will overflow over the cell B1 (if there is no data in B1). I don't want to resize my column, I only want the cell to show as much text in A1 as possible without covering cell B1. I believe I've seen the option to do what I'm looking for but I can't seem to find it and I can't figure out how to look it up in the HELP file. Thanks in advance. Rick My simple solution ha...

How do i give each email account i set up its own inbox etc?
i can create different accounts but when i send/recieve all the mail from different accouts goes to the same inbox...not good... About the best you can do is create an inbox rule that moves messages based on the account it was received with. "joejnknsn" <joejnknsn@discussions.microsoft.com> wrote in message news:E487FD01-2110-4BEC-930B-10590F05E885@microsoft.com... >i can create different accounts but when i send/recieve all the mail from > different accouts goes to the same inbox...not good... ...

Is Auto Expansion (i.e., wrap text) of a cell possible when the cell's contents are based on another cell?
I am trying to display the results from one tab (Tab 1) in another (Tab 2). So, for example, tab 2 contains the formula "=Tab1!A2". However, when the results are too large to fit the cell in tab 2 (i.e., the cell that contains a formula that draws from a cell in tab 1), the wrap text feature does not work unless i first double click in the cell in tab 2. Is there any way around this? Can the wrap text feature work automatically somehow? Or will i need to double click in every cell that contains text that doesn't fit into the cell. Thanks for any suggestions, or VBA code, th...

cursor control in cells
Can anyone help please? For some reason i am no longer able to scroll across a cell by moving through teh arrow keys. The whole sheet moves instead. Can anyone tell me what I ahve done to lose this control please? Many thanx DAT Hi Dave You have probably inadvertently pressed the Skulk key. Press it again to take Scroll Lock off and you should be OK. -- Regards Roger Govier "Dave T" <datucker@ntlworld.com> wrote in message news:J8sUh.378$V7.132@newsfe7-gui.ntli.net... > Can anyone help please? > For some reason i am no longer able to scroll across a cell by ...

remove carriage return in multiple cells
Hello, I have an excel sheet with lots of data. Unfortunately lots of cells contain carriage returns. Is there a way to remove the carriage returns in those cells (more returns in a cell possible)? Thanks, Andy > Andy Select your range, then CTRL+H. In Find What, type 0010 (number keypad) while holding down ALT. Click Replace All. HTH, Andy Try running this macro: Sub RemoveCRLF() Cells.Replace Chr(10), "" Cells.Replace Chr(13), "" End Sub Afterward you may want to turn off Wrap Text (under Format, Cells, Alignment) for the affected cells. -- Jim...

is it possible to host multiple CRM databases on 1 SQL server?
Hi, Is it possible to host two separate (on premise) CRM server databases on one SQL server? I am trying to achieve the above scenario, but there can only be one MSCRM_config database on any one instance of the SQL server. The Implementation guide isn't too clear about what to do in this situation. - Can two CRM databases share the same MSCRM_config file? - Do I need to install another instance of SQL server for the second CRM database? Or is there another solution? Thanks Depends on your CRM installation. If you are running the Enterprise version, then you can add a new org...

Cell formatting: displaying lat/long coordinates
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C4366C.F3FDF050 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Rather than formatting a cell to display time in hours and minutes and = seconds, I would like to display latitude and longitude in degrees, = minutes, and seconds. e.g. 43=BA 25' 34" Is this possible in Excel 2000? ------=_NextPart_000_0008_01C4366C.F3FDF050 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W...

sorting activities for each lead/contact by actual end dates
When I view the activities associated with each lead and contacts, the actual end date are not shown. Does anyone know how I can dispaly this field? The activities list in workplace does show the actual end dates but we'd like to see that when we access the list through indivial contact/lead record. Thanks, Victoria Hi Victoria, There is no good out of the box way to do this in MSCRM today. We're working hard to fix this for v2.0. Many of our customers have worked around this issue by registering for a post call out on the activity creation, and then having a custom object wh...

Sorting and Displaying data
I have some data, a list of names with locations and a single number. Something like: Steve 4 Boston Mary 3 Washinton Al 6 Boston Frank 2 Chicago Mort 8 Boston I want to have a seperate sheet that automatically lists the people from each city, sorted by the number. I would need this list to update 'on the fly' as new names are added with new numbers. So the output should be something like BOSTON WASHINGTON Mort Mary Al Steve etc. Thanks for any advice... ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup...