Wrapping sheets within a workbook
I am working with raw data in Excel 2003 in a workbook where there are 3-5
sheets completely filled with data, all 65536 rows. There are duplicates
scattered throughout the sheets. Is there a way I can link all the sheets
together so that when I do a sort, it will sort all the rows on each sheet
within the workbook?
You would need a macro to do that. This is an example of a Merge Sort
algorithm - you would need to sort each individual sheet, and then you
could look at the topmost item in each sheet and decide which record
should be written to a new sheet (and adjust a counter for the sh...extracting icons
Is there any way to extract an icon from an application?
Take a look at the ExtractIcon[Ex]() API
<email@example.com> wrote in message
> Is there any way to extract an icon from an application?
...Automatically update pivot table when switching to sheet that the pivot table is in
What would the code be to automatically refresh a pivot table when you
open the sheet that the pivot table is in? Today, I changed data in
the sheet that the pivot table draws from and forgot to refresh the
pivot table. Fortunately, I caught my error in time and refreshed the
table before I gave it to the boss. I would like the pivot table to
refresh whenever I switch to the sheet that the pivot table is in.
Try using the Worksheet_Activate event to update the PivotTable like:
Private Sub Worksheet_Activate()
<b...transferring data in rows of one table to columns of another table
We are working with FCC station data that puts some simple numeric data in
one file, arranged in a number of consecutive rows for each station. The next
station's data follows consecutively. Each group of rows that are common to a
station share an index number, while the next group uses its own separate
A separate file contains the main information fields in a table of separate
rows, or records, along with a matching index number. We'd like to move the
numeric data in a group of rows that share the index # for a station, to a
series of new fields add...extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet
--where two conditions/catogories must apply so that a third column where i
have inputed hours, will total for those conditions only... can anyone help?
Please spell out what you want we are not mind readers
Bernard V Liengme
remove caps from email
"Jovetta" <Jovetta@discussions.microsoft.com> wrote in message
>I need to know how to take different information from within a spreadsheet
> --where two condi...Q for Stephen LeBans or Allen Browne re: Row NumberVer2
Stephen or Allen,
I have been trying to recreate the row numbering query you have in
but the numbers in the query do not increment. The return value for
the query when
it is run, is zero for each record.... what am I missing?
On Oct 27, 3:30 pm, Opal <tmwel...@rogers.com> wrote:
> Stephen or Allen,
> I have been trying to recreate the row numbering query you have in
> your example:
> but the numbers in the query do not increment. The return value for
> the query...TIME SHEETS
I am trying to design a template to do timesheets with. How do I create a
formula that will read in time, out time, lunch time then total for the week?
(8:00am-12:00 then 1:00-5:00 = 8 hours?
See Chip Pearson's site.
Gord Dibben MS Excel MVP
On Fri, 28 May 2010 11:54:58 -0700, puzzled
>I am trying to design a template to do timesheets with. How do I create a
>formula that will read in time, out time, lunch time then total for the week?
> (8:00am-12:00 then 1:00-...how do i form 2 lines in 1 row?
I'm guessing you're looking to force text in a cell to wrap into the next
line of the same cell.
If that's true......
Edit the text and move the insertion point to where you want to wrap the
Hold down the [Alt] key and press [Enter]
Does that help?
Microsoft MVP (Excel)
"MSEXCELROOKIE" <MSEXCELROOKIE@discussions.microsoft.com> wrote in message
In one cell type this is line one then Alt + ENTER then type this is line
The Alt ...Excel: extract and sum numerals from mixed text/numeral cell range
I have a large (30x20) grid of cells with data, and I want to extract
and then sum up certain numerals from this entire range. The catch is
that the data is mixed numerals and text, as you'll see below.
Here's an abbreviated 3x3 example, with a value in each of the nine
V7.1 T H
P1 A T
B V3 P4.5
If I just wanted to sum up the instances of "T" appearing, I could use
COUNTIF() for the entire range to come up with answer ("T" appears 2
times). Easy enough.
But, what I'm trying to accomplish is to sum up the numerals associated
w...Extract emails from cells with text
I have a row in column A which includes an email address in the text that I'd
like to extract to column B. Is there a formula I can use to accomplish
extracting the email address only to column B?
Here's an example of different cells in column A:
Please email firstname.lastname@example.org to contact us......
Schedule an appointment for assistance, or email email@example.com with your
All on one line:
(" ",A1&" ",FIND("@",A1))-1)," ",
REPT(" ",...my printer ejects a blank sheet prior to printing with EXCEL
...extracting individual numbers from "ranges"
I'm trying to get EXCEL to extract stock prices quoted in individual cells
as 52-week ranges --e.g. 11.76-19.90-- and do the following calculation:
(higher price - lower price)/lower price
Obviously, this a problem because each cell appears to handle the range as
text or something. Is there are way to get EXCEL to handle the "text" (or
however else it is interpreting what's in the cell) as individual numbers?
Great thanks for any help.
do your entries always look like
if yes you may try the following formula in the adjacent row (lets
assum...Query and Extract
This seems basic, but I just cannot get it. While using SQL Server 2005 and
the Microsoft SQL Server Management Studio, I created a database called
AssetQuote. Inside I have on table called assetquotes.
From there, I have three columns, (date, quote, author)
The column type for date is datetime and the other two are just text.
My Query is something this:
INSERT INTO AssetQuote (date, quote, author)
VALUES('052010','No act of kindness, no matter how small, is ever wasted.',
When I run the query I get this result:
Msg 208, ...Prevent printing a sheet
I have a workbook with 10 worksheets. On two of the worksheets only I want
users to be able to view them but I don't want them printed. Is there a way
to do this so only these 2 worksheets won't print. Thanks
BeforePrint code will prevent if users have enabled macros.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sht As Worksheet
For Each sht In ActiveWindow.SelectedSheets
If sht.Name = "Sheet1" Or sht.Name = "Sheet2" Then
MsgBox "you are not allowed to print " & sht.Name
Cancel = True
...No typed numbers coming up on excel sheet
Suddenly we cannot get the right hand numbers on the keyboard to register in
an new Excel sheet and previous sheets will not accept new work on them in
one of our two user accounts only. This is being done also on old Works
spreadsheets. The Works file has beeb uninstalled and reinstalled but there
is no improvement. The numbers along the top will appear! Have we turned
something off inadvertently somehow? We would be glad of any opinions. db
Num-Lock turned off, perhaps?
"db" <firstname.lastname@example.org> wrote in message
news:0F252AEF-980A-43EC-8EEA-4E8FF1B5...extracting hours from a sum of time
I have a cell which sum a bunch of cells containing an elapsed time.
The sum totals to more than 24 hours.
I have to a cell with a cost per hour and I need to caclculate the
The problem is that the hour function returns values in the range of
0-24. My current sum is 25:30 and the hour function return 1 and not
Any ideas how to bypass it?
It "returns" the correct number, but doesn't show. Format Custom as [h]:mm and it will.
To convert to a number that you can multiply with an hourly rate, first multiply by 24; times in Excel are fractions of a day and
a day is s...Extract Text
I have this text in a field:
and I need to separate only the data after the six first data:
How can I do it?
Thanks a lot!!!
Take a look at Access HELP for the Mid() function. Open a query in design
view and add a new field something like:
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
<email@example.com> wrote in message
news:11893397...Extra Row in Defined Print Area
I am working with Excel 2000. I have a spreadsheet that has some 1000 rows
in it. I want to set the print area for rows 850 - 950 and that works fine
EXCEPT that row 504 also gets included in the print area definition. I have
cleared the print area repeatedly, I have set the print area repeated and in
every case regardless of what rows/columns I include in a newly defined
print area, row 504 gets included as the very first row of the print.
If I don't define a print area, row 504 does not get printed. But as soon as
I set a print area regardless of where it is in the spreadsheet, row 504
...Can I extract unique cell values from every nth column?
I have a range that covers B5:XA160. For each row, data is entered in
11-cell groups: date, some numbers, description (a text value), more
numbers, and more numbers. At first it was enough to merely count how
many times certain descriptions appeared, because those were the only
ones we would see - or so the story went. Now, I need to extract the
unique descriptions AND provide a count!
Actually, I'm cheating a bit. I'm using SUMPRODUCT to return the
number of times the expected descriptions appear, and by subtracting
these from the total number of text values I get a count of &quo...Formatting part of text in a Cell & shortcut copying Row hieghts
1.Is there a macro or otherwise to format i.e Bold,underline etc a part
of text in a cell?
2. Is there a shortcut way of applying hieght of a specific row to other
After a hard fought 1-1 draw, "The best side drew."
- Bill Shankly (Legendary Liverpool Manager)
one way for your first question: simly select the specific text part in
the formula bar and apply your format
> 1.Is there a macro or otherwise to format i.e Bold,underline etc a
> part of t...Delete rows with names
Dim Going As Long
Dim Far As Long
Dim Pass As Range
Going = ActiveSheet.Cells(10000, 2).End(xlUp).Row
For Far = Going To 1 Step -1
Set Pass = ActiveSheet.Range("N" & Far)
If Pass.Value = "Every name but name entered in textbox" Then
I am looking for assistance with this code. I’d like to add additional
steps to the start of it. Ideally I would like to run this macro and have a
textbox appear with a message that reads “What’s your name?” Below this
question is a...Rectangle border for the selected row in DataGridView
I'm using a DataGridView to bind my DataTable. The selection mode is
FullRowSelect. I wanted to show a rectangle border for the
I saw in some cases the cell alone is shown with the border, but still
don't know for which setting... :-( I wanted to do the same for the
Thanks in Advance
First, may I suggest, you just do with out it?
How critical is it after all?
Second, I am not sure it make sense
for the entire Row to have a FocusRect,
since it indicates KeyBoard focus/input,
and the whole row does...i forgot the password used for the exel sheet
I had used a password as a security in the excel sheet n now i have forgotten
my password so what should i do its an important data.
Google is your friend?
Many available password 'crackers' for Excel workbooks and worksheets, some
free, some not. Some not-free ones permit a download of demo that will
provide something like first X number of characters or every other character
of a password or limit the length of the password to be detected. But those
might give you hint enough to jog your memory.
Typically such crackers don't even tell you the original password, but the...Excel sheet: scroll down, but leave first lines visible
Have to solve a problem fast:
Excel sheet has in the first 4 lines text that i want to have visible at all
( fix on top ) even when i scroll down on the sheet......
How can i do it.
Thanks for your help
either here in the NG or to: wette-2(at)ballonfahrer(dot)ch
Select the Row under the last one that you want to remain visible and the
select Window > Freeze Panes
If you want the first 4 line to also print on every page then select File >
Page Setup > Sheet > Rows to Repeat at top enter 1:4
In Perth, the ancient capital of Scotland
and ...cheat sheet
I'm looking for an Excel cheak sheet or quick hints list.
You mean shortcut keys?
Debra Dalgleish's site:
Chip Pearson's site:
> I'm looking for an Excel cheak sheet or quick hints list.