How do I sort data in excel which was created by formulas?
I have a list of information that is created using formulas. I want the
information to update whenever I make changes in the excel spreadsheet, so I
want to keep the formulas (I do not want to do a paste special and choose
"values"). I want to be able to sort the information in an ascending order.
When I do this all of the formulas change. Is there a way to keep the
formulas constant so that it only refrences those cells chosen before a sort?
> I have a list of information that ...Formula Question #15
I have a list of seven people (golfers) who will have scores. For th
team scoring I would like the top-five of those seven to b
calculated and omit the bottom two scores. I would also like for
specific one of those golfers to count regardless of their score. I
there a formula that would do this
Let me know if I need to explain this better
A quick recap..
-I have seven golfer
-One of those golfers (preselected) counts in the team score no matte
-The two worst scores (outside of the one that counts no matter what
must be ommitted
Thanks for the help
Assume the scores are in B2:B8 ...Length & Distance formula
I need a formula that will count based on the length of a vehicle, and a
timed distance (counted in seconds) the vehicle is behind another vehicle.
Here’s the breakdown;
Satisfactory result for 40’ => 6 sec, 60' = >8 sec
Less than Satisfactory 40' => 3 sec & <6 sec, & 60'= > 4 sec & <8 sec
NOT Satisfactory 40' = 3 sec or less & 60' = 4 sec or less
40’ vehicles can be separated from the 60’ by the vehicle numbers.
All 40’ vehicle numbers will start with 24, 90, and 91.
So I have 3 columns to place a count. Satisfactory in Q3...Is it normal for a pst file to expand when imported into an ost fi
I had a 2+GB pst file that when imported into my exchange cached environment
grew to approximately 20GB. Of course this wreaked havoc on the user's
mailbox, and I am now having synchronization and all kind of wierd problems.
I guess my first question is, is it normal for a pst file to grow on import?
Also, on that import, is it possible for the original pst file to become
corrupt, or smaller after the import. I fear that I may have lost some
messages in the import
BookerW <BookerW@discussions.microsoft.com> typ...column formula
First time post here. Basically i have a database with names of peopl
in, say column A, B, and C (I am dumbing this down a little fo
simplicity's sake). Basically I want to create a new column tha
outputs something like "NameA, NameB, NameC". However in many cases
for example, a NameC won't exist, and I then want it to read "NameA
NameB". See what I am getting at? I only want it to display (an
format accordingly) if the corresponding name cell isnt blank.
Message posted from http://www.ExcelForum.com
not 100% sure...Formula #29
What is a formula?
Microsoft Excel MVP
"remy5323" <email@example.com> wrote in message
> What is a formula?
This link might be helpful to you...
Rick (MVP - Excel)
"remy5323" <firstname.lastname@example.org> wrote in message
> What is a formula?
...Formula or not?
How can one determine if a cells contais a value or a formula? I'm trying to
use Conditional Formatting to color code the cells based on their content
(formula or value).
Does anyone know?
Here's a nifty macro that some kind folks in the group gave me some time
ago........it works super.
(watch out for email word-wrap)
Vaya con Dios,
Public Sub IDFormulae()
Dim response As Variant
response = Application.InputBox("Identify Cells containing formulas with:" &
vbNewLine & "1 - Red Border" & vbTab &am...vlookup or match formula help
i am trying to match, or line up identical ID#'s in wksheet 1 - column A
(which has 305 lines/rows) to ID#'s in wksheet 2 - column A (which has 9500
lines/rows). (also, each wksheet has a column B with misc $ amts). thanks
In C2, copied down: =VLOOKUP(A2,Sheet2!A:B,2,0)
will return the amounts from Sheet2's col B
Downloads:17,400 Files:358 Subscribers:55
> i am trying to match, or line up identical ID#'s in wksheet 1 - column A
> (which has 305 lines/rows) t...need to create a formula
I need to create a formula that calculates the number of
times a name shows in column A and has a date in column C
and a blank in column D. I am woring on a single
worksheet. I have tried using this formula =COUNTA
(($A$2:$A$18="Roy"),($C$2:$C$18>0),($D$2:$D$18=" ")) , all
I get is a 0 when I can see more than 1. If I change to
COUNTIF, I get an error.
first put this function in a REGULAR (not sheet or ThisWorkbook) module
Function IsDate(Var As Variant) As Boolean'Chip Pearson
IsDate = VBA.IsDate(Var)
=sumproduct((rngA="myn...Expanding Folders #2
I have a Hotmail account that I read with Outlook 2003, but I can't
figure out how to keep the "Hotmail" folder expanded so I can see the
"inbox", "Deleted items," "junk email" and "sent items" sub-folders.
Thanks in advance for your assistance
tubbfan <email@example.com> wrote:
> I have a Hotmail account that I read with Outlook 2003, but I can't
> figure out how to keep the "Hotmail" folder expanded so I can see the
> "inbox", "Deleted items," "junk email" and "sent items...Creating a Bar Graph with a reference point
I would like to create a Bar Chart/Graph (horizontal) that would have
two sets of values and one category group. I would like to display
this as followed: value set 1 to be a bar and the value set 2 to be a
reference dot for value set 1. Say my Data looks like the following:
CatergoryGroup Val1 Val2
Month1 1 4
Month2 2 3
Month3 7 5
Again, I want Val1 to be bars. Val 2 I want to be dots on the same
I looked at the Column Charts and there is the "Plot Data as Li...apply the formula sign
I=B4ve imported data from other application and some cells have some
formulas like 10*12 but without the =3D sign. How can I apply the =3D sign
to all the cells quickly without going one by one so that it
calculates the formula. Example, =3D10*12, should be 120.
Copy this UDF to a general module in your workbook.
Function EvalCell(RefCell As String)
EvalCell = Evaluate(RefCell)
In an adjacent cell enter =EvalCell(cellref)
Gord Dibben MS Excel MVP
On Thu, 28 Jan 2010 07:12:14 -0800 (PST), canvas <spyele123@g...repeating a formula throughout a worksheet
I havent used excell for some years and have set up a basic worksheet, but
am wanting to repeat a formula each third row which is row 1 balance plus row
2 minus row 3 equals balance as displayed in row 4 and then repeat this
thoughtou the worksheet for several columns ( if that makes sense- im sure it
can be done not sure how thou and have looked through help topics everywhere
so if any one can help in basic language it would be great thanks.
If its single row then click your cell that contains the formula once
then hold shift and click the further most cell that you want the
f...Quick Formula Question
Dear Excel 2003 users,
I have a user in the field that likes to start formulas with a plus
sign (becasue she uses a numeric keypad without an equal sign).
Sometimes the formulas work, sometimes they do not. Here is an
she enters +150/2
she could get:
=150/2 or 75
Why does Excel sometimes run an immediate result on the entry and
sometimes convert it to a proper formula (which is the desired
Any chance she's hitting F9 after entering the formula (but before the enter
Or is there any chance she has a helpful macro that's jumping in where i...Expand Tag Along
how can i expand the tag along item ,that the cashier can select the tagged
item from a list of items (choose a gift for customer sold free with an item).
...how do I get more than 19 bars on a bar chart
Hi! I have three columns of data each containing 84 entries. These are
grouped in threes, and I wish to create a bar chart to show the comparison
between groups A, B and C.
I use the chart wizard, create a bar chart, then create the series. Then
for each series I go down the column with CTRL held down, selecting each
third entry. This is fine for 19 entries, but when I click to select entry
no. 20, the series clears itself. If I highlight 20 entries and then click
on Insert/chart, I get the message 'Series formula is too long'.
Can anyone explain please how to over come t...Formula calculation
I have a simple amount due to contractors and a schedule of payments when
they reach certain points in the job (ie 25% 50% 75% 100%). The calculations
work out fien if everything goes according to schedule. But, if we amend the
contract and increase the contract price, say, after the 50% payment is
made....when I change the original amount, the two pyments already made will
now change to their respective percentage of the NEW total amount. I don't
want my data entry person to have to manually figure the payments. Is there
a simple fix for this issue?
The following equation does what most of what I want. All values are times with the format h:mm.
=IF(OR(C2="YES",C2=""),IF(AND(E2>=D2,G2>=F2,I2>=H2),(E2-D2)+(G2-F2)+(I2-H2),"Time missing or mistyped"),"OFF").
An example of what it doesn't do is allow me to see a total for rows where G2<F2 and/or I2<H2 when E2>=D2.
I'd like to make it flexible enough to SUM only the combinations that return a positive value while ignoring the others. This would allow me to have a running total instead of the text message "Time missing or m...Oulook, Expand/Collapse How to setup from Expand all the time.
It drive me nut to reset this each time I need to look at my contact.
Need help on this on.
You can set that in the view settings. While in the view in question go to
the View menu > Current View > Customize Current View > Group By dialog and
set the expand/collapse setting at the bottom
"Ernie" <Ernie@discussions.microsoft.com> wrote in message
> It drive me nut to reset this each time I need to look at my contact.
> Need help on this on.
Operating System: Mac OS X 10.6 (Snow Leopard)
I have a column of cells that contain yes or no answers. What I need to do is write a formula that will result in a summary cell that answers "yes" if all of the above cells say yes or answers "no" if any of the cells above say no.
On 3/3/10 1:04 PM, firstname.lastname@example.org wrote:
> Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor:
> Intel I have a column of cells that contain yes or no answers. What I
> need to do is write a formula that w...Line & Bar on Same Chart?
I have two sets of data - one set I would like to plot as a line plot,
the other as a bar plot. Is there a way to put both of these on the
I am interested in knowing how to do this too. Is the line called a trend
line? Help in Excel says to add a trendline, you should choose "Add trend
line" from the Chart menu. There is chart tool bar and chart options, but I
don't know where the CHART MENU is?
> I have two sets of data - one set I would like to plot as a line plot,
> the other as a bar plot. Is there a way to put both of the...Help with populating cells using formulas or macros
I have a problem that I've spent hours working on but even Microsoft
customer support has not been any help.
I'm trying to work with a large data file where the data is grouped
into two types. Let's call them A & B. For each day of data, I have two
data points, A & B. These are entered next to each other in the same
row. It looks something like this:
A1 B1 A2 B2 A3 B3 ...
That is, for Day 1, I have data point A and data point B.
I want to be able to easily group all the "A" data and all the "B" data
That is, in a separate part of ...Automatically Expanding the GL Transaction Entry Grid
Does anybody know the code to automatically expand the grid on the GL
Transaction Entry window? I tried to use the same code (adapted) that I used
on the payables transaction distribution window, namely:
scrollexpandswitch.value = 1
But I got an error when it ran.
Charles Allen, MVP
Using VBA, that is.
Charles Allen, MVP
"Charles Allen" wrote:
> Does anybody know the code to automatically expand the grid on the GL
> Transaction Entry window? I tried to use the same code (adapted) that I used
> on the payables transaction distribution window, namely:
>...Referencing a formula (as text)
Hi, can anyone help with the following?
I want to pick up a formula (written as text) from one cell so it can be
used in another function? This will then be applied to a range of cells by
For example if I write a function as VLOOKUP(X,Table,3,false) without the =
sign and therefore store it in a cell as text, I want to be able to
reference that cell and apply the function.
Is this possible?
You'd need a macro to do that. If that formula were in D3, for example, this
line of VBA would do it:
Answer = evaluate("=" & [d3])
"Nels...Remove bars (left hand pane)
is it possible to remove the Marketing or Service bars (in the left hand
One of our clients would like their users to be focused on just areas that
they will use, for example, the Sales users (by Role) will not need the
Is it possible to do this?
Yes, simply click on Workplace bar, then just above click on "personalize
Workplace" then tick on or off what you need
"GP" <GP@discussions.microsoft.com> wrote in message
> is it possible to remove the Marke...