Coloring rows to show groups of records
I have a report where one or more rows belong to a group and I'd like
to color rows to help the reader easily see what rows belong together.
I'm trying to do this by writing a VBA function that tests for changes
in the column that distinguishes the groups. I then call this
function in a Conditional Formating condition to set the row color to
the alternate color from the row above. This seems to work but Excel
There must be a standard way to do this but I haven't found anything
yet, so any ideas appreciated.
how are your rows separated. Probably no need for ...Can multiple cell results be displayed in a single cell?
Is it possible to display the contents of multiple separate cells in another
If the results of several cells say B1, B2, and B3 are the amounts $10, $15
and $20, I would like to display them in cell A1 as follows:
I could do this statically by using text and the 'Alt|Enter' function within
the cell. However, these amounts will change from time to time so I'd like a
Is this possible or not?
Thanks for any help.
="$"&B1&CHAR(10)&"$"&B2&CHAR(10)&"$"&B3&am...Merge cells tool is not getting highlighted
pls find me an answer for the question.
Perhaps 2 possibilities:
Shared workbook*, or the sheet is protected**
*As listed in Excel help for: Limitations of shared workbooks
** Try unprotecting via: Tools > Protection > Unprotect sheet
GMT+8, 1� 22' N 103� 45' E
"viswanathan" <email@example.com> wrote in message
> pls find me an answer for the question.
Re: Merge cells tool is not getting highlighted
Is your w...floating counts
My spread sheet has several colums using COUNTA. For example:
=COUNTA(A3:A105). Each week I might insert 30 or more rows that I want
included in the count. Is there an expression in the COUNTA formula that I
can use to "float" the formulas to a row just below the last row containing
data so I do not have to modify the ending row number (A105 in this example)
when new rows are inserted?
Assuming your formula will be in A106 to start and you always want to start
with the 3rd row:
"Bob in Granger" wrote:
> My spr...Counting question
Column 1 contains a product ID and could contain just one entry or several
entries depending upon how many reels of that product ID we have in
inventory. Column 2 has the reel quantity in feet. I need to get a total
quantity in feet for each product ID.
Column 1 Column 2
Prod ID Feet
I need the total product, in this example ABC = 450 ft and XYZ = 100 ft. I
have over 3000 lines and over 600 products I can't do it manually. I think a
pivot table would do it but I'm not sure...
st...line spacing in cell
I would like to type 3 pieces of text, each on a seperate line in one cell.
I've looked under cell format
Press Alt-Enter where you want to start a new line!
„dlh” ezt írta:
> I would like to type 3 pieces of text, each on a seperate line in one cell.
> I've looked under cell format
Thanks so much. Works great!!
> Press Alt-Enter where you want to start a new line!
> „dlh” ezt írta:
> > I would like to type 3 pieces of text, each on a seperate line in one cell.
&...Different counts on same query
I need help on finding out how to run three different counts on one query. I
have a table set up to show Date
Within this query I have the following fields:
Date | Method | AgedDays ... taken from TotalInventoryTable
my goal is to say the following in one query.
Date Method CountofAge CountofAge>30
Date Calls ## ##
Date Corr ## ##
All on one query... Is this possible.
Thank you everyone.
Message posted via http://www.accessmonster.c...Deleteing cells in Excel
I've pasted the same info off a web page into three columns on a spreadsheet.
I neet to get a title, hyperlink and decription to go across the page, not
down. By pasting it three times, I just have to clean it up. (The titles
all say "Title:" in front of them.) But cleaning up is a real pain. Is it
possible to delete from the auto sort drop down box? (Or any other way?)
That way, in the third column, I could delete all the titles (since they only
go in the first column).
If I am correct in assuming that the data Title, Hyperlink and Description,
could be copied onl...Cell is too small?
I have a lot of text in my cell. After a while it stops wrapping the text, so
I can't print what is within the cell. I don't know why this is hapenning.
how can I fix this?
Cells were never intended to store large volumes of text. They have a limit
of 409 points in height (roughly 34 lines of 10 pt. type, assuming 120%
leading). There is also some technical glitch with content that falls within
the 256-1024 character range.
If you need to include that amount of text as a unit, you might want to
consider using Cell Comments or drawing Text Boxes/AutoShapes to store,
...256 color palatte in excel
How do I access the 256 color palatte in excel
In article <9D7D2EDA-96CD-49EB-93E3-9335F5B7A505@microsoft.com>, "=?
Utf-8?B?U3RpY2sgRnJhbWU=?=" <Stick Frame@discussions.microsoft.com>
> How do I access the 256 color palatte in excel
XL supports only 56 colors in its palette.
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
"Stick Frame" wrote :
> How do I access the 256 color palatte in excel
On the face of it, perhaps ...
Click Tools > Options > Co...cell gridlines
i cannot make cell gridlines appear despite checking the gridlines box and
selecting a colours. any offers?
Your cells are formatted to be filled with the color white. You need to
format them so that there is "no fill"
> i cannot make cell gridlines appear despite checking the gridlines box and
> selecting a colours. any offers?
...Counting a count
How do I count a count??
This is what im trying to do:
I have a database with a table named tblMain and has incorporated everything
within that table but contains info from about 8 other tables. I have not
doesigned this database and it appears to have a very poor structure...anyway
There are three fields involved in this process...'Request' (either
'dispensation', 'waiver' or 'dispensation and waiver'), 'Department' (which
consists of 7 other drop down menu options) and 'RequestStatus' which
contains the status of each request by another...count question
I have a table with four columns and 3000 distinct records.
One of the aforementioned columns is named case_number with many
duplicates in that column.
I created a query to count the number of distinct case_numbers within
the column (something that I do all the time w/ SQL in ORACLE).
Access, however, returns a count of all case_numbers, including
I created this workaround:
SELECT Count(case_number) AS COUNT
FROM [select distinct case_number
FROM table_1]. AS [table_1];
I'm wondering why Access won&...fill several sheets with data from other workbooks matching criter
So far, I was able to make a macro to filter data from a project log sheet in
a workbook, and copy/paste the rest of the data in each row based on criteria
in column A to the corresponding worksheets with a name matching the data in
column A on the project log. But now what I need to do is make a macro that
will do basically the same thing, but will instead run on sheet(1) of any
other workbooks with a filename that contains "RetailProjectLog" in a certain
folder location on a network share. Is this possible? If so, how can I do it?
I wish I could attach my sample work...I change a % in a cell all values in the columns dont adjust 2 new
You're not giving us much to help you but will try.
Press F9, if that works , goto >Tools>Options>Calculation Tab>Set
calculation to Automatic not Manual.
If that don't work, show us your formula, maybe a bit of your layout
anything will help to help you.
"neety" <firstname.lastname@example.org> wrote in message
...goto special blanks does not return any blank cells
I have a sheet that has a lot of blank cells in column A and data in colum
B. I want to automatically fill in theblanks in A with the information from
the cell above, so I can use the data in B in relation to A.
Try Debra Dalgleish's nice coverage on this at her:
where she lists 2 methods to do so
(one manual, the other programmatically*)
*Sub FillColBlanks by Dave Peterson
Please respond in thread
"Lobo" <Lobo@discussions.microsoft.com> wrote i...fill list box with file names
how to fill list box with file names sorted by date - newest first.
On Tue, 20 Apr 2010 09:04:46 -0700 (PDT), mcnews <email@example.com>
>how to fill list box with file names sorted by date - newest first.
Look at the VBA help for the Dir() function, and use a loop to fill a Table.
Use that table (or a query based on the table) as the Row Source for the
John W. Vinson [MVP]
1. What is the make of the table holding these names?
2. What is the name of the date field?
3. What is the name of ...What VBA function returns cell 2 from which cell 1 gets its value?
This is a simple and possibly embarrassing question.
In Excel 2002 SP3, cell 1 is on ProfitSheet and depends on cell 2, on
a different sheet.
Cell 1 has a UDF, so it looks like:
Given that I know cell 1 at runtime, what VBA function returns cell 2?
I have tried:
Set Cel2 = Cel1.Precedents(1)
Set Cel2 = Cel1.Precedents.Cells(1, 1)
but that only seems to return Cel1 itself (at least that's what the
Debug Window shows).
1) Is my syntax wrong?
2) Is Excel's lack of external dependent/precedent functionality in my...Counting Records after filtering by feilds
I am working on a project that will create a report to count the
number of open work orders (records that have their opendate field
with in a date range given), close work orders (records that have
their closeddate field with in the same date range given earlier) and
pending work orders (records that do not have an entry in the closed
date field). I don't know how to (a) prompt the user for a given date
range, im thinking of maybe using a form interface? Is it possible
for me to have a calander button to click to select a date from there?
and (b) To count the what was previously describe...protect cells #6
is it possible, if so how, to protect certain cells within a worksheet
By default all cells in excel are protected or locked, select the cells you
want to unlock and go to format, cells, protection and uncheck locked, the
go to tools, protection, and protect sheet, enter a password if you want,
now only the cells that you unlocked can be edited. Be aware that this
protection is very easy to break, the code to do so can be found very easy,
but it will work for most people .
If you only need a few locked I would select them all first, Ctrl A, then
go...Counting rows, then counting values.
I have a lot of spreadsheets with many rows and columns of numbers. I need
a way to find the highest value in each row, (mark it some how, bold the
number maybe), then count the number of "highest" values are in each
Is there an easy way to do this?
You can use this to get the value
and this to count
Regards Ron de Bruin
"Michael via OfficeKB.com" <forum@nospam.OfficeKB.com> wrote in message news:152907df2ac24b6ab6ee8d51d46958d9@OfficeKB.com...
>...Can server 2003 forward a specific port to another machine?
I have to machine, 192.168.1.1,192.168.1.2. I want all the access to
192.168.1.1:8000 being forwarded to 192.168.1.2:7000.
Can server 2003 accomplish this or I should install some other software?
> I have to machine, 192.168.1.1,192.168.1.2. I want all the access to
> 192.168.1.1:8000 being forwarded to 192.168.1.2:7000.
So both the front end target (192.168.1.1) and the back end target
(192.1168.1.2) are in the same IP subnet. Correct?
Presuming that the above statement is correct (for the sake of
discussion), you have to worry about reply ...how can I split one cell's data into two cells?
I am using Excel as a database, and have a column with cells with a person's
name (text) in it...e.g., John Smith. NOW, after the fact, I wish to have a
column with just John in it, and another column with just Smith...but I have
NO idea how to do this! Help would be appreciated. Thank you.
Please refrain from multiposting, you have answers in one of the 4!
newsgroups you posted the same question in. Multiposting is frowned upon and
even the MS web interface states so
Excel 95 - Excel 2007
Northwest Excel Solutions
"It ...Linking to a data range containing blank cells
I'm using MS Excel 2003.
I've got a chart from range of data gives the calculates accumulated total
from another table and would like the option to have the calculation result
in a blank cell that is not plotted as a zero.
I tried the following IF statement: =IF(Q6="","",Q6+P12) but "" is still
treated as a zero.
"James from Sage" <JamesfromSage@discussions.microsoft.com> wrote in message
I thought Excel used to display the number of pages with data on the bottom
status bar. I that option gone or is it only in Word?
Excel has never, in my memory, given that information on the status bar.
Perhaps you once had some code that gave you a page count.
Gord Dibben MS Excel MVP
On Wed, 11 Jan 2006 15:01:02 -0800, "pkferret"
>I thought Excel used to display the number of pages with data on the bottom
>status bar. I that option gone or is it only in Word?