If value in cell Then perform function #2
Many thanks for the solutions - they both work!
emm8080's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1185
View this thread: http://www.excelforum.com/showthread.php?threadid=27483
...Creating graph from values in multiple tabs within workbook
I am trying to produce a simple line graph from values in worksheets within
Worksheet 01/01/07 Share price A �5
Worksheet 01/01/07 Share price B �2
Worksheet 01/02/07 share price A �4
Worksheet 01/02/07 share price B �2.50
I thought that you could hold the ctrl key down and select the data from
each cell in the worksheet but I have obviously got that wrong.
As you can see from above I am looking to initially create 2 lines over a
say a year to monitor the values of a number of different share prices.
Can someone point me in the direction of an idiot guide of how to do this....Formula in Excel 07 to get the formula used in one cell in another cell, showing VALUES, not cells references
I'm gonna illustrate this by an example as I think it is the easiest
way to explain what I need:
A1 = 10
A2 = 5
A3 = 2 because A3 = A1/A2
Now, what I want is to be able to in cell B3 to write some sort of
formula e.g. =GetFormula(A3) that will give me:
=10/5 and NOT =A1/A2 , NOR 2
Does anyone know what I can do?
I don't know how to work with VBA or anything, but I know how to copy-
paste if you know the codes.
I previously got the following answer:
Here is a custom function that will do what you want.
To install the custom function:
Alt+F11 to ope...Invalid Value types in Lookup field
I am new at Access and don't really know what I am doing. I created two
tables... 1. contact information 2. subcontractors used at job sites. I
created a lookup field for the subcontractors so when I create reports or
queries I can connect the subcontractors with their contact information. I
set the field to text, but everytime I try to put the cursor in the field it
comes up with an error message saying that the value isn't valid for the
field. How do I get it to stop doing this?
Your first problem was defining a "lookup field" -- I'm sure Microsoft was
tryi...Autocomplete email domain from previous record
I have a form with an email address field in which the domain is often the
same for each new record. For example, email@example.com... the "@domain.com"
portion is often the same. What I would like to do is somehow have some code
that autocompletes just the domain portion from the previous record when the
user types the "@" symbol. So, they can then type in "test2" and as soon as
they hit the @ symbol it would complete the field with the domain portion
from the previous record.
Does anyone have a genius way to do this? :-)
Thank you for your help!...DATEVALUE("10/30/2009") Fails with #VALUE! Error
Operating System: Mac OS X 10.6 (Snow Leopard)
I need to manipulate some date/time strings that I'm importing from an external source. The strings look like this: <br><br>10/30/2009 3:19:08 PM <br><br>I need to separate date & time, and use subtotals etc. on change of date & change of hour. <br><br>I thought the best thing would be to separately extract the date and time, then use DATEVALUE() on the date string so I can format it as yyyy-mm-dd and do calculations. <br><br>...Hiding "FALSE" values
I know how to hide zero values, but can anyone tell me how to "FALSE"
Trap the condition that creates it. For instance, in the formula
=IF(A1=17,"Yes") will return Yes if A1 is 17, but FALSE otherwise. SO if you
you don't get FALSe.
Similarly with other formulae.
... looking out across Poole Harbour to the Purbecks
"Zorro" <firstname.lastname@example.org> wrote in message
> I know h...Counting cells which have some numerical value...
In Excel, how do I "count" a value of a cell as "1" if it has a numerical
value inside (for example, 2.50), and count it as "0" if the cell is blank,
such that at the bottom of the column I will have a total count of all
will count all the cells with numbers
will count all the cells with anything--including formulas that evaluate to ""
will count the number of blank cells--including the formulas that evaluate to ""
> In Excel,...Second largest value
How do I find the second largest value in a range of data? I have a list of
daily meter readings and need to subtract the second largest from the largest
to determine the daily usage.
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Kim Young" <Kim Young@discussions.microsoft.com> wrote in message
> How do I find the second largest value in a range of data? I have a list
> daily meter readings and need to subtract the...Calculating a value for a new record
I have a simple database which records a list of different reports my team
produces. Each report has a unique hopefully sequential number "Report No"
which is different to the "Record ID" (Autonumber).
I have a form which I am using to add new reports to the underlying table.
As I add a new report record I want to look up the highest existing Report No
value in the table and auto populate the New Report Form Report No Control
with that number +1. i.e. if the last report added was numbered 255 the next
one shoiuld be 256. I have tried using Max ([Current ...Calculating percent of times a value is in a column and total #
Ok so I have a column with values in it. Say A4:A12, the values entered will
always be 1, 2, 3, or 4. I need a formula that will calculate the % that say
a 1 appears in that range and I need a formula that will calculate the total
number of 1s in that range.
Format as percent
"clbritt76" <email@example.com> wrote in message
> Ok so I have a column with values in it. Say A4:A12, the values entered
> alwa...Does any Chart Y-axis with 2 Values on X-axis(A train's tracking)
I need to draw a chart, where Y-axis represent Railway
Stations and X-axis represent times of a day.
I need to show simple line chart Stating From Station A,
0700hr(Train Dep Time);Station B 0800hr(Train arrive
time);Station B 0800hr(Train Dep Time);Station C 0830hr
(Train Arrive Time);Station C 0835hr(Train Dep Time);and
finally Station D 0900hr(Train Arrive Time).
You can see that in some station, the train does not stop
and so Arrive and Dep time are the same. But in some
stations, the train stops and therefore the line chart
will show as parallel to X axis during its stops at th...Can't use new banks previously not in 2004 for auto downloads #2
I use National City and they are now listed in the banks for online services.
I try to setup online services for my account and when I select them out of
the list it starts downloading something but ultimately says account setup or
online setup is unavailable and to try again later. Is this the Yodlee
problem people keep speaking about?
Yeah, I think it is. I have their credit card services and am having the
same problem. It did download initially but there were some problems with it
so I think they have completely shut it down for now.
> I use Na...How to select combobx value with vba?
For an electrical part number quoting application developed in
Excel 2000, I have a a userform (userform4) with a combobox (cboQpn)
to hold the quoted part number, and another combobox (cboFormula)
prepopulated with a named range (Partnum)
The user types in a part number in cboQpn and then chooses
the correct formula from cboFormula.
Now in access I have a table (tblDetail) that holds previously quoted
and their details--the formula used in the previous quote, for
The formula information is in column 5 in tblDetail
I want to emulate the manual selecting o...SEARCH AND REPLACE
Historically, when I select cells and do a "replace all" (via Ctrl+H) Excel
reports the number of replacements it is making (same as with Word). I have
just started at a new location-office. Excel does not report this. It
replaces without asking for confirmation. I cannot find settin or info that
will cause it to report as discribed above.
Help, please (solution, info, etc).
When you use Ctrl+H to make replacements and fill in the
from and to fields. Do you see buttons on the right for
Find next, Close, Replace, Replace All
sounds like you want to u...Checking on two values
To count the numbers of rows that include the number "16" in column "I", I
use the following formula:
(I think this is called COUNT.IF in english)
I want to count the numbers of rows that include both this and the letter
"A" in column "B". (Meaning not counting rows which just fulfill one of these
How do I write this formula? (Please feel free to write the english formula,
I will find out how to write it in norwegian.)
COUNTIF takes only one criteria. You can do this wit...Total Account Value
I am a first time user of MM 2007 and dowloaded my account info (for this
calendar year) form my broker. Total Account Value seems to reflect the total
value of the account minus the cost basis. This seems a bit strange, an
account with a Market Value of $5706.5, a Cash basis of $5299.84 is isted as
having a Total Account Value of $406.66.
If I sold these shares, they would be worth $5706.50 - commission. So why is
the Total Account Value $406.66 ??
In microsoft.public.money, HowardC wrote:
>I am a first time user of MM 2007 and dowloaded my account info (for this
&...Equations with numbers and letter values
I am creating a performance chart for employees to fill
out. Below is an example of a set of questions they need
to complete, with a possible answer to each. My
spreadsheet is being designed to calculate a points system
on multiple categories
1. How many appointments did you have? "2"
2. How many new appointments did you set? "1"
3. Did you follow up with active client groups, not yet
4. Have you made collection calls to clients that owe
5. Have you reviewed and replied to all your emails? "Y"
I need...Getting a Return Value from a Stored Procedure in VBA
Hi all, I have a Stored procedure, here:
-- Add the parameters for the stored procedure here
DECLARE @cnt_id int, @tbox_id int
INSERT cnt_info VALUES(@slide_id, @shape_id, @cnt_typ_id)
SET @cnt_id = @@IDENTITY
INSERT tbox_cnt VALUES(@cnt_id)
SET @tbox_id = @@IDENTITY
That returns the variable. This works in SQL Server, and I get the
My VBA code from my ADP is as such:
.ActiveConnection = ConnStr
.CommandText = "...How do I export/save a shhet where some of the cells have multiple line values
At the office I wrote a perl script to proces the data exported/saved
from an excel spreadsheet. I noticed that the perl script processed
more lines than existed rows in the spreadsheet. Upon closer
examination I noticed that some of the cells have multiple line
Is there any way I can control the action of the export/save so that
the multiple line val;ues are combined into a single line ?
The cells with multiple lines would have linefeeds in them.
This would be the character 0010 which is entered using Alt + Enter
Select all cells before export/save and Edit>Replace...how do i separate cell value in four different cells? #2
Thanks for the tip but i think i should explain some more. If th
amount is 250.50 (USD) it should be displayed in the last 5 cell
excluding the dot(assuming that amount is no more than five digits an
This shoud be a simple accounting operation to transfer a single cel
ammount in to assignet cells to the right.
If you have ideas... help pleas
sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262
View this thread: http://www.excelforum.com/showthread.php?threadid=26585
...Money 2006 Portfolio two different values for Annualized Return
I have the test drive version of Money 2006. In the portfolio manager, I
have the Annualize Return column visible. This provides values for each
holding, each account, and the entire portfolio.
At the bottom of the portfolio manager, I have selected Annualized Return as
one of the three to display. My totals line my portfolio displays an
annualized return of 7.5%, on the bottom, it displays 10%. I bounced around
changing the bottom line to display values such Cost Basis, Market Value,
and Total Returns and these all match the line labeled as Total Portfolio
I believe t...Using SUM to calculate values of cells containing formulae
I'm new to this forum so "Hello" to everybody. I've had a look aroun
the forum but I can't seem to find the answer to my problem. I'm sur
it must be possible but I've tried everything I know short of writing
Macro. I hope one of you wonderful people will be able to help, I can'
believe I am the first person to come up against this problem
Here's the problem (somewhat simplified) ;
I have a column of cells containing the following formulae
Cell 1 (K2) =VLOOKUP(I2,$A2:$E34,5)
Cell 2 (K3) =VLOOKUP(I3,$A35:$E103,5)
Cell 3 (K4) =VLOOKUP(I4,$A104:$E176,5...cell in row has highest value and rtrns col hdr
can anyone advise as to which formula would be able to find the highest
("=MAX(A$:T$)")value in a row of cells and once identified return the column
header? thanks for any help!
Will find the first largest value in row 2 and return the header from row 1.
> can anyone advise as to which formula would be able to find the highest
> ("=MAX(A$:T$)")value in a row of cells and once identified return the column
> header? thanks for any help!
>...Auto replace, can it easy be done?
I have a series of tables where by I put in each tenant's name (column
a) and their lease end date (column b) and add any special comments or
irregularities in the leases in column c. Right now we either type in
each individual comment or use number codes, or essentially footnotes
where we define them somewhere else.
Is there any easy way to assign a number to a comment so that if you
enter 1 it gives "comment A", 2 "comment B" and so on and so forth. It
would be great if you could add multiple comments and not have the
formatting go all wonky but I'd be able to...