Create a Name in your workbook (ribbon Tab Formula Name Manager
Name (for Excample) : SheetsCount
Refer to: =GET.WORKBOOK(4)
[image: Create a NAME.PNG]
in the sheet you can use
*=SheetsCount*
resultling number of sheet in the workbook
they called it : Macro Excel4
It seems you need a new template standards.
Open a new workbook
Set the desired number of sheet in the workbook
Select all sheets at once as a group
Select all cells (Ctrl + A)
Format the cells of this workbook in your
own font-style, font-size, and so on,
including the format number (=
Another *Array Formula* for Simple Calendar
(MultipleResult Array Formula on 7 columns X 6 Rows) based on Date in a
cell F22)
grid formula
=$F$22-WEEKDAY($F$22)+COLUMN(1:7)+ROW(1:7)*7-7
weekday name formula
=TEXT({1,2,3,4,5,6,7},ddd)
On Sun, Oct 30, 2011 at 8:40 AM, NOORAIN ANSARI
Dear Mr excel,
You are welcome to visit your brother here
http://www.mrexcel.com/articles.shtml#VBA
best regards
~siti
your fans from indonesia
On Thu, Oct 20, 2011 at 8:21 AM, Mr excel excelkeec...@gmail.com wrote:
Thanks all,for the replies.i had been working with the VBA macro
without macro solution
Assuming 1,2,3,4,5 etc is stored in A1:A300
Put this formula in D1
*=OFFSET($A$1,ROW(A1)*3-(4-COLUMN(A1)),0)*
copy right down 3 columns x 100 rows
best regards,
~ siti
On Fri, Aug 26, 2011 at 8:45 PM, GreenBriar impedimenta.st...@gmail.comwrote:
Transpose
http://chrisrae.com/vba/whatisvba.html
http://www.excel-vba.com/
http://www.excel-vba-easy.com/
http://www.youtube.com/watch?v=t1vdt0FddsE
On Fri, Aug 26, 2011 at 10:24 PM, Mohamed Youssouf
youssouf.mohame...@gmail.com wrote:
I'd like to learn how ti use MACRO
--
A little correction
Sub cleanrowsandcolumnsSAS()
lr = Cells.Find(*, Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row *+ 1*
lc = Cells.Find(*, Cells(Rows.Count, Columns.Count) _
, , , xlByColumns, xlPrevious).Column *+ 1*
Application.EnableEvents = False
*1.
**Worksheet.Change Event*
*
*Occurs when cells on the worksheet are changed by the user or by an
external link.
This event doesn't occur when cells change during a recalculation. Use the
Calculate event to trap a sheet recalculation.
This example changes the color of changed cells to blue.
visit ..
http://phoenix.phys.clemson.edu/tutorials/excel/regression.html
http://mallit.fr.umn.edu/fr4218/assigns/excel_reg.html
http://www.chem.orst.edu/courses/ch361-464/ch464/RegrssnFnl.pdf
http://faculty.fuqua.duke.edu/~pecklund/ExcelReview/Use%20Excel%202007%20Regression.pdf
Function MonthFrac(StartDate As Date, EndDate As Date) As Single
*'= siti Vi / Jakarta 12 July 2011*
Dim YY1 As Integer, MM1 As Integer, DD1 As Integer
Dim YY2 As Integer, MM2 As Integer, DD2 As Integer
Dim MM As Integer, DD As Integer, EndMonthDays As Integer
YY1 =
how about:
fraction to the LASTMONT ( days in EndDate Month)
On Wed, Jul 13, 2011 at 9:28 AM, Dave Bonallack
davebonall...@hotmail.comwrote:
Hi,
Fractions of a month are a bit subjective. Fractions of WHICH month, needs
to be decided. Fractions of a 28-day month? 29- 30- or 31- day month?
Converting text case to ProperCase in VBA
alt:
Instead of using Application.Function / WorksheetFunction) we can also use
VBA function:
*StrConv *function
StrConv( , vbProperCase)
Example in sub procedur
Sub ConvertProperCase()
Application.ScreenUpdating = False
Dim Cell As
=*LOOKUP*
(D16,{3001,5001,6001,7001,8001,9001,15001,25001,40001},{30,40,45,50,60,110,130,150,200})
or..
=*VLOOKUP*(D16,$A$1:$B$9,2,TRUE)
the above VLookUp fungtion refers to a table
*tabel A1:B9*
* 3001 30 *
* 5001 40 *
* 6001 45 *
* 7001 50*
* 8001 60*
* 9001 110*
*15001 130*
you are talking about *Permutation*
given number : 1 2 3 4 5
total arrangement of permutation *=FACT(5)* = *120*
==
12345 21345 31245 41235 51234
12354 21354 31254 41253 51243
12435 21435 31425 41325 51324
12453 21453 31452 41352 51342
12534 21534 31524 41523 51423
12543 21543
'--Thisworkbook Module:--
Dim OldStaBar As String
Private Sub Workbook_Open()
OldStaBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = ThisWorkbook.FullName
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sub Insert2Rows()
' siti Vi / jakarta, 29 Jun 2011
'---
Dim RNG As Range, r As Long
Set RNG = ActiveSheet.Cells(1).CurrentRegion.Offset(1, 0)
For r = RNG.Rows.Count To 2 Step -1
If r Mod 2 0 Then RNG(r, 1).Resize(2, 1).EntireRow.Insert
Next
Dim ws As Worksheet
Const pass As String = rash
Dim ArChkBox(1 To 6) As Object
Private Sub UserForm_Activate()
Set ArChkBox(1) = CheckBox1
Set ArChkBox(2) = CheckBox2
Set ArChkBox(3) = CheckBox3
Set ArChkBox(4) = CheckBox4
Set ArChkBox(5) = CheckBox5
Set ArChkBox(6) = CheckBox6
You need some Variables (memory variable)
http://www.excel-vba.com/excel-vba-contents.htm
Sub Test()
'--declaring variable
Dim MyCell As Range
Dim MyValue As Variant
Dim CompareResult as Boolean
'--assignments
Set MyCell = Range(A1)
MyValue = MyCell.Value
'--copying the value to another
please check this vba code, hope it helps..
-Standard Module
Sub ExpiryWarning()
' siti Vi // Jakarta, 19 Jan 2011
'
Dim dTable As Range, t As String
Dim R As Long, N As Long, I As Integer
Set dTable = Cells(1).CurrentRegion
N = dTable.Rows.Count
use Recuva
Recuva recovers files deleted from your Windows computer, Recycle Bin,
digital camera card, or MP3 player. And it's free!
http://www.piriform.com/recuva
http://www.piriform.com/recuva
On Fri, Jun 17, 2011 at 12:26 PM, Bhushan Sabbani bsabban...@gmail.comwrote:
Dear Excel Expert,
Dear Mr. Smith,
Please try and check this VBA Code, if it helps...
Sub AbnormalizeYourTabel()
' STDEV(i) / milis belajar-excel / 08 apr 2011
' retouched for another table-structure
' jakarta, Jun 12, 2011
'---
Dim Tbl As Range, NewTbl
=OFFSET($A$1,MATCH(E2,$B$2:$B$8,0),0)
or
=INDEX($A$2:$B$8,MATCH(E2,$B$2:$B$8,0),1)
On Fri, Jun 10, 2011 at 3:14 AM, hanumant shinde arsfan2...@yahoo.co.inwrote:
Hi friends,
this is really really urgent and very very very IMP for me so quick help
would
be really really appreciated.
May be, by hiding the rows that contains formula, before printing
A cell contaning formula can be detected by *HasFormula property*
Please check this code if it helps.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim MyRng As Range, r As Long, c As Integer
Set MyRng =
please check and try this VBA code if it helps
Sub DoSomething()
* ' siti Vi*
Dim w As Worksheet, xCel As Range
Dim sCriteria As String
sCriteria = MyCriteriaText ' please edit
For Each w In Worksheets
If Not w.Name = ActiveSheet.Name Then
If Not LCase(w.Name) =
Dear GoldenLance,
Thank you very much for your great sugestion..
user just *selects/activates a cell* where the criteria is written, and run
the macro...
kindest regards,
STDEV(i)
On Sat, Jun 4, 2011 at 5:57 PM, GoldenLance samde...@gmail.com wrote:
Just a minor tweak to STDEV(i)'s code
Sub Copy_One_File_Only()
FileCopy D:\Bla_Bla_Bla\SOS\SourceFolder\FileName.pdf,
E:\WOW\AngelinaJoly\DestinFolde\FileName.pdf
End Sub
On Sun, May 29, 2011 at 11:06 PM, PAB pab1...@gmail.com wrote:
Hi
Using VBA in Excel 2003 is it possible to select a pdf file in a
folder and move
try this formula
=*SUBSTITUTE(B3*
,MID(B3,1+FIND(^,SUBSTITUTE(B3,\,^,LEN(B3)-LEN(SUBSTITUTE(B3,\,-1,999),)
and see the attachment if it helps
On Fri, May 27, 2011 at 12:56 PM, Jhoomla amith.yeshwa...@gmail.com wrote:
Does any one have a function or formula to extract for a path for
eg.,
Assuming that you r date is in Cell A1
Convert them with this formula:
Formula in B1=Date(2010,Month(A1),Day(A1))
On Fri, May 27, 2011 at 12:49 AM, Rich Prince rich.pri...@gmail.com wrote:
I have a column of various dates that have the incorrect year, for
example:
9/1/2011
when you type a data ( event a dot ) in a cell,
the old data (incl. FORMULA) in the cell will be removed
and replaced with the new data.
with macro you can automatically keep the old data
(and stored in another place)
and then enter the new value in the same cell
cmiiw
On Mon, May 23, 2011 at
is not completely removed
using TRIM ( ) could anyone suggest what to do in such case?
On May 18, 3:38 am, STDEV(i) setiyowati.d...@gmail.com wrote:
sometimes what we thought as the space (as prefix or suffix) turns out to
be
*Char (160)*so we need formula like this
=SUBSTITUTE(A1,CHAR(160
*Array Formula, written in 9 cells (oneRow Range) at once !*
=IFERROR(TRANSPOSE(INDEX($B$3:$B$11,SMALL(IF($A$3:$A$11=$C16,ROW($1:$9)),ROW($1:$9,)
On Wed, May 18, 2011 at 5:36 PM, KAUSHIK SAVLA savla.kaus...@gmail.comwrote:
Hi All,
I want to transpose certain data from row to column if
Put this code into *Sheet1 Module*
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count = 1 Then
If .Column = 8 Then Target(1, 2).Activate
If .Column = 9 Then Target(2, 0).Activate
End If
End With
End Sub
' this macro run in column H and column I of
it looks like you need a textbook / excel standard manual
Go to a bookstore and buy one
you can also visit to this sites:
http://blog.contextures.com/archives/2010/03/10/sort-it-your-way-with-excel-custom-lists/
http://www.ehow.com/how_5905455_create-custom-list-excel-2007.html
sometimes what we thought as the space (as prefix or suffix) turns out to be
*Char (160)*so we need formula like this
=SUBSTITUTE(A1,CHAR(160),)
On Sun, May 15, 2011 at 12:48 PM, Prabhu prabhugate...@gmail.com wrote:
Hi friends,
How to remove spaces(bugs) in a particular cell either will be
Sub InsertDatetUntilToday()
' coded by: siti Vi / Jakarta, May 16, 2011
'---
Dim SeleDate As Date, DiffDate As Long, n As Long
Do While ActiveCell 0
If IsDate(ActiveCell) And ActiveCell Date Then
If Not IsEmpty(ActiveCell(2, 1))
you should visit
http://www.rondebruin.nl/merge.htm
On Mon, May 16, 2011 at 12:44 AM, Prathima R prathima@gmail.com wrote:
Hi All,
i have excel data in five differrent workbooks which i will consolidate
into one workbook and apply filter and select data by category Xin
particular
i like this cool formula
=32-DAY(A1-DAY(A1)+32)
last day in a month / number of days in a month
based on a date.
On Sun, May 15, 2011 at 8:53 AM, Armando amon...@gmail.com wrote:
Try these:
=DAY(EOMONTH(A1,0))
=32-DAY(A1-DAY(A1)+32)
--
thanks to mr verma, with his formula
=DAY(DATE(YEAR(TODAY()),MONTH(*(B21)*)+1,1)-1)
how about
=DAY(DATE(YEAR(TODAY()),MONTH((B21))+1,*0*))
The *last day of this month* is = the *0*th day of the Next Month !!
On Sat, May 14, 2011 at 7:03 PM, rajan verma rajanverma1...@gmail.com
wrote:
find
use your keyboard
On Wed, May 11, 2011 at 6:15 PM, jmothilal gjmothi...@gmail.com wrote:
I am typing Name and place name day 2 day different excel files,
what is easy method i type these names and place names.
--
J.Mohilal
Universal Computer Systems
# 16, Brindavan Complex
to mention the year 1998
in this manner 10498.
---
*Sixthsense
**:) Man of Extreme Innovative Thoughts :)*
On Fri, May 13, 2011 at 4:13 PM, STDEV(i) setiyowati.d...@gmail.comwrote:
*
=DATE(2000+RIGHT(B2,2),MID(TEXT(B2,00),3,2),LEFT(TEXT(B2,00),2))
*
see the attachment
On Thu, May
another variant
=DATE(20RIGHT(B2,2),MID(TEXT(B2,00),3,2),LEFT(TEXT(B2,00),2))
On Thu, May 12, 2011 at 8:58 PM, GoldenLance samde...@gmail.com wrote:
Use this in E2 and drag down
=DATE(2000+RIGHT(B2,2),LEFT(RIGHT(B2,4),2),LEFT(B2,LEN(B2)-4))
On May 12, 5:14 pm, Prabhu
please check the attachment if it helps..
best regards,
STDEV(i)
note:
we need dynamic* Max Value* of ScrollBar
so we use Scroolbar from ActiveX Control, instead of from FORM.
A little macro wil work each time you change the career
On Wed, May 11, 2011 at 5:21 PM, karan 1237 karan1
try using
Application.OnTime method
http://www.ozgrid.com/Excel/run-macro-on-time.htm
http://www.cpearson.com/excel/OnTime.aspx
On Mon, May 9, 2011 at 1:17 PM, Anil Bhange
anil.bha...@tatacommunications.com wrote:
Hi All,
I am having one database which is link to the external data
, you are requested to attached the result sheets to
this groups.
I 'll do the same on May 10, 2011.
thank you and best regards
STDEV(i)
On Sun, May 8, 2011 at 3:13 PM, rajan verma rajanverma1...@gmail.com wrote:
hi STDV(i)
Your macro only working with 3 Rows.. if he want to add more data
Unfortunately *Month Function* returns *Month Index ( 1 to 12 )* not *Month
NAME*
Assuming A1 containts date data : 12/31/2011
Formula in B1 =MONTH(A1) returns : *12* not *December*
In vba you can used MonthName Function
Sub AboutMonth()
Range(B1) = Month(Range(A1))
Range(C1) =
Your code looks like to be run in excel 2003 only
(1)
In both excel 2003 / excel 2007
Range(A65000) ' last row in A column
can be coded as
Cells(*Rows.Count*,1)
or
Range(A *Rows.Count*)
(2)
*Value* property of a Range Object is a *DEFAULT Property*
so you can ignore it
For example
It would be easy for anyone in the group to respond, if you can send a set
of sample data.
On Sat, May 7, 2011 at 1:47 PM, maulik desai mauliksde...@gmail.com wrote:
Hi All,
I have excel database sheet
I have created some excel output sheets (around 35 excel sheets) which
gives me output
try to use this UDF (user defined function)
Function UniqueList(Dat As Range)
' List of UnSorted Unique Values '
'-'
Dim Cel As Range, vArr(), n As Long, Tx As String
Tx = ,
For Each Cel In Dat
If InStr(1, Tx, , Cel , ) = 0 Then
instead of writing =NOW() or =TODAY() in a cell
select a cell then
press Ctrl + ;(for FIXED today's date )
press Ctrl + Shift + :(for Fixed Now time )
On Sun, May 8, 2011 at 2:12 AM, Bob bobandrich...@comcast.net wrote:
If I enter the Customer in B27 I'd like for todays date to be
Dear Indrajit
please try this code
Private Sub CommandButton1_Click()
' siti Vi / jakarta, May 07, 2011
Dim R As Long
R = WorksheetFunction.CountA(Range(A:A)) + 1
Cells(R, 1) = Cells(1, 3)
Cells(R, 2) = Cells(1, 4)
End Sub
On Sat, May 7, 2011 at 3:26 AM, Indrajit $nai
properly if these is any blank row between the
Data.. It will all time replace existing Data..
On Sat, May 7, 2011 at 8:56 AM, STDEV(i) setiyowati.d...@gmail.comwrote:
Dear Indrajit
please try this code
Private Sub CommandButton1_Click()
' siti Vi / jakarta, May 07, 2011
Dim R As Long
how can we forget this:
http://www.excelitems.com/2010/11/shrink-reduce-excel-file-size.html
http://www.excelitems.com/2010/11/shrink-reduce-excel-file-size.html
On Wed, Apr 27, 2011 at 6:05 PM, Sundarvelan N nsund...@gmail.com wrote:
How to reduce the file size
--
Thanks
N.Sundarvelan
Save As *.PDF (excel 2007)
[image: save as PDF.GIF]
On Thu, Apr 28, 2011 at 7:39 PM, Anil Bhange
anil.bha...@tatacommunications.com wrote:
Hi Expert,
I am having one excel file which contains many report, I wanted to convert
the same in PDF. Below example will give you further details,
by hiding application objec at time of userform initialized or activated
Application.visible = False
and don' forget to unhide the aplication (excel) at time of userform
deactivated or unloaded
Application.visible = False
se attachaed workbook
On Wed, Apr 27, 2011 at 1:38 PM, NOORAIN ANSARI
when you say it is a challenge; then you have to have your own good answer.
Sub Time_Test_Array2()
Dim DatRng As Range, LastRow As Long
Dim r As Long, i As Long
Application.ScreenUpdating = False
Range(E:E, K1:K3).Clear
Range(K1) = Timer
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set DatRng
1. *array formula*
=OFFSET(Sheet1!$B$6:$C$6,MATCH($C$4,Sheet1!$B$6:$B$55,0),0,5,2)
witten on I6:J10 at one !
2. formula wittten in one cell I5
=$C$4
On Fri, Apr 22, 2011 at 1:47 PM, Jai jaihumtu...@gmail.com wrote:
Attachemtn attached
--
--
then you have to ask to the author of the spreadsheet
On Fri, Apr 22, 2011 at 10:19 PM, vamsi varma tv.vamsikris...@gmail.comwrote:
dear friends,
i need some help..
i need to cal the macro in another spreadsheet. But unfortunately that
spreadsheet is locked. I dont know the name of the
http://www.techonthenet.com/excel/formulas/stdev.php
http://www.techonthenet.com/excel/formulas/stdev.php
http://www.excelfunctions.net/Excel-Stdev-Function.html
http://www.excelfunctions.net/Excel-Stdev-Function.html
http://support.microsoft.com/kb/826349
and
F1 function button on you keyboard
assuming that the both workbooks are openned
Sub CanSomeOneHelp()
Dim RgDest As Range
With Workbooks(Anodize.xls).Sheets(1)
.Unprotect
Set RgDest = .Cells(1).CurrentRegion
End With
Set RgDest = RgDest.Cells(RgDest.Rows.Count + 1, 1)
).CurrentRegion.Copy RgDest
RgDest.Parent.Protect
Application.CutCopyMode = False
*Workbooks(Quote.xls).Sheets(1).Cells(1).CurrentRegion.ClearContents*
End Sub
On Wed, Apr 20, 2011 at 7:51 AM, STDEV(i) setiyowati.d...@gmail.com wrote:
assuming that the both workbooks are openned
Sub
try to use this UDF and see if it helps..
Function GetNumbers(S As String, Index As Integer)
'
' siti Vi / jakarta, 30-08-2009
'
Dim ArrN(), i%, tmp$, n%, t$, t2$
S = Trim(S) |
For i% = 1 To Len(S) - 1
t$ =
unlock all cells in column A
instanciate a checkbox in cell A1 (CheckBox1)
create a conditional formatting for B1:C1
put the thos vba code to Sheet1 Module
Private Sub CheckBox1_Click()
Me.Unprotect
If CheckBox1 = False Then
Range(B1:C1).Locked = True
Else
plese check the attached workbook, and see if it helps.
*ARRAY FORMULA*
=small(if(left(C2:C29,len(F4))=F4,row(1:28)),row(1:28))
FORMULA
=IF(ISERR($D6),,OFFSET(A$1,$D6,0))
On Mon, Apr 18, 2011 at 3:16 PM, C.G.Kumar kumar.bemlmum...@gmail.comwrote:
PFA sample File.
-- Forwarded
Sub ABCD()
Sheets(MySheet).Unprotect yourpassword
--- your program accessing to sheets(MySheet)
Sheets(MySheet).Protect yourpassword
End sub
On Fri, Apr 15, 2011 at 6:51 PM, Rajesh K R rajeshkainikk...@gmail.comwrote:
Hi Experts,
I want to password protect my work sheets, but the same time
You can check the GROUPNAME property of your OptionButton
(active-X-Controls)
A group / a set of OptionButtons shd have SAME GroupName
[image: OptionButton GroupName.GIF]
On Thu, Apr 14, 2011 at 8:04 AM, Dick bobde...@yahoo.com wrote:
Why is some option buttons linked to each other and some
try this formula and see if it helps
=SUM(OFFSET($D8:$I8,0,0,1,MONTH(TODAY(
On Wed, Apr 13, 2011 at 12:23 AM, vinod rao vinod.ma...@gmail.com wrote:
Hi,
I need a formula adding each month one column.
Attached file has a details.
Regards,
Vin
--
_xlfn IS
http://office.microsoft.com/en-us/excel-help/issue-an-xlfn-prefix-is-displayed-in-front-of-a-formula-HA010204569.aspx
On Sat, Apr 9, 2011 at 11:02 PM, Dhananjay Pinjan dppin...@gmail.comwrote:
Pl. explain what is *=_xlfn*.???
Is it User Defined function or what else?
Regards,
*=TextDif(A2,B2)*
TextDif is an UDF, like this:
Function TextDif(S1 As String, S2 As String) As String
Dim Arr1, Arr2
Dim n As Integer, i As Integer, t As String
Arr1 = Split(S1, ,): Arr2 = Split(S2, ,)
For i = LBound(Arr2) To UBound(Arr2)
For n = LBound(Arr1) To UBound(Arr1)
i think mr. bhanu needs
000999 tobe 999 not 00999
On Mon, Apr 4, 2011 at 11:45 AM, §»VIPER«§ viper@gmail.com wrote:
if your data in a1 type the below formula on anywhere in the same worksheet
=IF(AND(ISTEXT(A1),LEFT(A1,1)=0),RIGHT(A1,LEN(A1)-1),A1)
--
*Thanks Regards
Thamu
*
run this macro
Sub RemoveAllZeros()
Dim xCell As Range
For Each xCell In ActiveSheet.UsedRange
xCell = Replace(xCell, 0, )
Next
End Sub
since you ask for REMOVING ZERO IN EVERY CELL
1500 will be 15
2100500 will be 215
and so on
On Sun, Apr 3, 2011 at 9:55 PM, bhanu prakash
- bhanu
On 04-Apr-2011 3:45 AM, STDEV(i) setiyowati.d...@gmail.com wrote:
run this macro
Sub RemoveAllZeros()
Dim xCell As Range
For Each xCell In ActiveSheet.UsedRange
xCell = Replace(xCell, 0, )
Next
End Sub
since you ask for REMOVING ZERO IN EVERY CELL
1500 will be 15
CompareWorksheetsAdd-in.xla !!!
It is a usefull tool / add-in ..
Thank you Mr Ashish
best regards,
STDEV(i)
On Fri, Apr 1, 2011 at 12:21 AM, ashish koul koul.ash...@gmail.com wrote:
try this
On Thu, Mar 31, 2011 at 8:37 PM, hanumant shinde
hanumant_5...@yahoo.co.in wrote:
Hi Guys
so... make it simplest
=IF((B2=7/24)*(B2=*21*/24),NO,YES)
On Thu, Mar 31, 2011 at 12:23 AM, JsinSk jsin...@gmail.com wrote:
On my response I screwed up the 24 hour time format! Should be 21:00
instead of 17:00.
On Mar 30, 1:17 pm, STDEV(i) setiyowati.d...@gmail.com wrote:
Try
is the value of the
shipment yet to ship. =MAX(range) works but it senses the zero if it is
gone so the =MIN(range) doesn't.
Thank you for your kind teaching help.
John
On Tue, Mar 29, 2011 at 4:17 PM, STDEV(i) setiyowati.d...@gmail.comwrote:
Please try, and check if it helps
=IF(COUNT('Dept
Try this formula
=IF((B2=7/24)*(B2=9/24),NO,YES)
On Wed, Mar 30, 2011 at 6:53 PM, zinknax.zin...@gmail.com wrote:
Hi All,
I need formula to update column C, value if the time is between 7:00 AM to
9:00 PM YES else NO
Find attached for the same.
Column AColumn BColumn C
in excel 2003
use menu: Window New Window
and then: menu Window Arrange
On Wed, Mar 30, 2011 at 12:54 PM, manhar prajapati
prajapati.man...@gmail.com wrote:
Hi expert
I have a software which show two diff. work sheet in one excel under
different tab.
If any one know that software
your data in B columns are TEXT data, not time data / numbers data
so you can pass into LARGE function
please try this array formula
=TEXT(LARGE(TIMEVALUE(RIGHT(B2:B886,8)),ROW(1:10)),[hhh]:mm:ss)
array formula written on one_column (10 cells) at once !!
On Mon, Mar 28, 2011 at 5:59 PM,
please try this Array formula
=ROUND(AVERAGE(--LEFT(A1:A3,3)),0)/ROUND(AVERAGE(--RIGHT(A1:A3,2)),0)
Array Formula needs to be entered with 3 keys : Ctrl + Shift, Enter !
On Mon, Mar 28, 2011 at 12:28 AM, Susan sunni...@gmail.com wrote:
Hi experts!
I have a project to do given to me by a
Thank you Mr. Ayush..
Thank you Mr. Bonallack
Best Regards
siti
On Sat, Mar 26, 2011 at 9:43 PM, Ayush jainayus...@gmail.com wrote:
Really Awesome formula.
Thanks Siti Vi. I have published this formula on the same page along with
your name.
Regards
Ayush Jain
--
-HA001087290.aspx
On Sat, Mar 26, 2011 at 9:18 PM, Rajesh K R rajeshkainikk...@gmail.comwrote:
Hi Dev
Thank u very much for your quick reply, unfortunately I don't have any
idea about array formulas.
how can I learn it properly.
Regards
Rajesh Kainikkara
On 3/25/11, STDEV(i) setiyowati.d
no attachments in your mail/posting
just for info
Color Index can be detected by
*(1) making and UDF (user Definded Function)*
Public Function ColorIndex(Rng as range) as long
ColorIndex = Rng.Interior.ColorIndex
End Function
in worksheet you just use this NEW FUNCTION
=ColorIndex(B5)
please find attached
and see if it helps
best regards
siti Vi
On Sat, Mar 26, 2011 at 9:15 PM, Rajesh K R rajeshkainikk...@gmail.comwrote:
On 3/26/11, Rajesh K R rajeshkainikk...@gmail.com wrote:
Hi Experts
How to understand the room status, by color code.pls check the
attachment.
Please try my UDF for the same
Public Function ColumnLetter(N As Long) As String
ColumnLetter = Replace(Replace(Cells(1, N).Address, $, ), 1, )
End Function
On Fri, Mar 25, 2011 at 10:40 PM, Ayush jainayus...@gmail.com wrote:
Hello everyone,
Here is the UDF to convert a number into
an *array formula* =IFERROR(INDIRECT(C3), ) is written in a range C5:H20
at once !!
about array formula:
http://www.cpearson.com/excel/ArrayFormulas.aspx
http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx
but before you do it, you have to define
=LEFT(A1,FIND(@,A1,FIND(@,A1)+1)-1)
or
=MID(A2,1,FIND(@,A2,FIND(@,A2)+1)-1)
of
array formula
=LEFT(A3,MAX((MID(A3,ROW(INDIRECT(1:LEN(A3))),1)=@)*ROW(INDIRECT(1:LEN(A3-1)
On Fri, Mar 18, 2011 at 3:16 PM, Deepak Rawat deepakexce...@gmail.comwrote:
Hi Masters,
My query is to remove all the
=INT(I3)+CHOOSE(MATCH(I3-INT(I3),{0,0.32999,0.82999},1),0,0.5,1)
or
=IF(ROUND(MOD(I3,1),2)=0.83,ROUNDUP(I3,0),IF(ROUND(MOD(I3,1),2)=0.33,TRUNC(I3)+0.5,TRUNC(I3)))
and more..
On Fri, Mar 18, 2011 at 10:42 PM, Rajesh K R rajeshkainikk...@gmail.comwrote:
I want to round the value 16.33 as 16.5
if you open a blank workbook in excell 2007
the workbook is in compatibility mode
save your workbook in *.XLSX format
close your workbook, and open again
ta da
On Mon, Mar 7, 2011 at 5:36 PM, dpk dpk1...@gmail.com wrote:
how to increse rows in excel 2007 from 65 k to more
--
you are talking about List of Unique Values
in Excel 2007 you can use REMOVE DUPLICATE
in Excel 2003 use : Advanced Filter (Copy to another range / UniqRecords
Only / Criteria = blank)
or you can use a mega-formula see my example workbook at
http://www.box.net/shared/o614r2gnrj
On Tue, Mar
excel is opening a text File
the array are data for column index and length of string string to be
converted to a cells
Array(Array(1, 2), Array(2, 4))
text file contents: ABCDEFG
converted to 2 columns
column 1 = AB
column 2 = CDEF
If you use [Text To Column] ( of DATA menu) you will
let us be very patient, waiting for some one who
give a formula that can write a Constat_Date into a cell
On Mon, Mar 14, 2011 at 1:06 AM, Hems coolh...@gmail.com wrote:
Hey STDEV(i),
Thanks a lot but i dont want macro, i want formula for it.
Let me know if is there any formula
in the google search box, type sumproduct
On Mon, Mar 14, 2011 at 2:50 PM, sudhir kumar bluecore...@gmail.com wrote:
Hi
Dear Members
can any one sent me example of Sumproducts.
thanking u
--
--
Some
put this code into sheets' module
Private Sub Worksheet_Change(ByVal Target As Range)
' siti Vi villagera.g...@gmail.com
' jakarta, 11 mar 2011
'
If Target.Count = 1 Then
If Target.Column = 1 Then
If Target.Row 1 Then
If Len(Target)
While condition
'-- your macro
Wend
condition is an expression that evaluate to TRUE or FALSE
Ex: yr data are in cell B4-down.
i = 1
With Activesheet.Range(B4)
While Len(.cell(i,1) 0
.cell(i,1).texttocolumns destination:=.cell(i,2),
'-- etc --
i = i + 1
Wend
End with
On 2/10/11, Jorge
other stuff
=MONTH(DATEVALUE(1 A1 2010))
On Tue, Dec 28, 2010 at 3:34 PM, siti Vi villager.g...@gmail.com wrote:
If the word April or *another month name *is type correctly in cell A1
try this formula in B1
=TEXT(DATEVALUE(1 A1 2010),M)
On Tue, Dec 28, 2010 at 1:56 PM, Rohan Young
you can save your workbook (containing ONE worksheet)
as XX.TEXT
or as XX.CSV
please note the notepad (a TEXT editor) is not contains multi cells like
excel worksheet
On Mon, Oct 18, 2010 at 2:07 PM, Krishna krishnaja...@gmail.com wrote:
Dear Vijaykumar,
Do you mean, an is
95 matches
Mail list logo
