*Hi Experts,
Its not working Please do in the sheet please, so that i will clarified.
thanks
*
Thanks & Regards,*
*
*Deba Ranjan P***
On Mon, Apr 16, 2012 at 12:20 PM, anil panchal wrote:
> TEXT(B5+C5,"[h]:mm:ss")
>
>
> On Mon, Apr 16, 2012 at 12:00 PM, Deba Ranjan wrote:
>
>> *Hi Experts
TEXT(B5+C5,"[h]:mm:ss")
On Mon, Apr 16, 2012 at 12:00 PM, Deba Ranjan wrote:
> *Hi Experts,
>
> Please see the attached File. There i am getting error in time
> calculation. please do the needful. Thanks in advance.
> *
>
>
> Thanks & Regards,*
> *
> *Deba
>
> *
>
> --
> FORUM RULES (986+ member
*Hi Experts,
Please see the attached File. There i am getting error in time calculation.
please do the needful. Thanks in advance.
*
Thanks & Regards,*
*
*Deba
*
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Hi..Experts..
i m facing a problem to convert a number in text, so kindly help me on
this problem
--
P Before printing, think about the environment.
Devendra sahay
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles
Nikhil, One way:
Assume date is in A2. So
In B2 for the Curr Quarter.
=TEXT(LOOKUP(MONTH(A2),{1,4,7,10})*29,"")
Then in C2
=TEXT((1&B2)+99,"")
Copy across to E2.
See the attached.
___
HTH, Haseeb
nikhil wrote:
>
> Now answer is right ,
>
> i.e. Date 01/05/2010 ( DD/MM/
Difference items need one more conditions
Mothilal
On Sun, Apr 15, 2012 at 4:19 PM, Rajan_Verma wrote:
> ** **
>
> Stock = Opening Stock + Purchase – Sales , and there is no opening stock.
> So P-S
>
> =SUMIF(A2:D7,"P",C2:C7)-SUMIF(A2:D7,"S",C2:C7)
>
> ** **
>
> *From:* excel-macros@goo
Hi,
To get the percentage, *Copy paste* or *Drag Down* the formula from *
Sheet1-D4*.
On Mon, Apr 16, 2012 at 1:10 AM, Sara Lee wrote:
> Hi
>
> So do i have to copy and paste this formula?
>
>
> On Sun, Apr 15, 2012 at 4:32 PM, Maries wrote:
>
>> Hi,
>>
>> In that case, Dynamic range is goo
Yes my file is protected and I want to keep it protected, because though
file is created by me, users are different and any change in data/ formula
will differ its result.
Please guide.
On Sun, Apr 15, 2012 at 4:24 PM, Rajan_Verma wrote:
> Is your worksheet protected?
>
> ** **
>
> Rajan.**
Hi Hasseb,
Now answer is right ,
i.e. Date 01/05/2010 ( DD/MM/ ) Format ,
Currently it's showing Curr.Qtr = April ; Next Qtr = July ..it is ok
After July , Now I want Next Qtr = October ; Next Qtr = January
Nikhil
On Sun, Apr 15, 2012 at 11:07 PM, Haseeb A wrote:
> Nikhil,
>
> I got an
Ah, all in one step. Yes thanks.
On Sun, Apr 15, 2012 at 7:54 PM, Asa Rossoff wrote:
> Does it help to say that:
>> With Sheets(RawData)
>> Set tmprange = .Range(.Cells(2, BarOpen),
> .Cells(.UsedRange.Rows.Count, EContango))
>> End With
>> DataArray = tmprange.Value2
>
> is equivalent to:
>
Does it help to say that:
> With Sheets(RawData)
> Set tmprange = .Range(.Cells(2, BarOpen),
.Cells(.UsedRange.Rows.Count, EContango))
> End With
> DataArray = tmprange.Value2
is equivalent to:
With Sheets(RawData)
DataArray = .Range(.Cells(2, BarOpen), .Cells(.UsedRange.Rows.Count,
EC
yeah all the rest was there. Still confused though. He supposedly
does without SET what I cannot do unless I use SET.
On Sun, Apr 15, 2012 at 7:25 PM, Asa Rossoff wrote:
> Your code is fine except it doesn't do what the comment says it does :) --
> unless there is following code to finish the j
Your code is fine except it doesn't do what the comment says it does :) --
unless there is following code to finish the job.
To copy to an array,
1. Declare a Variant variable to hold the array
2. Assign the Value2 or Value property of a range to the Variant (Don't use
Set when you do so)
Sir, any help ?
=Mangal Ho
On 4/10/12, Somnath Khadilkar wrote:
> Sir,
> A while ago, you had sent me a file, [ which I am enclosing ] to
> convert the DATA file into MANY SHEETS depeding upon filter condition,
> That is V useful, but I would like to generate NewFIles [ instead of
> New Shee
Happy Birthday Mr. Don,
May God Bless you with Long Life & Peaceful Future
Somnath Khadilkar
On 4/14/12, Ayush Jain wrote:
> Dear members,
>
> Don Guillett has been selected as 'Most Helpful Member' for the month of
> March'12
> He has been helping forum members from long time consistently an
On Saturday, April 14, 2012, Ayush Jain wrote:
> Dear members,
>
> Don Guillett has been selected as 'Most Helpful Member' for the month of
March'12
> He has been helping forum members from long time consistently and we are
proud to have him in the forum.
>
> He is business degree holder from Univ
hi group,
I had a routine question regarding the comparision of worksheets.I had
searched the internet regarding the differences between two worksheets
which have similiar columns & headings.I want to find out the
changes/differences between the two worksheets.
In depth, I had a master workshee
Hi
So do i have to copy and paste this formula?
On Sun, Apr 15, 2012 at 4:32 PM, Maries wrote:
> Hi,
>
> In that case, Dynamic range is good solution.
>
> I have used Name Range "rng" refers
> as*=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
> *
> Regards,
>
> MARIES.
> *
Hi,
In that case, Dynamic range is good solution.
I have used Name Range "rng" refers
as*=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
*
Regards,
MARIES.*
*
On Mon, Apr 16, 2012 at 12:14 AM, Sara Lee wrote:
> thanks it is good but one change required--- when i add more rec
Hence my confusion. Here is the code I am currently using. Sounds
like you are saying the SET should
cause it to fail.
But it only works if I use the SET. Otherwise it will not compile.
' First let's copy everything we need into an array for efficiency
With Sheets(RawData)
Set tmprang
thanks it is good but one change required--- when i add more records into
my base sheet , and then refresh the pivot, that column % does not get
updated automatically;; also when i delete records, that column shows up
0 even though there are no records to left
On Sun, Apr 15, 2012 at 3:41 PM, Ma
> Well on the read side we definitely match except he makes the point
> of saying he is not using SET.
> Why is that? What is the difference?
I assume he's contrasting with the "Use a range object" routine where
DataRange was a Range object, and thus needed Set for it's assignment. In
the "Use a
Hi,
Try this formula,
=B4/OFFSET($A$3,COUNTA(A:A)-1,2)
Regards,
MARIES.
On Sun, Apr 15, 2012 at 10:20 PM, Sara Lee wrote:
> i have created a pivot table in excel in sheet 4 out of sheet 1 data. Now
> i have added a calculation column - right most column of the pivot...
>
> now when i delete
Perfect timing!
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of dguillett1
Sent: Sunday, April 15, 2012 6:07 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Most helpful Member - March 12- Don Guillett
Thanks to all who have respond
Thanks but all handled and the solution was posted.
On Sun, Apr 15, 2012 at 7:36 AM, dguillett1 wrote:
> I haven't paid much attention to this so..
> If you are still having a problem with this you might consider using the
> FIND method to get the last value you want
> Send your file direct to m
i have created a pivot table in excel in sheet 4 out of sheet 1 data. Now i
have added a calculation column - right most column of the pivot...
now when i delete the row of the data in sheet 1 say mumbai row, and then
refresh the pivot... then the last column gets messed up showing div/0
error
is
Nikhil,
I got answer July instead of April. There were two formulas in my last
reply. First one for Current Quarter, 2nd one for Next Quarter.
See the attached.
___
HTH, Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thr
If your request is for a reply, I did reply.
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: Abdulgani Shaikh
Sent: Saturday, April 14, 2012 5:18 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ If date in One then another cell to be unlock
Pl.re
Pl.reply
On Sat, Apr 14, 2012 at 2:02 PM, Abdulgani Shaikh wrote:
> I have pasted this in Excel, but it is not working, pl.find attached file,
> where i am wrong ?
> Regards
>
>
> On Sat, Apr 14, 2012 at 12:38 PM, Rajan_Verma wrote:
>
>> Hi,
>>
>> You can try this :
>>
>> ** **
>>
>> Priv
Hi sam,
super Idea
Nikhil Shah
On Sun, Apr 15, 2012 at 7:01 PM, Sam Mathai Chacko wrote:
> A shorter one
>
> =CHOOSE(CEILING(MONTH(A1)/3,1),"April","July","October","January")
>
> Regards,
> Sam
>
>
> On Sat, Apr 14, 2012 at 6:19 PM, Nikhil Shah wrote:
>
>> Hi Maries,
>>
>> Thanks for solving m
I haven't paid much attention to this so..
If you are still having a problem with this you might consider using the
FIND method to get the last value you want
Send your file direct to me if desired with a complete explanation.
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmai
UN protect your worksheet manually or by code. TESTED fine on file presented.
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: Abdulgani Shaikh
Sent: Sunday, April 15, 2012 4:43 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Fwd: If data in One t
A shorter one
=CHOOSE(CEILING(MONTH(A1)/3,1),"April","July","October","January")
Regards,
Sam
On Sat, Apr 14, 2012 at 6:19 PM, Nikhil Shah wrote:
> Hi Maries,
>
> Thanks for solving my problem.
>
> Nikhil
>
> On Sat, Apr 14, 2012 at 6:15 PM, dguillett1 wrote:
>
>> You need to provide a file
Hurray Don. 76 ?wow
Congrats and a very Happy birthday.
Sent from my BlackBerry® smartphone from Airtel Ghana
-Original Message-
From: "Mohammed Muneer"
Sender: excel-macros@googlegroups.com
Date: Sun, 15 Apr 2012 16:13:27
To:
Reply-To: excel-macros@googlegroups.com
Subject: RE: $
Happy Birthday Mr. Don,
May God Bless you with Long Life & Happy Future. At the same time enrich
your knowledge with more talents.
Regards,
Muneer,
CC...
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like
Thanks to all who have responded or will.
Someone must have know that it is my 76 th birthday today.
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: Ayush Jain
Sent: Saturday, April 14, 2012 3:36 AM
To: excel-macros
Subject: $$Excel-Macros$$ Most helpful Member -
Hi Haseeb,
Sorry , answer is wrong.
i.e. Dt.01/05/2010 ( DD/MM/ ) ,then Next Quarter should be July , here
it's display april..
Nikhil
On Sun, Apr 15, 2012 at 2:57 PM, Haseeb A wrote:
> Hello Nikhil,
>
> This will give the current quarter.
>
> =TEXT(LOOKUP(MONTH(A1),{1,4,7,10})*29,"mmm
Well on the read side we definitely match except he makes the point of
saying he is not using SET.
Why is that? What is the difference?
It does indicate that if performance was any issue I should create an
output array and fill it then copy to
the Results sheet range but seeing as the program tak
Is your worksheet protected?
Rajan.
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Abdulgani Shaikh
Sent: Apr/Sun/2012 03:14
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Fwd: If data in One then another cell to be
unlock
My she
Stock = Opening Stock + Purchase - Sales , and there is no opening stock. So
P-S
=SUMIF(A2:D7,"P",C2:C7)-SUMIF(A2:D7,"S",C2:C7)
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of jmothilal
Sent: Apr/Sun/2012 02:31
To: excel-macros@googlegroups.com
Subjec
My sheet contains some tax formula and I want to keep these formula
protected, if inadvertently they deleted or corrected, it will not give
correct answer.
If I am protecting the worksheet, it gives error at
Range("A1:J58").SpecialCells(xlCellTypeConstants, 1).ClearContents
Please guide
Thanks a
Thank. I will definitely read tomorrow. I am using an temp array
copy of the raw data for reading values.
I decided not to use an array for the output as I did not expect much
gain. The program runs in seconds anyway
as I just got my first I believe successful run moments ago. Still
have to do
I have pasted it in worksheet code module, but it is not working, pl tell
me where I am wrong ? File is attached herewith.
Regards
On Sun, Apr 15, 2012 at 8:52 AM, Rajan_Verma wrote:
> You need to paste it in worksheet code module.
>
> ** **
>
> Rajan.
>
> ** **
>
> *From:* excel-macros
Just came across a simple example using an array to manipulate cell values,
then put only values that got changed back to the worksheet. It also has a
range/index equivalent sample, and a range/selection/offset example (this is
often the sort of thing first-time macro writers end up with after mod
Hello Nikhil,
This will give the current quarter.
=TEXT(LOOKUP(MONTH(A1),{1,4,7,10})*29,"")
This will give the Next Quarter
=TEXT(LOOKUP(MONTH(A1)+3,{1,4,7,10,13})*29,"")
___
HTH, Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thre
Never mind. Too late to be trying to debug code. Left out the actual
assignment FillResults = x about
which VBA is apparently quite picky.
On Sun, Apr 15, 2012 at 2:05 AM, tangledweb wrote:
> I assume this has to do with the assignment being to a variable that is
> passed into the function, bu
I assume this has to do with the assignment being to a variable that is
passed into the function, but if so is there something more
elegant than using a temporary copy for the maxrow value passed in or
making it global? The function needs to know the value
of maxrow on entry and needs to incremen
Thanks i am updating
Mothilal
On Sun, Apr 15, 2012 at 2:24 PM, Haseeb A wrote:
> You can get this with just one SUMPRODUCT,
>
> =SUMPRODUCT((B$2:B2=B2)*(A$2:A2={"P","S"}),C$2:C2*{1,-1})
>
> Which is on the file in the last reply.
>
>
> ___
> HTH, Haseeb
>
> --
> FORUM RULES (986+ members
You can get this with just one SUMPRODUCT,
=SUMPRODUCT((B$2:B2=B2)*(A$2:A2={"P","S"}),C$2:C2*{1,-1})
Which is on the file in the last reply.
___
HTH, Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Plea
Thanks it working . also i find this formula
=SUMPRODUCT(--(($A$2:A2="P")*($B$2:$B2=B2))*$C$2:C2)-SUMPRODUCT(--(($A$2:A2="S")*($B$2:$B2=B2))*$C$2:C2)
On Sun, Apr 15, 2012 at 2:16 PM, Haseeb A wrote:
> Hello Mothilal,
>
> See the attached. if you are on XL 2007 or later use SUMIFS
>
> _
Congratulation Don. Very happy to see your name on this thread.
Regard,
Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
Hello Mothilal,
See the attached. if you are on XL 2007 or later use SUMIFS
___
HTH, Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Ne
I want to find different items sums
PURCHASE / SALES ITEM QTY STOCK P LG DVD WRITER 4 4 P LG DVD WRITER 5 9
S LG DVD WRITER 1 8 P LG DVD WRITER 1 9 P LG DVD WRITER 10 19 S LG DVD
WRITER 4 15 p 512 MB DDR RAM 1 1 p 18.5 Monitor 1 1 p 500 GB Hard disk 1
1 S 512 MB DDR RAM 1 0 S 18.5 Mon
53 matches
Mail list logo