report sorting formula
Hi everyone -
awhile back i posted on here a question about sorting a report in a
"weird" way. i have a slightly different question now...
i have a report that is sorted by a purchase order number (ponum) from
most recent to oldest. currently we have ponums from 1-2999, but are
starting to get ponums of 3000+.
this is the formula:
=IIf(CInt(Left([po_number],2))>=30,"19" & [po_number],"20" &
it works to sort from 2999 (most recent) - oldest perfectly, except
now i have to add in the 3000's on top on the report
It may se...Use of array formula
I would like to use the worksheetfunction.linEst function in excel VBA, but
need help on how to identify that the function returns an array rather than a
single value. Thank you.
By selecting several cells first, like 5 rows x 2 columns, for example.
Enter the LINEST function, press ctrl/shift/enter, see different results in
There's not a way to inherently know if a function should be entered as an
array formula without using help (excel's or other folks' or newsgroups, or
trial & error --)
"hsPipe"...Exchange 2003 hotfix...OWA attachment issue
I need to know if the Exchange 2003 SP1 included the hotfix 838236 Post-RTM
Rollup. According to the KB article it is included in the SP1 update, but if
you look at the SP1 bug fixes, it never mentions this issue or the KB
The issue is one others are also having, dealing with attachments not
visible in OWA. From the sounds of the KB article 827637 it is the answer to
this issue for us. But before we install it, we want to verify whether or
not it was actually part of SP1 (which is already installed on our Exchange
Also, if anyone else has this problem and ca...Formula #67
I have a sheet that is used to post daily mileage and expenses for an
employee. He works for a property management company, so he's traveling
during the day from one property to the next, and each property has a
two-digit code assigned to it. Easy enough to sum things up on the daily
log; however, then I want to create a second consolidated sheet showing the
totals for each one of those locations he travels to during the month. So,
for example, I have the following columns in the sheet:
Description of Travel or Expense
Some codes are CC, H...Checkbook issue
I want to know how I can pay a vendor without it affecting the checkbook. I
have an account that simply reduces a liability, and my G/L accounts are
okay, however the amounts appear as o/s cheques in the checkbook? Therefore
the cash account and the checkbook are out of balance. Any way around this?
Using Dymamics GP 9.0
It sounds like you will first have to reconcile your checkbook to bring the
balance to the correct amount. You can always create an adjustment as an
in/out to the cash account having no impact on your GL.
Mariano Gomez, MIS, MCP, PMP
Maximum Globa...How to write a formula
I have a column that lists the names of pieces of equipment. These are
not sorted in any order in this column (the sheet is sorted by the
owner's name in another column). It includes, for example
Further down in this column I have the next available number for A, B &
C. To date I have been manually updating this but I am sure there must
be a way to write a formula that searches the column above and gives me
the next number. Any suggestions?
henry's Prof...Payroll Split Challenge
We have a unique situation for distribution of Payroll and a one benefit
Employee John Doe is full time salaried employee and receives $2000 per pay
period. The employee wages need to be slit 50% to each of the following
Location Dept Acct
01 01 5000 Wages 50% = $1000
02 01 5000 Wages 50% = $1000
They have 1 benefit expense which is based on a percentage of gross – 25%
or $500 for our example. They also want the benefit expense to be split – to
a single or multiple GL code...Formula PDA
Can somebody tell me what if this formula has
alternitives because this doesn't work on my PDA (pocket
Does it support array formula? If so, perhaps
(remove nothere from the email address if mailing direct)
"marciemar" <email@example.com> wrote in message
> Can somebody tell me what if this formula has
> alternitives because this d...formula if with condition in other cell
Is it possible to do an if formula in which the condition applies the
true value if the value is another cell? Example =IF(a1<>"F",1,0). I
mean if <>"F" is in another cell and not in the formula.
Happy New Year to all
Can you try it.
Microsoft MVP Excel
"1234" <firstname.lastname@example.org> wrote in message
> Is it possible to do an if formula in which the condi...Focus issue 02-26-08
I have a listbox that is made visible when
a user clicks on an associated command
button. The listbox includes an option to
"CANCEL" if they decide not to select
one of the options listed. If the user
ignores the "CANCEL" option but instead
clicks somewhere else on the form, I have
coded in the "LostFocus" event a statement
to again to make "False" the listbox's visibility.
I get a runtime error that I can't make the
visibility false while the control has the focus.
How can that be if the "LostFocus" event has
That'...how to make Under invoice formula
Please help me how to make formula if as following case? Thank you very
much in advance to expert helpers.
I want divide a total amount of many diffrent product, in according to
there actual value of product of each product. as following example.
Actual invoice and value & The actual amount
Product quantity unit price total price
Bag 1000 2.1 $2,100
shoes 5000 3.1 $15,500
blanket 8000 4.32 $34,560
jacket 1500 1.8 $2,700
bed sheet 6000 ...IRERR nested formula help please
hi just wondering if someone can help with the following nested ISERR
formula. I want to be able to show in J3 that if the value in G3 is
"0" then show as "-100%" OR if the value is "NULL" then show as "0"
but if the value is >0 then I3/G3*100
it is working to a degree but if value in G3 is NULL then still shows
as -100% I want it to show as 0% if NULL and -100% if 0 - hope it
currently have following in J3
G3 = Order
I3 = Profit
thanks in advance
Depending on how I interpret NULL
=IF(ISBLANK(G3)...no cell results from formula
Often, in Microsoft Excel 2003, when I put a formula in a cell th
results do not show in the cell. For example, today it was a simpl
+b6-b7; Each of these cells had numbers in them - 15,000 and 10,00
respectively, yet the cell showed zero. Automatic calc was on.
tried that same formula in several cells and they all showed zero. Ca
Abram's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1524
View this thread: http://www.excelforum.com/showthread.php?threadid=26871
What formula do you use to change a birthdate to find the
age of person
If you had the birthday in A1
Datedif isn't listed in the function wizard, but can be
found in the help menu.
"Michelle" <email@example.com> wrote in message news:firstname.lastname@example.org...
> What formula do you use to change a birthdate to find the
> age of person
As Mr. McGimpsey just pointed out in response to
another one of my posts...
It's only in the help menu in 2000 (possibly 2003/XP)
For all other XL versi...Font in Formula Bar
I just started using Microsoft 2003.
In Excel, I can't read text that is in the Formula Bar because it is i
a weird small font. I looked everywhere to figure out how to change th
font in the Formula Bar, but i can't find it anywhere.
What font does it use? Is it based on an option in Microsoft or is i
based on something to do with the Desktop settings...i've looke
everywhere for an answer to this.
Thanks for any help.
jdkuhndog's Profile: http://www.excelforum.com...Colour numbers within my formulae
I have been playing around with the '&' function, allowing me to mix text
with calculations and cell references.
I have the following at the moment:
="Of the "&TEXT(J954,"�#,0.00")&" made this year, I have managed to save
Which gives me a cell displaying the following:
Of the �0.00 made this year, I have managed to save 0.0%
What I would like to do is show the numbers (�0.00; 0.0%) in the colour
blue. I don't want to add conditional formatting, just apply a colour so
I am trying to find a way to protect formulas on a spreadsheet whil
still allowing users to put in new data. Is there a way to do this?
am using excel 200
Message posted from http://www.ExcelForum.com
unlock the cells where you want input and then protect the sheet with a
Format | Cells... | Protection tab | Locked = false (not ticked)
"Cherilyn >" <<Cherilyn.email@example.com> wrote in message
> I am trying to find a way to protect formulas on a spreadsheet while
...using ROW in a formula
i have a formula which uses cell "J6" for example...
as i drag down it changes to "J7" "J8" "J9" etc... etc...
however, this formula is linked to a user form, and as the user clicks 'OK' it copy's the formula into my specified cell...
however it copy's the exact contents of the formula, and it does not change the row.. so each formula down the sheet uses "J6".
i want to modify the formula so it uses "J&ROW()" for example, but i do not know how to write this into a formula; i get the #NAME? error.
I entered the catalog item:
<Feature Name="Item" CatalogItemId="Item:MS:sysgen_timesvc_dst" />
and confirmed that the device has the registry key:
HKLM\Software\Microsoft\Clock\AutoDST = 1
But the device still does not automatically adjust for DST. I set the
timezone to Pacific, set the time to 3/14/2010 at 1:59AM, waited for one
minute...and the time changed to 2:00AM instead of 3:00AM.
What am I missing?
which OS version are you using? have you updated it with the relevant qfes
which deal with the changing in DST starting day?
-...Help with some array formulas
Hi there I have this scenario
Tariff A 100 mins
Tariff B 200 mins
Tariff C 50 mins
Tariff A 125 mins
Tariff C 150 mins
Tariff B 175 mins
I want to extract the following information:
Tariff A 0-50 mins 0
51-100 mins 1
101-150 mins 1
Tariff B ....
150-200 mins 2
I know this has to be done by an array... anyone can help
me out please... please also guide me as to how the array
is created (i.e. what each part of the formula represents)
Thanks in advance for your support
how about non-ar...A challenge #2
Hi all you masterminds,
I've spent some time trying to solve this problem. Not exactl
because I need this, but because it's challenging. At least to me i
So, can you make a worksheet function that reverses a string? That'
Message posted from http://www.ExcelForum.com
Yes, something like this. I haven't tried to code but..
Dim variable as string, Result as string
Dim Length as integer, x as integer
Length = len(variable)
Result = Result & mid(variable,x,1)
Message ...help with formula #8
I have a spread sheet like this. A1 is a # for Jan 2004, A2 Feb. 2004 down
to dec in A12, Column B is the same but for 2005. row B13 is a YTD for
2005. I need a formula that will automatically count just the same YTD
period of 2004 in A13. My first thought was just average per month and
multiply it by the number of the month we are in but is was not accurate
enough for me due to slow time vs. the busy times. I have many columns to
do so I need something fast. Is it possible to have a cell set up where I
could just input a number (say 4 for April) and then the formula would only
count...rollout e2k3, any issues with migrating e2k mailboxes
we plan on installing exchange 2003 soon and i have a few questions and
ideas which i welcome any comments.
here is our current setup:
+ small domain (2dcs, 10-15 workstations, windows XP)
+ we are behind a firewall (cisco pix 501, nat/pat).
+ dc1: (win2k server,fmso,gc,dns,dhcp,sql server,ras)
+ dc2: (win2k server,dns,ras,ex2k,IIS,gc)
what i am thinking:
+ new member server (win2k3 server, ex2k3, IIS) <- migrate mailboxes from
+ dc2: (clean install win2k3 server,dc,fmso,gc,dns,dhcp,ras,veritas backup)
+ dc1: (clean install win2k3 server,dc,dns,gc)
i think it is preferable ...2 questions regarding count formula
If I have the following data
A B C
1 Personnel group type p't number
2 Melinda 1 4
3 Sean 1 5
4 Melinda 1 6
5 Sean 2 30
6 Fay 3 8
7 Melinda 1 32
1. How shall I contruct my formula to count the number of data that satisfy
the following criteria:
1. personnel is Melinda
2. group type is 1
3. ...Newbie Q
I am wondering if there is a way to prevent a formula to be updated after an
As an example, let's say that I have a cell on Sheet2 that references
Let's say that I insert a row in Sheet1 above row 4. Automatically my
formula on Sheet2 is updated so that is references Sheet1!A5. Is there a way
to prevent this?
Check out Indirect function
"Atchoum" <NO_goglus_JUNK@videotron.ca> wrote in message
> I am wonderin...