Hi Skanda,
Using worksheet funtions, it would just be:
=MID(B6,7,19) copied down.
Programatically it would include a For/Next loop, but the otherwise look very
similar:
Sub SevenToTwentyfive()
For i = 6 To 1000
Cells(i, 3) = Mid(Cells(i, 2), 7, 19)
Next i
End Sub
This
Hi Angelo,
The summing could be done with worksheet functions - probably SUMPRODUCT. But
the deleting of lines has to be done by macro, so it makes sense to do both by
macro. I can't look at it straight away, but if no one else comes up with
anything, I'll have a look later.
Regards - Dave.
with a workbook. Thank you!!!
Nadine
From: Dave Bonallack davebonall...@hotmail.com
To: excel-macros@googlegroups.com excel-macros@googlegroups.com
Sent: Sun, August 1, 2010 10:23:44 PM
Subject: RE: $$Excel-Macros$$
Hi Nadine,
Have a look at the attached.
It's a bit long-winded, but seems to work
Hi Ayush,
My pleasure.
Dave.
Date: Sat, 31 Jul 2010 01:59:16 -0700
Subject: $$Excel-Macros$$ Thank you Dave Bonallack !!
From: jainayus...@gmail.com
To: excel-macros@googlegroups.com
Dear Group,
In the recent group survey conducted, I asked one question that How
the top posters
Too kind Harmet.
Dave.
From: harmeet.hew...@gmail.com
Date: Tue, 3 Aug 2010 14:46:02 +0530
Subject: Re: $$Excel-Macros$$ Thank you Dave Bonallack !!
To: excel-macros@googlegroups.com
I Second that Ayush...Dave Rocks.Cheers
Warm Regards,
Harmeet Singh
IT Analyst
McKinsey
Hi Nadine,
I'm guessing a bit.
What I need is a total of the result of that
I assume this means the result of your SumProduct formula
plus the same if it = $R$2 and if it = $R$1
I assume that if the result of the SumProduct formula = R2 AND = R3, then you
want the result of the
Hi,
I never use Ctrl+Space. Didn't know about it. When the list of names appears,
just select the one you want by double-clicking on it
Regards - Dave.
Date: Tue, 3 Aug 2010 15:49:18 -0700
Subject: $$Excel-Macros$$ Ctrl + space bar
From: moor...@ldschurch.org
To:
-Macros$$ Re: URGENT: Sum of 2 Lines replaced by 1
To: excel-macros@googlegroups.com
Does someone else know how to do this please!
2010/8/3 Dave Bonallack davebonall...@hotmail.com
Hi Angelo,
The summing could be done with worksheet functions - probably SUMPRODUCT. But
the deleting of lines
: Nadine S n8dine4ma...@yahoo.com
To: excel-macros@googlegroups.com
Sent: Sat, August 7, 2010 8:24:16 AM
Subject: Re: $$Excel-Macros$$ Need max date
I'm getting the result #VALUE!. Could that be because what in column A in both
sheets is alpha numeric?
From: Dave Bonallack davebonall
: Dave Bonallack davebonall...@hotmail.com
To: excel-macros@googlegroups.com excel-macros@googlegroups.com
Sent: Sat, August 7, 2010 10:14:21 PM
Subject: RE: $$Excel-Macros$$ Need to remove Alt + Enter
Hi Nadine,
The Alt+Enter leaves a character which has an ascii value of 10. So there are a
couple
Hi,
If you have the Analysis Tool Pack loaded, use EDATE. It's very
straight-forward.
If your date is in A1, then in another cell: =EDATE(A1,50). Format using a Date
fromat.
If you don't have it loaded, it comes with XL. Tools menu, Add-ins, tick
Analysis Toolpack.
Regards - Dave.
Hi,
No. VLOOKUP only works left to right.
If you want to work right to left, you have to use Index / Match
Regards - Dave
Date: Wed, 11 Aug 2010 14:46:31 +0530
Subject: $$Excel-Macros$$ Can we find left Value through VLOOKUP formula..
From: noorain.ans...@gmail.com
To:
.
Could you help me to find one more task.
I need to list TL names under each Manager Name.
Please help me. I haev attached the revised Excel file for your work.
Thanks,
sundarvelan
On 8/8/10, Dave Bonallack davebonall...@hotmail.com wrote:
Hi Sundarvelan,
Have a look at the attached. I have
Test3
Thabks. Nadine
From: Dave Bonallack davebonall...@hotmail.com
To: excel-macros@googlegroups.com excel-macros@googlegroups.com
Sent: Mon, August 9, 2010 6:33:27 AM
Subject: RE: $$Excel-Macros$$ Need to remove Alt + Enter
You're welcome.
Dave.
Date: Mon, 9 Aug 2010 05:31:11 -0700
still need
the info to show as 3 lines/sentences in one cell. :(
Nadine
From: Dave Bonallack davebonall...@hotmail.com
To: excel-macros@googlegroups.com excel-macros@googlegroups.com
Sent: Fri, August 13, 2010 6:30:32 AM
Subject: RE: $$Excel-Macros$$ Need to remove Alt + Enter
Hi Nadine
Hi,
I hope the attached contains what you need.
Regards - Dave.
Date: Sat, 14 Aug 2010 10:23:42 +0530
Subject: $$Excel-Macros$$ Help Required
From: deepaktheind...@gmail.com
To: excel-macros@googlegroups.com
Hi All,
I need your help to make an automation of the attached worksheet. In
You're welcome.
Regards - Dave.
Date: Sat, 14 Aug 2010 14:17:47 +0530
Subject: Re: $$Excel-Macros$$ Help Required
From: deepaktheind...@gmail.com
To: excel-macros@googlegroups.com
Thanks Dave - it works...
On Sat, Aug 14, 2010 at 1:56 PM, Dave Bonallack davebonall...@hotmail.com
wrote
Hi Alex,
Have a look at the attached. I have assumed by the syntax of your original code
that you are useing XL2007, so I've used that too.
One sheet does what you want by macro, while the second sheet does it with
coditional formatting. The second sheet has the advantage of being active, in
Hi,
Have a look at the attached to se if it does what you need.
Regards - Dave.
Date: Tue, 17 Aug 2010 22:06:40 +0530
Subject: Re: $$Excel-Macros$$ Extract Only Mobile Numbers from the
strings--Urgent
From: venkat1@gmail.com
To: excel-macros@googlegroups.com
CC: bhavya...@gmail.com
Hi,
You can use COUNTIF, which will return 1 if the id has been used only once, or
greater than 1 if used multiple times.
Regards - Dave.
Date: Wed, 18 Aug 2010 15:54:49 -0700
From: n8dine4ma...@yahoo.com
Subject: $$Excel-Macros$$ Unique id to name
To: excel-macros@googlegroups.com
I
You're welcome
Regards - Dave.
Date: Wed, 18 Aug 2010 15:54:25 -0700
From: n8dine4ma...@yahoo.com
Subject: Re: $$Excel-Macros$$ Need to remove Alt + Enter
To: excel-macros@googlegroups.com
Dave,
This worked beautifully. Thanks so much!
From: Dave Bonallack davebonall
Hi Becky,
If XL has put NA in a cell, you must already have a formula there. You'll need
to send a sample workbook so we can see get the whole picture.
Regards - Dave.
Date: Wed, 18 Aug 2010 12:15:30 -0700
Subject: $$Excel-Macros$$ Need help with a formula
From: rebecca.math...@kraft.com
On Wed, Aug 18, 2010 at 10:17 AM, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi,
Have a look at the attached to se if it does what you need.
Regards - Dave.
Date: Tue, 17 Aug 2010 22:06:40 +0530
Subject: Re: $$Excel-Macros$$ Extract Only Mobile Numbers from the
strings--Urgent
To: excel-macros@googlegroups.com
On 8/19/10, None n8dine4ma...@yahoo.com wrote:
Dave,
Can I trouble you for an example? I'm not sure how the COUNTIF would work
here. Thanks.
From: Dave Bonallack davebonall...@hotmail.com
To: excel-macros@googlegroups.com excel-macros@googlegroups.com
Hi David,
Have a look at the attached. I hope it meets your needs.
As good as the macro recorder is, it always returns way too much code. Part of
learning VBA is learning how to trim down what we get from the macro recorder.
For example:
We can name a range without actually selecting
Hi Antony,
See if the attached contains what you need.
For the catagory column, I've used a nested IF formula. But you could also have
a separate table and use VLOOKUP.
Columns A B are formatted as Date, while Column C is formatted as Number.
You will have to fill-down the formulas in
problems..
Keep it up man!!
You are a pillar of our group...
Cheers!!!
Andy
On Sat, Aug 21, 2010 at 2:45 PM, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi Shariq
I've done this with worksheet functions, not VBA.
The original formula is in B3 on the Report sheet. It's then just copied down
,
Tony
On Aug 21, 11:45 am, Dave Bonallack davebonall...@hotmail.com wrote:
Hi Antony,
See if the attached contains what you need.
For the catagory column, I've used a nested IF formula. But you could also
have a separate table and use VLOOKUP.
Columns A B are formatted as Date
know your are very good in vba.
Thanks
Shariq
Sent on my BlackBerry® from Vodafone
From: Dave Bonallack davebonall...@hotmail.com
Sender: excel-macros@googlegroups.com
Date: Sun, 22 Aug 2010 15:10:50 +0800
To: excel-macros@googlegroups.comexcel-macros@googlegroups.com
ReplyTo: excel-macros
Hi,
If the question really is is it in sheet 1? then this is a job for SUMPRODUCT!
Please see the attached to see if it helps.
Regards - Dave.
Date: Mon, 23 Aug 2010 06:28:14 +0530
Subject: $$Excel-Macros$$ V look up more than one criteria
From: sba...@gmail.com
To:
Hi Neil,
Nice form!
Now, to make it work, each button needs some code.
I am assuming you have XL2003.
Follow these steps.
Open the VBA window.
Right-Click UserForm1 and select View Code.
You'll see a drop-down which currently has (General) in it. Click on that
and you'll see a list of
Hi,
This is a tricky problem, made trickier by the fact that in paragraph 1 you ask
for blank cell paths, while in paragraph 2 you ask for non blank cell paths.
But which ever you want, the problem has the same level of difficulty.
Supposing we want to find blank cell paths:
Basically you
criteria
From: sba...@gmail.com
To: excel-macros@googlegroups.com
thanks this was what i needed.
Taking it forward if it is in sheet 1 then can it pick up a value in anotther
column of the same row like how we use in V LOOK UP
instead of YES or NO
S Baloo
On 8/23/10, Dave Bonallack
Hi,
If your sample data is really representitive of ALL your data - ie, the numeric
portion is always at the end of the string - then the attached should do what
you need.
Regards - Dave.
Date: Mon, 23 Aug 2010 15:36:04 +0530
Subject: $$Excel-Macros$$ Extract only from number from a text
Hi,
Do you back-up regularly? If so, it may be in your back-up files. If not, then
it is lost lost. Sorry.
Regards - Dave.
Date: Fri, 27 Aug 2010 05:24:43 +0400
Subject: $$Excel-Macros$$ Need help deleted column in excell sheet
From: idhrees...@gmail.com
To: excel-macros@googlegroups.com
Hi,
First of all, you can't use Data Validation after the data is entered. You may
apply the validation, but it won't react to invalid data. It only responds to
data entered after the validation rule is in place. If you want to be alerted
to invalid data that is already present, you'd
Hi,
You have custom formatting in the both of your main data columns, which means
that only the numeric value is entered. (Select one of the cells and see what
appears in the formula bar). The units are done with the formatting. So you
only need a simple =A2 sort of formula in a cell with
$$ Help in Separate the Num Alpha in a cell into
another two cells
From: ankit.agrawal...@gmail.com
To: excel-macros@googlegroups.com
I did not get it what does it means? i m newly in excel, so plz decribe it
thanks regards
ankit
On Sun, Aug 29, 2010 at 10:14 AM, Dave Bonallack davebonall
Hi,
Thanks for opening up an area of XL that I'd never even heard about. I did some
research, and have found the following page to be most informative on the
subject of GET.CELL
http://www.mrexcel.com/forum/showthread.php?t=20611
Regards - Dave.
Date: Mon, 30 Aug 2010 11:52:47
Thanks!
Regards - Dave.
Date: Mon, 30 Aug 2010 11:52:47 +0700
Subject: Re: $$Excel-Macros$$ Help in Separate the Num Alpha in a cell into
another two cells
From: villager.g...@gmail.com
To: excel-macros@googlegroups.com
Without VBA, we can create a NAME of formula
(menu Insert Name
I like XL because it's like a logic puzzel, except that the puzzle is solved,
you actually end up with something useful.
Reards - Dave
Date: Mon, 30 Aug 2010 04:35:33 -0700
Subject: $$Excel-Macros$$ Re: I love excel because...
From: dertop...@web.de
To: excel-macros@googlegroups.com
Hi all,
When I create a custom command bar in XL2003, I can attach it to a workbook
using Tools, Customize, Toolbars Tab, Attach. I can also remove custom command
bars in the same way. However, when I turn on the macro recorder to learn the
VBA syntax for doing this, nothing is recorded.
I
Hi,
Please see the attached for a possible solution.
Regards - Dave.
Date: Sat, 4 Sep 2010 08:40:35 -0700
From: n8dine4ma...@yahoo.com
Subject: Re: $$Excel-Macros$$ Re: Ned Maxif
To: excel-macros@googlegroups.com
Thank you for the response. Unfortunately, the data can't be
Hi,
This seems to be a straight-forward job for SUMIF or SUMPRODUCT.
SUMIF is well explained in the help.
Let me know if you need more help, or if I have mis-interpreted your needs.
Regards - Dave.
Date: Mon, 6 Sep 2010 23:19:21 +0400
Subject: $$Excel-Macros$$ Need Solution
From:
Hi,
I assume your shading is currently done manualy.
It could easily done with VBA or even more easily with Conditional formatting,
as long as you aren't currently using it for something else.
Select all cells in the block
Open the conditional formatting dialogue box
Select 'Formula is'
Hi,
I have not heard that XL97 won't run on Windows 7, and would be very suprised
if it were so.
Regards - Dave.
Date: Sun, 12 Sep 2010 11:43:15 -0700
Subject: $$Excel-Macros$$ Excel 97 - Windows 7
From: spa...@corbetteer.co.uk
To: excel-macros@googlegroups.com
Currently running Excel
Hi,
Yes, you can use Call
eg:
Sub Run5Macros()
Call Macro1Name
Call Macro2Name
Call Macro3Name
Call Macro4Name
Call Macro5Name
End Sub
Regards - Dave.
Date: Mon, 13 Sep 2010 01:02:15 -0700
Subject: $$Excel-Macros$$ create a mcro in xls that launches
Hi,
20 digits is too much for XL, which I think only handles 16 digits accurately.
If you really need accuracy to that degree, you'll have to go elsewhere, or
start splitting the number over 2 cells, which makes the maths quite complex.
If you Google Big numbers in Excel you'll see lots of
Hi,
I am assuming that your first unique invoice numberis in A2.
Format B2 of the same sheet as Date, then enter the following:
=Sumproduct(Max((OtherSheet!A2:A100=A2)*(OtherSheet!B2:B100)))
Then copy down to the end of your unique data.
If you can't get this to work, post again with a
* 40436 = 0 * 40436 = 0
true * 40436 = 1 * 40436 = 40436, when formatted as a date = 9/15/2010
I like it...
Paul
From: Dave Bonallack davebonall...@hotmail.com
To: excel-macros@googlegroups.com excel-macros@googlegroups.com
Sent: Wed, September 15, 2010 12:39:40 AM
Subject: RE: $$Excel
Hi,
The answer to all your questions is Yes. But what we need in order to offer
specific help, is a framework - at least a sort of skeleton of a workbook, with
as much info as you know how to put in. From there, we can probably help you
little by little.
Regards - Dave.
Date: Fri, 17 Sep
Hi Kalyan,
Have a look at the attached. The solution I've come up with is rather
cumbersome, but I couldn't think of how to shorten it.
Regards - Dave.
Date: Sat, 18 Sep 2010 16:16:27 +0530
Subject: $$Excel-Macros$$ Stock Monitoring from Raw data
From: kalx...@gmail.com
To:
AA
B
14
AA
C
11
AA
D
13
AA
E
14
AB
A
13
AB
B
13
AB
C
14
AB
D
14
AB
E
12
AC
A
13
AC
B
10
AC
C
14
AC
D
11
AC
E
14
AD
A
14
AD
B
10
AD
C
10
AD
D
13
AD
E
13
- Original Message -
From: Dave Bonallack
To: excel-macros@googlegroups.com
Sent: Wednesday, September 08
Hi Ramkesh,
This can be done with an event macro, but not, I think, with worksheet formulas
and/or functions. Are you allowed to use macros in this workbook?
Regards - Dave.
Date: Tue, 28 Sep 2010 23:40:09 +0530
Subject: $$Excel-Macros$$
From: sunscel...@gmail.com
To:
A further question: Do you want the date in Col B to enter only when the
adjacent cell in Col A receives text for the first time, or any time the
adjacent Col A cell is changed?
Regards - Dave.
Date: Tue, 28 Sep 2010 23:40:09 +0530
Subject: $$Excel-Macros$$
From: sunscel...@gmail.com
To:
TMin mins
msg = msg TSec sec
MsgBox msg
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
Paul
From: Dave Bonallack davebonall...@hotmail.com
To: excel-macros@googlegroups.com excel-macros@googlegroups.com
Sent: Wed, September 29
Sub
Paul
From: Dave Bonallack davebonall...@hotmail.com
To: excel-macros@googlegroups.com excel-macros@googlegroups.com
Sent: Wed, September 29, 2010 5:14:06 AM
Subject: $$Excel-Macros$$ Macro hangs
Hi group,
I'm hoping someone can help me with the attached workbook.
I've written
Hi,
How would I prove row 32 is the first visible row through VBA
A = Activewindow.VisibleRange.Row
If i then wanted to make row 50 the first visible row, how could it be done
through VBA
Range(A2).Select
ActiveWindow.SmallScroll Down:=48
There's probably a better way of doing the
Hi,
A simpler alternative for your second requirement:
ActiveWindow.ScrollRow = 50
Regards - Dave.
Date: Tue, 28 Sep 2010 16:08:14 -0700
Subject: $$Excel-Macros$$ Visible Row Below Freeze Pane
From: spa...@corbetteer.co.uk
To: excel-macros@googlegroups.com
With the top row
And from me too, Ayush, congratulations! Very good service you provide here.
Lots of work and time behind the scenes. Thank-you.
Dave.
From: shubhangidesa...@gmail.com
Date: Sun, 3 Oct 2010 16:46:13 +0530
Subject: Re: $$Excel-Macros$$ Ayush Jain – Microsoft MVP 2010
To:
Hi,
For every unique value of col A - C and col F, then the number is increased by
1 from col G unless there is already a number for that combo in col H, then it
gets increased by 1 from that number in col H
I think I understand the first part of the sentence, but the meaning of the
Hi Alfred,
Please don't apologise for your English. We are quite tolerant here.
Have a look at the attached. First click on the 'Subject' buttons. You will
notice that clicking any one of them puts a Yes in it's Row, and changes the
other Rows to No.
Use these buttons to select which subject
email id so that
i can communicate directly? I have a long database now.
On Oct 21, 6:10 pm, Dave Bonallack davebonall...@hotmail.com wrote:
Hi,
Have a look at the attached to see if it does what you need.
Regards - Dave.
Date: Wed, 20 Oct 2010 20:04:57 -0700
Subject: $$Excel
Hi,
You can test for a non-contiguous selection with the following line of code:
A = Selection.Areas.Count
Use this in an If statement - eg
If Selection.Areas.Count 1 then msgbox You can't use non-contiguous cells.:
Exit Sub
Hope this helps.
Regards - Dave.
Date: Thu, 28 Oct 2010
VLOOKUP HERE
hope u understand
Thanks in advance
Girish
On Tue, Nov 2, 2010 at 10:08 AM, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi,
Your sample sheet gives insufficient and contradictory info.
Column B has a header which says USED HERE LEFT FUNCTION but there is no left
function
at 7:59 AM, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi Girish,
in USED VLOOKUP HERE column i used Vlookup function taken table _array as
SYSTEM DATA
This is not a formula. I need the actual formula you used so I can see what you
are trying to achieve.
I don't mind persisting
, it
works like a charme !
I'm sorry about the month for responding to you !
again : thanks a lot, it will a super example to learn more about
timers !
José
On 11 oct, 11:29, Dave Bonallack davebonall...@hotmail.com wrote:
Hi Alfred,
Please don't apologise for your English. We
this particular step??*
*It also seems that you have done conditional formatting in the main table.
But how is that only the month which am sorting is showing red in
colour??You need to explain me this step too.
*
*Regards*
*Anindya
*
On Mon, Nov 29, 2010 at 2:44 PM, Dave Bonallack
Hi,
Harmet, your formula is using relative values for the SUM(B2:B8) section, and
as a result, has created errors while copying down.
Formula in D2 should look like this:
=B2/SUM($B$2:$B$8)
Then copy down.
Regards - Dave.
From: harmeet.hew...@gmail.com
Date: Fri, 3 Dec 2010 11:36:59 +1100
for the correction and sorry for being so dumb :D.
Warm Regards,
Harmeet Singh
IT Analyst
McKinsey Company
http://www.facebook.com/Harmeeet
On Fri, Dec 3, 2010 at 1:16 PM, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi,
Harmet, your formula is using relative values for the SUM(B2:B8
Hi Vebhav,
You say you need to change the highlighted code daily, but you don't say what
you change it to, or on what basis it is changed. We need to know this if we
have any chance of helping you automate the process.
Regards - Dave.
Date: Tue, 7 Dec 2010 22:33:00 +0530
Subject:
Hi Vebhav,
You say you need to change the highlighted code daily, but you don't say what
you change it to, or on what basis it is changed. We need to know this if we
have any chance of helping you automate the process.
Regards - Dave.
Date: Wed, 8 Dec 2010 06:51:01 +0530
Subject: Re:
query.
On Wed, Dec 8, 2010 at 9:45 AM, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi Vebhav,
You say you need to change the highlighted code daily, but you don't say what
you change it to, or on what basis it is changed. We need to know this if we
have any chance of helping you
Hi,
Try this.
Sub Macro6()
If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect Else
ActiveSheet.Protect
End Sub
Regards - Dave.
Date: Wed, 8 Dec 2010 20:49:58 +0530
Subject: $$Excel-Macros$$ how to protect unprotect the sheet with same
button
From:
Regards
Rajesh kainikkara
On 12/9/10, Dave Bonallack davebonall...@hotmail.com wrote:
Hi,
Try this.
Sub Macro6()
If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect Else
ActiveSheet.Protect
End Sub
Regards - Dave.
Date: Wed, 8 Dec 2010 20:49:58 +0530
the
adjacent column.
Thanks,
Vebhav Jain
On Mon, Dec 13, 2010 at 8:32 AM, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi Vebhav,
You seem to be trying to autofill a range based on where the range currently
ends. Are you sure you don't want to fill Col 8 as far down as data in an
adjacent column
Hi Susan,
Not sure if this is the problem, but I thought I'd mention it;
When using a sheet name, the syntax is:
Worksheets(Base Scenario) or just Sheets(Base Scenario)
But when using the sheet number, neither quotes, nor the word 'Sheet' are not
used inside the brackets.
So it just becomes:
Hi,
A shorter version would be:
=MONTH(--(1A1))
This works for 3-lettered abbreviatios (eg Mar, Oct) as well as extended
abbreviations (eg Sept) and also unabbreviated (eg June, November)
Regards - Dave.
Date: Tue, 28 Dec 2010 15:34:01 +0700
Subject: Re: $$Excel-Macros$$ how to get month
, STDEV :)
Best Regards,
DILIPandey
On Tue, Dec 28, 2010 at 4:13 PM, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi,
A shorter version would be:
=MONTH(--(1A1))
This works for 3-lettered abbreviatios (eg Mar, Oct) as well as extended
abbreviations (eg Sept) and also unabbreviated (eg
Hi,
To just do the 3 levels you asked about, try:
=IF(B16500,3.5,IF(B161000,2.75,2.25))
Regards - Dave.
Date: Tue, 28 Dec 2010 20:18:30 +0530
Subject: Re: $$Excel-Macros$$ Nested IF functions?
From: dilipan...@gmail.com
To: excel-macros@googlegroups.com
Hi J D,
á
It would be really
.
Date: Wed, 29 Dec 2010 12:12:16 +0500
Subject: Re: $$Excel-Macros$$ how to get month Occurrence in no.
From: sajidmansooral...@gmail.com
To: excel-macros@googlegroups.com
Really Impressive!
Let me know how this formula works
On Tue, Dec 28, 2010 at 3:43 PM, Dave Bonallack davebonall
Hi,
A UDF (User Defined Function) is like any other function. It can only return a
value. It cannot do anything else. To change the background colour of a cell,
use Conditional formatting or a regular macro.
Regards - Dave.
Date: Wed, 5 Jan 2011 06:50:53 +0530
Subject: Fwd:
Hi Kalyan,
The attached works, with the following limitations:
That the data in Col A is always the same (as in your sample data)
That any given BP Name is not repeated in more than 1 Depot (as in your sample
data)
If either of the above limitations are unacceptable, then it's back to the
Hi,
Have a look at the attached. I've started with just '1' in the first cell, then
a formula after that, copied down to about row 30. Copy it down as far as you
need. The appropriate sequential number will appear when you enter data into
Col B cells. It is also self-correcting if you delete
Hi,
If you are using xl2007 or more, you can use the IFERROR function.
In your case, I think the syntax would go something like this:
=IFERROR(INDIRECT(A1D$65)*1,Linked Workbook not open!)
But I don't have XL2007 here to check it.
Hope this helps.
Regards - Dave.
Date: Mon, 10 Jan 2011
Hi Ayush,
There seems to be some confusion. I thought the question related to our
favourite formula, not favourite function. Please clarify.
Regards - Dave.
From: rohan.j...@gmail.com
Date: Wed, 12 Jan 2011 16:01:19 +0530
Subject: Re: $$Excel-Macros$$ Formula of the week - share your best
Hi,
Not sure if I'm missing the point here, but what about just typing the date
into your first cell, then press enter. From there, select your first cell and
drag the copy-down handle down as far as you want. XL defaults to incrementing
the date one day at a time.
Regards - Dave.
Date:
Hi,
The problem is not the sheet names, but the indirect refering to a date, which
is really just a date code, not a date as displayed.
Regards - Dave.
Date: Thu, 13 Jan 2011 23:08:24 -0800
Subject: Re: $$Excel-Macros$$ Formula of the week - share your best formula
From:
, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi,
The problem is not the sheet names, but the indirect refering to a date, which
is really just a date code, not a date as displayed.
Regards - Dave.
Date: Thu, 13 Jan 2011 23:08:24 -0800
Subject: Re: $$Excel-Macros$$ Formula of the week
Hi,
I would like to submit the following formula as one I like.
It performs a case-sensitive Vlookup, and is non-array.
=LOOKUP(2,FIND(A1,Sheet2!A:A),Sheet2!B:B)
A1 contains the lookup value
Sheet2!A:A is the lookup column
Sheet2!B:B is the return column
I hasten to add that this formula is
getting #NUM! with the formula? (See Attached)
Thank you.
John
On Fri, Jan 14, 2011 at 10:35 AM, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi,
I would like to submit the following formula as one I like.
It performs a case-sensitive Vlookup, and is non-array.
=LOOKUP(2,FIND(A1,Sheet2!A:A
Hi David,
Sounds like your spreadsheets are using different calendars systems.
Excel can use the 1900 system or the 1904 system. 1900 is the default setting.
In XL2003, go to Tools, Options, Calculations Tab. There make sure that the
1904 thingy is unchecked.
Do the same for both workbooks.
In
Hi,
You need to tell us how this data is entered.
Is each line 1 cell, 2 cells, or 3 cells?
Regards - Dave
Date: Fri, 14 Jan 2011 23:11:40 -0800
Subject: $$Excel-Macros$$ there has to be an easier way!
From: gold.her...@gmail.com
To: excel-macros@googlegroups.com
Hi,
my timecard dumps
, and c1 has time
a2 has OUT, b2 has date, and c2 has time
On Jan 15, 12:49 am, Dave Bonallack davebonall...@hotmail.com wrote:
Hi,
You need to tell us how this data is entered.
Is each line 1 cell, 2 cells, or 3 cells?
Regards - Dave
Date: Fri, 14 Jan 2011 23:11:40 -0800
Subject
Sorry Aamir, Can't understand the question. If your worksheet had a cell with
the desired answer in it (entered manually) this may help me understand your
request.
Regards - Dave.
From: aamirshahza...@gmail.com
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Query
Date: Mon, 17
:10 AM, ashish koul koul.ash...@gmail.com wrote:
check the attachment see if it helps you
On Tue, Jan 18, 2011 at 10:36 AM, Dave Bonallack davebonall...@hotmail.com
wrote:
Sorry Aamir, Can't understand the question. If your worksheet had a cell with
the desired answer in it (entered
Hi Manish,
Please attach the solution you have. Perhaps one of us may be able to speed it
up.
Regards - Dave
Date: Tue, 18 Jan 2011 23:24:56 -0800
Subject: Re: $$Excel-Macros$$ Re: Concatenate Value Problem
From: pansari.man...@gmail.com
To: excel-macros@googlegroups.com
Any Help
Hi,
IFERROR can be stacked, just like IF can be.
Try this formula in C4, then copy down:
=IFERROR(VLOOKUP(B4,$E$3:$F$3,2,0),IFERROR(VLOOKUP(B4,$H$3:$I$3,2,0),VLOOKUP(B4,$K$3:$L$3,2,0)))
Regards - Dave
Date: Tue, 25 Jan 2011 22:57:34 +0500
Subject: $$Excel-Macros$$ Lookup values in
Hi,
I think that mm will return 02, while m will return 2
Regards - Dave
From: anil.bha...@tatacommunications.com
To: excel-macros@googlegroups.com
Date: Thu, 3 Feb 2011 14:02:16 +0530
Subject: RE: $$Excel-Macros$$ HOW TO CONVERT DATE IN TEXT WITH FOMULA
Hi Aamir,
Please see the
Hi Solomon,
Have a look at the attached. Most numbers-to-text conversions are done with
VBA, but this one is done with formulas and functions. It uses a bunch of cells
to do its workings. In your case I have put those cells in the range A50:K62.
If you need that range for something else, then
301 - 400 of 444 matches
Mail list logo
