Last record in detail
Microsoft Office/Access MVP
"TaylorLeigh" <TaylorLeigh@discussions.microsoft.com> wrote in message
...find column with text data in row 1
I have the following macro which bounces me back and forth between b:k
Private Sub Workbook_SheetBeforeDoubleClick( _
ByVal Sh As Object, ByVal target As Range, Cancel As Boolean)
If Not Intersect(target, Range("B:J")) Is Nothing Then target.Offset(0,
If Not Intersect(target, Range("M:U")) Is Nothing Then target.Offset(0,
Now I would like to build on that. My K Column is my delimiter with
the static text 'Totals'. I would like to be able to have the
functionality of the above without have the number of columns static,
so...Creating a matrix from columns
I have an Excel Sheet A with 3 columns. Col A=Site; Col B=Part; Col
C=Quantity. Rows of site and parts data are added daily and may contain the
same site or same part number along with quantities for each row in Col C. I
then create Sheet B with unique sites and unique parts so that I now have a
matrix of unique sites in Col A and unique parts (transposed)in Row 1. In
the data cells of the matrix, I need to know how to reference back to the
Sheet A to summarize the quantities (Col C) of each unique part located at
each unique site (VLOOKUP? SUMIF? Some other database function?) Any...adding digits to front/end of fax numbers
how do i add "1" before each fax number and ",,7741" at the end of each one?
we have to dial one to dial long distance and we have a code to use the long
distance in our office. please help quickly. Thanks
Fax number in A1:
If you want a space after the 1:
"Luke" <Luke@discussions.microsoft.com> wrote in message
> how do i add "1" before each fax number and ",,7741" at the en...sorting #42
I have a column of names with last name first. I want to have Excel sort
these so that the FIRST name is first. Can this be done?
You'll need to pull the first names into a different column. While you can
do that with formulas, it is usually faster & easier to insert two new
columns, copy & paste the names into the left new column, and then use
Data|Text to Columns on that copied data, splitting the names where there's a
comma or space, depending on how the list is set up.
> I have a column of names with last name first. I want to have...How do I zip sort mailing list when I have 5 digit AND 9 digit zip
I have a mailing list with zips in XXXXX and XXXXX-XXXX all mixed in. When I
sort them, it seems to put all the 5 digits on the top half and 9 digits on
the bottom half. I have made sure the column settings are set to
special/zipcode. I have also tried setting them as Zip Code + 4 but it
converts all the XXXXX versions to 0000X-XXXX. What am I doing wrong?
Convert to TEXT
Microsoft MVP Excel
"Greg" <Greg@discussions.microsoft.com> wrote in message
news:97AB96D9-EAE6-4E6F-8AA1-1DE4137EDA06@microsoft.c...a-z sort on sheets
can anyone tell me pls if it is possible to sort my worksheets
alphabetically? I have about 30 or so which I would like to sort after
"Dale" <email@example.com> wrote in message
> can anyone tell me pls if it is possible to sort my worksheets
> alphabetically? I have about 30 or so which I would like to sort after
> renaming them.
...How do I modify an existing worksheet to remove columns & contents
I am working with a very large spreadsheet and want to modify it using only
certain columns and data in order to keep from re-doing the entire worksheet.
ANybody out there in cyberspace got suggestions or good reference sources I
There are just too many scenarios to ponder without getting some more details
on what you wish to do.
Please be a little more specific in your description.
Gord Dibben Excel MVP
On Wed, 9 Feb 2005 18:15:06 -0800, "Gil Gray" <Gil
>I am working with a very large spreadsheet and want to m...How do I link columns so data flows from 1 column to another like.
Is there a way to link columns in Excel, so that data that's entered flows
from one column to the next? There's a function similar to this in Quark.
Anybody know anything about this?
If I understand the question correctly, and the information is being typed
into the cells, you could try this:
Select a block of cells, say A2:C10
Then, type a value. Press enter. If yuo press enter after each value
entered, when you enter a value in A:10 and press enter, you're active cell
will be B2.
"M. Frazel" wrote:
> Is there a way to link columns in Excel, so t...Using Index across several columns
I have this table that span from colum E:J
1-4 14% 113-116 42% 225-228 71%
5-8 15% 117-120 43% 229-232 72%
9-12 16% 121-124 44% 233-236 73%
13-16 17% 125-128 45% 237-240 74%
The table is complete for 365 days, I just omited the rest to save
Cell A1 has number of days.
I want to find the percentage that matches that day.
I have come up with this formula:
Which works fine, IF the number of days is less than 16 which is
normal because index only looks in column F.
I would like to expand this formula to also look...email addressbook name order (last, first)
I'm using stand alone Outlook 2007. When I create a new email then hit "To"
and my address book come up all the names are arranged first, last. How do I
order them last, first?
Change the sort here: Tools menu > Account Settings > Address Book tab >
Highlight Outlook Address Book > Click Change
"Dave R" <firstname.lastname@example.org> wrote in message
> I'm using stand alone Outlook 2007. When I create a new email then hit
> "To" and my address book come up...Comparing multiple columns
I need to compare two excel sheets(sheet1,sheet2) where in i have the
employee hours worked for a fortnight. One sheet is generated using an
exe file and the other is the one that has got uploaded to backend.
Need to find whether the hours worked match one to one.
My sheet 1 would have data like this....(without the header)
0 4 8 6 8
6 0 5 3 0
8 8 8 8 0
4 4 4 2 0
8 8 8 8 0
11 11 10 9 0
0 0 0 0 0
0 4 8 6 8
6 0 5 3 0
8 8 8 8 ...How do you change the size of a range of cells in a column/row
without changing all cell sizes in those rows or columns
You can't, width and height are properties of columns and rows, not
cells. You can merge cells in two columns or rows to combine their width
In article <0F00CD03-E47C-44AD-9185-89BEEF4D3FEB@microsoft.com>,
SOkoll <SOkoll@discussions.microsoft.com> wrote:
> without changing all cell sizes in those rows or columns
...Change Default Columns?
I use MS Outlook 2007 SP2.
Is there a way to make all folders, or, at least all NEW folders have
a standard set of columns, which is different than the default
For example I don't need the "Status" or "Size" columns. Rather than
delete them after I create a new folder, I'd like the default
configuration NOT to include them
On Jan 31, 4:50=A0am, Don Green <dmg...@gmail.com> wrote:
> I use MS Outlook 2007 SP2. =A0
> Is there a way to make all folders, or, at least all NEW folders have
> a standard set of columns, which is differ...Can I add more columns beyond col. IV?
I realize this may be a elementary question to most, but I hope there'
I'm making a spreadsheet that requires about 10 more columns (beyon
column IV) than the normal new Excel file allows (to the would-b
column JE). Is there any way for me to extend the sheet and add mor
Rick_B's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=596
View this thread: http://www.excelforum.com/showthread.php?threadid=39343
No, you are limited to 256 columns
Regard...Find MAX of column from using 2 criterias
Spreadsheet (duh) with 3 columns:
Month, Vehicle Licence #, Odometer readings.
Now looking at the Month and Vehicle Licence #, what is the lowest odometer
reading and also what is the highest odometer reading, so as to calculate the
Do not want to use Pivot tables - as columns will grow.
Do not want to use array formula as end user is as useless as putting butter
on bread with a toothpick.
Thanks in advance.
Try this array formula.
Change MIN to MAX to get the max value
D1 is a number i.e. 4 fo...using row number or column letters in formula
Now I'm trying to use a row number in a formula so I don't have to type the
same formula individually on each row 12 times!
My formula is currently this
Sheet'!$F$61&"...Largest non-blank sequence in row or column
I'm trying to create a formula which will count the largest number of
sequentially non-blank cells in a column or row.
e.g. if the row had the following columns:
|y|y|y|y| | |y|y|y|y|y|y| |y|y|y|y|y|
the value would be 6 because it is the largest number of "y" values
next to each other. Any Ideas please let me know.
I'm using the formula to work out the longest number of days I do a
particular task in a row. I update the spreadsheet daily and put a "y"
in the row if I do that task.
On Mar 11, 10:57 pm, David <david.mcdow...@gmail.com> wrote:
> I'm t...Lock columns
I have this excel spreadsheet that I need to distribute to people to use to
fill in but because I need to convert this spreadsheet to a specific format
once they send it back to me I don't want them to be able to change the
column width. Is there a way to lock the column width but still have people
be able to enter data and be able to still use the tab key to move from cell
CTRL+A / Format / cells / Protection / Uncheck Locked. Then Tools / Protection
/ Protect Sheet - password optional.
Ken....................... Microsoft MVP - Excel
...Copy range to different columns depending on cell value
What I would like to do is look at a range (A6:IXXX), where XXX is the
last row of the range, and if there is NOT an "X" in I6, copy that row
to another range (K6:S6), this would look at each row and copy to the
new range if "I" is empty.
Is this possible to do?
Dim I As Long
Dim aLastRow As Long
Dim dLastRow As Long
Dim ws As Worksheet
Dim aRange As Range
Dim dRange As Range
Set ws = Worksheets(2)
aLastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
For I = 6 To aLastRow
Set aRange = Range(&q...Insert symbol as a header for a column
I would like to insert 1/5 as a fraction header for a column. You can select
from the symbol menus say for instance under the Airal choices menu as there
are a small number of fraction signs to select. I do not want to particular
type in 1/5 or 1/5th as I want the looks that are obtained from using the
Does anybody know how you might achieve this?
The code for one fifth is 8533.
Insert the following UDF:
Function unicd(r As Long) As String
unicd = ChrW(r)
Format A1 as Ariel MS Unicode
In A1 enter:
Us...Formatting a Column....
I want to do a continuous +1 in A1 thru A17 - ex. A1 = H03860, A2 = H03861,
A3 = H03862 ------ yes I feel very stupid asking this!
Type the first 2 numbers in A1 and in A2 then click the bottom of cell 2 and
drag down has far has you like.
They will increment by themselves
"dawn brist" <email@example.com> wrote in message
>I want to do a continuous +1 in A1 thru A17 - ex. A1 = H03860, A2 = H03861,
> A3 = H03862 ------ yes I feel very stupid asking this!
I should h...Comparing stacked columns
I am trying to create the following chart. How do I do it in excel.
Is there and additional template/addin that would help in this?
Widgets in the market(by geography) My Widgets
I am trying to create two stacked bars on the X axis, one for "Widgets
in the market" and "My Widgets". I then want to depict that "My
widgets" is x% of Widgets in the market. I would like to be able to
draw a line from one cell to another (comparing the reduction in share
I wish I could upload a picture somewhere!
Tha...How to convert contents of column from numeric data type to text
you know how sometimes when you paste a value into a cell it reads like a
number but Excel thinks its text and aligns it to the left and gives you a
little dropdown menu with options such as keep this value as text or to
convert it to numeric data. I have a whole column of mostly numbers (and
excel is storing them as numeric data) and want to convert them to text. I
don't have that menu option available since excel thinks they're numbers.
Is there a way to convert numeric data to text?
If you don't mind a helper column, then you c...CF for Row when CF is applied to Column
I've read through the postings but can't find an answer to my issue. Seems
simple enough but I can't figure it out. I have a spreadsheet which I've
applied conditional formatting to a column so that if any text appears in a
cell, to color that cell yellow. What I'd like is for the row from A:O be
colored yellow, not just the J cell.
I'm using 07. Could someone please explain the steps in English to me or
point me to the appropriate post? Cuz, I ain't figrin it out!
Thanks for the help in advance!
I would give my left hand to ...