Hiding blank rows
I have a spreadsheet with columns, first name, last name, home phone,
business phone and cell phone. When I don't have phone information in any of
the three columns I would like that column to be hidden or deleted. Can I do
this in some automated fashion? Thanks. Michael
try adding a column
in that column put something like
and use autofilter to hide the 1s
> I have a spreadsheet with columns, first name, last name, home phone,
> business phone and cell phone. When I don't have phone information in any of ...Subform link problem
I have a main form which display the details of an invoice from a query:
WHERE InvoiceNumber = [InvoiceNum]
I have a button to execute this query and pass the parameter, eg. 100 as
InvoiceNum parameter value.
I also have a subform in datasheet format, displays the invoice items and
also allows the user to enter the invoice items.
The link has been setup
Source Object: frmInvoiceItems
Link Child Fields: InvoiceNumber
Link Master Fields: InvoiceNumber
This subform link to the record source of a query:
ItemN...How do I set up column charts' x values in 'm' ie. 300m, 600m etc
I am learning charting in Excel. How do I display x values axis as 300m,
600m, 900m while the values displayed inside the columns are 256.4, 488.7 and
You make an XY chart not a Line chart. Line charts are used when the
x-values are categories (text or numbers that are not used numerically like
Bernard V Liengme
remove caps from email
"t4gbg" <email@example.com> wrote in message
>I am learning charting in Excel. How do I display x ...Can you lock a drop-down cell so that it sorts with the row?
I have created drop-down cells (type of business) for my client contact list
but when I sort that list alphabetically or by last contacted date the
drop-down cells don't sort with it. How can I solve that issue?
Thanks for the responses!
Are these Data Validation dropdown lists?
Where is the list range located?
If inside your sort area the lists should change to whatever is in A2:A20
after the sort.
Tested in 2003 and 2007
Gord Dibben MS Excel MVP
On Tue, 26 Jan 2010 13:19:01 -0800, Lisa in Victoria <Lisa in
Victoria@discussions.micros...Creating a Formula to Format Column automatically? #5
They're just name values, pulled from a database. What was strange i
that some of the formats tok correctly, others applied another condito
(i.e. Sally Smith was supposed to be green but came out red)P
bludovico's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1480
View this thread: http://www.excelforum.com/showthread.php?threadid=26434
...Macro for merging rows
I have a fairly large spreadsheet that are sorted based on a file # (ie:
E0800100, E0800101). The spreadsheet is setup to where each entry is on an
individual row as seen below:
A B C
E0800100 Review.... 1.0 (hr)
E0800100 Review.... 2.0
E0800101 Review.... 1.5
E0800102 Review.... .5
I am trying to organize the spreadsheet so that there is only one row per
file number and the Descriptions (B) and Time (C) extend along the columns of
A. B....Determine a result of one column based on conditions in two column
Col A Col B Count the number of a's in Col B only when an x is in
x a Result should be 2
z p I can't figure it out
x m Thanks
Hope this help
Please click the Yes button below if this post have helped answer your needs
> Col A Col B Count the number of a...To find the Last row
The following is the pattern of the data I have.
I need to know which is the last row which has the data
A1 - AAA
A3 - <blank>
A4 - CCC
A5 - DDD
A6 - <Blank>
A7 - <Blank>
A8 - <Blank>
A9 - EEE.
In the above example I want to get the row number as 9 as the last row
which has the data.
Can this be acheived by some formula. I tried COUNTA, but it does not
serve my purpose.
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.Excel...Dynamics GP with remote SQL Server across 2MB direct link
I have a client who is wishing to use Dynamics GP with the SQL server hosted
remotely. He has a 2MB/sec Direct Internet Link in both directions which he
claims is very robust and works well for their .NET developers.
He is looking for us to load the GP database on his server and load the
client software on his workstations.
Does anyone have experience of working using this method which is
effectively a 2MB LAN.
We had a client want to do this with bonded T1's and 3Mbps pipe to their
servers at a data center and the performance over it was like swimming in
puddin...Link Command Button
I have 27 Excel workbooks, each of which has a command
button to run a program written in Cplus. I presently
have this button on each workbook. If the program is
changed I would have to go into 27 workbooks to update the
program. Is there a way to "LINK" a command button to all
27 workbooks. My first thought is to open a new workbook
with just the macro code and assign the command button for
all 27 to this workbook. Will this work. Do you see any
problems doing it this way or have any suggestions.
Does Cplus mean that it's an executable (*.exe)?
Does ...add high/low/close lines to a stacked column chart
I need to add high/low/close lines to a stacked column chart. The
high/low/close values are unique, and each one corresponds with one of
the stacked bars. they should be on the same y-axis
Or I can add points next to each column of my stacked column chart and
then add custom error bars to those points. Either way will work, but
I can't figure out how to do either of these options.
Do you have a bunch of stacked bars, and you're showing the HLC for each?
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http:/...Why NOT to link using pragma comment (lib, libname)
I posted a while ago about static linking, and it generated a big discussion
of the merits or demerits of using #pragma comment (lib, "libname") in the
source code to specify the names of the static libraries to link against.
I thought I'd share something that I discovered later.
My app links against a single static lib that I created. I used the pragma
method to specify the library. What I found, though, was that the static
library was not part of the build dependency analysis. If I changed the
static library source and rebuilt the library, then rebuilt my app without
ma...looking for empty row to paste a range of copied cells
Hi - I am trying to make a code that will copy a range ("A27:L27") on sheet 1
and then look for the next empty row on sheet 2 and paste it in range
("A27:L27") . I would also like the macro to insert a new blank row (or
insert the copied row) for the purpose of shifting existing SUM functions on
sheet 2 down. I would like those functions to be right below the
copied/pasted cells every time the macro is executed.
Thanks for any help - Jim A
You don't Mention What column you want to sum
This code will copy and paste to the fist row and then sum column D
Sub Cop...Row Limit in Excel
I work a lot with excel and I know that the row limit is 65,536, but I need
more than that. Is there any way to make the number of rows infinite or at
least to give me a certain amount of more rows. If there is please let me
know. Thank you.
Molly, that is all there is, per sheet.
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
"Molly" <firstname.lastname@example.org> wrote in message
news:FD2C7921-E249-4...linking data with other workbooks
I have a spreadsheet (FrontEnd.xls) whos cell gets data from another
spreadsheet using (='C:\junk\[BackEnd.xls]Sheet1'!C3). I then close both
spreadsheets and open BackEnd.xls and insert a column causing the BackEnd
data to now be C4. Now when I open the FrontEnd.xls I am asked if I want to
update the links. After choosing "Yes", the cell is still pointing to
"'C:\junk\[BackEnd.xls]Sheet1'!C3" rather then to C4. Is there a way to have
the FrontEnd.xls spreadsheet update cell references that have shifted because
of insertions or deletion of...Can I control linked cell property value in a copied ActiveX contr
I need to copy an ActiveX control approximately 200 times in 1 worksheet (not
form). The LinkedCell property value does not change when I copy and paste -
so I end up with 200 controls pointing to the same cell. This is a pain to
The control is invoked with the following syntax
=EMBED("Forms.ScrollBar.1",""). It "smells like I should be able to expose
that cell address so a copy might work but I can't find any reference to the
EMBED on line or in 6 books. Any help or redirection would be much
...Row colouring that accommodates filtering?
What can be used instead of "=MOD(ROW(),2)=1" that accommodates filtering,
The above gives clumps of identically-coloured rows, dependent on the
particular filter criteria used.
Thank you! :oD
As long as there are no empty cells within the filtered list:
Assume A1:B1 is the header row.
A2:B10 is the data
Select the range A2:B10
Formula Is: =MOD(SUBTOTAL(3,$A1:$A$2),2)=0
"StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message
> What can be used instead of "=MOD(ROW...reporting tools linking with excel
Supposing i want a report to be generated from excel....something like using
crystal reports.... is it feasible to link a reporting tool with excel....
I hope i am clear.
...fix cell contents before deleting column from which formula's are based
I want to e-mail a spreadsheet to someone showing only the
calculated results and deleting the column from which the
formula's are based. I have done this (fixing the cell
contents) before but can't remember how and am obviously
not wording my query correctly for the help function.
Copy, paste special, values? then delete the column?
>I want to e-mail a spreadsheet to someone showing only
>calculated results and deleting the column from which the
>formula's are based. I have done this (fixing the cell
>cont...Insert row with formatting from row below instead of row above?
My default Excel template has the top row formatted as a header, with
bold text and a dark bottom border to distinguish it from the rows
So if I insert a row at row 2, the new row is formatted like the
header. Then I have to select the new row and remove the bold font,
remove the dark border, select the header row and reapply the bottom
Is there a way I can change the insert default so that it takes it's
formatting from the row below instead of the row above. Or better
yet, so that it has no formatting at all?
AFAIK you can't change this behaviour
Fra...calculating row by row
I would like to have Excel total the sum of a row 5 of randomly generated
numbers in a 6th column, one column at a time.
I have no problem generating the random numbers and totalling them but I
would like to generate the first number and place it in the total column then
when I initiate it generate the second number, add this to the first and
place it in the total and so on to the 5th column. Is this possible?
Unless I'm missing something here, this formula should do it for you. This
is for row 2 on the sheet, and would go into cell F2
That formula will &q...2 axis column chart
Anybody who knows how to make a column chart with 2 different valu
MartinNT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1565
View this thread: http://www.excelforum.com/showthread.php?threadid=27190
Make the chart with two data series
Right click on one of the data series in the chart; select Format Data
Series, open the Axis tab; click Secondary axis
It needs to data series. It you have only one then you need to reproduce the
I am currently working with Visual Studio 2008 and asp.net 3.0 I have
the following question I hope that someone can help me with:
1) I have a GridView with rows that I read from a database.
2) The user can select a row from the grid, which fires the event:
protected void MyGrid_SelectedIndexChanged(object sender,
<update text field on form>
3) I have the <update text field on form> portion of the routine
working, but need to call the <cal...Sorting Rows by Color
Excel 2003: Is there a way to sort rows by color? Put all
the blue rows together, all the green together, etc?
There is but you need to work it.
Take a look at
(remove nothere from the email address if mailing direct)
"SharonJo" <email@example.com> wrote in message
> Excel 2003: Is there a way to sort rows by color? Put all
> the blue rows together, all the green together, etc?
...adding every third column
i need a formaula that adds every third column i.e. formula in cell A1 adds
D1,G1 ect ect can some one help with this?
See your other post
Microsoft Excel MVP
"gma" <firstname.lastname@example.org> wrote in message
>i need a formaula that adds every third column i.e. formula in cell A1 adds
> D1,G1 ect ect can some one help with this?