A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Search Forums
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Showing results 1 to 25 of 100
Search took 1.43 seconds.
Search: Posts made by: Gary''s Student
Forum: General Discussion June 5th, 2010, 11:39 AM Posted to microsoft.public.excel.misc
Replies: 1
Views: 657
Posted By Gary''s Student
Free Ebay consignment spreadsheet?

Many are available. See:

http://www.brothersoft.com/ebay-consignment-spreadsheet-download-46836.html


--
Gary''s Student - gsnu201004


"Steve" wrote:
Forum: General Discussion June 4th, 2010, 12:09 AM Posted to microsoft.public.excel.misc
Replies: 2
Views: 329
Posted By Gary''s Student
Convert to hrs:min

Select the cells you want to convert and run this little macro:

Sub marine()
Dim hrs As Integer, mins As Integer
For Each r In Selection
v = r.Value
hrs = Left(v, 2)
mins = Right(v, 2)
...
Forum: General Discussion June 3rd, 2010, 11:55 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 483
Posted By Gary''s Student
How can I update macro to delete zero amount columns?

Hi Nora:

Try this modification:

Sub RemoveColumns()
Dim nLastColumn As Long
Set r = ActiveSheet.UsedRange
nLastColumn = r.Columns.Count + r.Column - 1
For i = nLastColumn To 1 Step -1
i1 =...
Forum: Worksheet Functions June 3rd, 2010, 11:22 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 8
Views: 609
Posted By Gary''s Student
Concatenation help

You are welcome. Thanks for the feedback.
--
Gary''s Student - gsnu201003


"Nadine" wrote:

THANK YOU!!!!! I knew you'd come through for me.

"Gary''s Student" wrote:
Forum: Worksheet Functions June 3rd, 2010, 11:04 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 8
Views: 609
Posted By Gary''s Student
Concatenation help

A tiny trick:

=RIGHT(LEFT(A1,5)&RIGHT(A1,10),12)&A2&TEXT(A3,"00")
--
Gary''s Student - gsnu201003


"Nadine" wrote:

I have 3 cells I need to merge into one. One of the cells is a number
Forum: Worksheet Functions June 3rd, 2010, 10:58 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 1
Views: 446
Posted By Gary''s Student
Zip Code Last 4 Digit as Zero

With a five digit ZIP code in A1, in another cell:

=A1 & "-0000"

--
Gary''s Student - gsnu201003


"cheppy" wrote:
Forum: Worksheet Functions June 3rd, 2010, 10:57 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 2
Views: 434
Posted By Gary''s Student
Zeros displaying in cell when using = sign

Since you are seeing zero rather than an error, it is likely that the
destination links got scrambled. Verify the addresses.
--
Gary''s Student - gsnu201003


"watermt" wrote:

I was moving...
Forum: Worksheet Functions June 3rd, 2010, 10:52 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 4
Views: 383
Posted By Gary''s Student
Racking my brain on sumif

Consider using a Pivot Table instead:

http://peltiertech.com/Excel/Pivots/pivotstart.htm
--
Gary''s Student - gsnu201003


"Victor R." wrote:

I have a report download that roughly looks like...
Forum: Worksheet Functions June 3rd, 2010, 07:10 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 3
Views: 412
Posted By Gary''s Student
Average If

=AVERAGE(IF(A:A75,B:B))
This is an array formula that must be entered with:
CNTRL-SHFT-ENTER
rather than just the ENTER key.
--
Gary''s Student - gsnu201003


"carl" wrote:
Forum: General Discussion June 3rd, 2010, 11:03 AM Posted to microsoft.public.excel.misc
Replies: 3
Views: 497
Posted By Gary''s Student
how do i write on the background of a spreadsheet

Insert Picture WordArt
--
Gary''s Student - gsnu201003
Forum: Worksheet Functions June 2nd, 2010, 05:12 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 3
Views: 394
Posted By Gary''s Student
Formula Help

=IF(ISERROR(FIND("bail",G2)),"",B2 & "," & G2 & "," & H2)
--
Gary''s Student - gsnu201003
Forum: General Discussion June 2nd, 2010, 03:40 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 402
Posted By Gary''s Student
Change the values of cells by a fixed factor i.e. 10/100= 0.10

Say we want to reduce the value in cells by 1%
So 100 would become 99

In an unused cell, enter .99
Copy this cell
Paste/Special/Multiply onto the cells you want to reduce.
--
Gary''s Student -...
Forum: General Discussion June 2nd, 2010, 12:45 AM Posted to microsoft.public.excel.misc
Replies: 11
Views: 663
Posted By Gary''s Student
Remove columns with all zeros

Try this:

Sub RemoveColumns()
Dim nLastColumn As Long
Set r = ActiveSheet.UsedRange
nLastColumn = r.Columns.Count + r.Column - 1
For i = nLastColumn To 1 Step -1
If...
Forum: General Discussion June 1st, 2010, 10:53 PM Posted to microsoft.public.excel.misc
Replies: 3
Views: 288
Posted By Gary''s Student
how can we make one constant number to be act as variable

Perhaps somethin like:
=VLOOKUP(A$12,A$35:J$40,ROWS(1:$1)+3,TRUE)
--
Gary''s Student - gsnu201003


"Vimlesh" wrote:

I mean to say if we have this formula in a cell:
=vlookup(A12,A35:j40,4,true)
Forum: New Users June 1st, 2010, 01:13 PM Posted to microsoft.public.excel.newusers
Replies: 1
Views: 2,015
Posted By Gary''s Student
text column - via macro

You can do this without macros, but try this:

Sub SplitUm()
Dim r1 As Range, r2 As Range
Set r1 = Intersect(ActiveSheet.UsedRange, Range("A:A"))
cutt = " - "
For Each r2 In r1
v = r2.Value
...
Forum: General Discussion June 1st, 2010, 12:18 PM Posted to microsoft.public.excel.misc
Replies: 3
Views: 388
Posted By Gary''s Student
Deleting invisible range names - how?

This little macro loops over your defined names. If it finds #REF in
RefersTo, the name is deleted:

Sub dural()
Dim s1 As String, s2 As String, s3 As String
Dim s4 As String
s3 = "#REF"
For Each n...
Forum: General Discussion May 31st, 2010, 09:14 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 278
Posted By Gary''s Student
I want to add or subtract a range of cells

With A1 thru B9 containing:

1 d
2 d
3 d
4 d
5 r
6 r
7 r
8 d
Forum: Worksheet Functions May 31st, 2010, 03:46 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 6
Views: 460
Posted By Gary''s Student
Lose the 0 in the Total cell

=IF(COUNTIF(B5:B22,"s")+COUNTIF(H5:H21,"s")=0,"",COUNTIF(B5:B22,"s")+COUNTIF(H5:H21,"s"))

--
Gary''s Student - gsnu201003
Forum: General Discussion May 31st, 2010, 02:42 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 232
Posted By Gary''s Student
Date Format Problem

In another cell enter:

=LEFT(A1,2) & "/" & RIGHT(A1,4)

or

=DATE(RIGHT(A1,4),LEFT(A1,2),1) and format as mm/yyyy
--
Gary''s Student - gsnu201003
Forum: Worksheet Functions May 31st, 2010, 02:26 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 2
Views: 620
Posted By Gary''s Student
View Message when Mouse Hovers over Cell. (Not the Comments Feild)

You should still use Comments.

You can write an Event macro that runs everytime the worksheet containing
the names is activated.

The macro would erase all the comments in row #2 and replace them...
Forum: General Discussion May 27th, 2010, 11:04 PM Posted to microsoft.public.excel.misc
Replies: 4
Views: 249
Posted By Gary''s Student
Macro from cell specific to whole column

Don't both with the rewrite.

Once you deposited a good formula in a single cell, use copy/paste to fill
the rest of the column.

After all, if the approach is good enough for a human, it should be...
Forum: General Discussion May 27th, 2010, 09:21 PM Posted to microsoft.public.excel.misc
Replies: 3
Views: 272
Posted By Gary''s Student
Two values not adding correctly

This is a common problem called rounding error. It can be avoided as follows:

=ROUND(664199.05-582911.61,2)

--
Gary''s Student - gsnu201003


"chitown29" wrote:
Forum: Worksheet Functions May 27th, 2010, 09:20 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 2
Views: 147
Posted By Gary''s Student
Is there a way to "glue" a matrix rectangle of cells together?

Select or hi-light the matrix. You then move the cursor to the very edge of
the hi-lighted area. The fat plus will become a thin plus with arrow heads.

You can then click drag and drop the matrix...
Forum: General Discussion May 27th, 2010, 08:53 PM Posted to microsoft.public.excel.misc
Replies: 1
Views: 178
Posted By Gary''s Student
DEFAULT ITERATION - HELP NEEDED

I would include a macro that runs automatically when your workbook is opened.
The macro would save the state of iteration and then setup iteration
according to your model's needs.

Another macro...
Forum: General Discussion May 25th, 2010, 08:31 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 177
Posted By Gary''s Student
Macro to Cut and paste Values

Try this small macro:

Sub noformulas()
Dim i As Long, n As Long
n = Cells(Rows.Count, "P").End(xlUp).Row
For i = 1 To n
If Cells(i, "P").Value = "Y" Then
Set r = Range("A" & i & ":U" &...
Showing results 1 to 25 of 100

 
Forum Jump

All times are GMT +1. The time now is 03:56 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 OfficeFrustration.
The comments are property of their posters.