Replace null value with the previous value?

I have a database that was just imported that has approximately 388000 
records.  The problem is that there is information about a person in multiple 
different records but the name did not come across with each record.  (So I 
have 10 records with information for a certain name, but the name only 
appears in field 1 of the first record and not the subsequent 9, etc.)  I 
need to create a query or expression that will fill field 1 with the 
preceding value if it is null.  This way I will have all the information for 
field 1 in a manner that I can link and combine data.  

Simply I need to fill field 1 with the previous records information if it is 
null.  

Any help would be greatly appreciated.  
0
Utf
1/23/2008 3:58:02 PM
access.queries 6343 articles. 1 followers. Follow

8 Replies
2040 Views

Similar Articles

[PageSpeed] 1

Create a Public Function in a standard module with a Static variable that 
will return the value is receives if it is a good value or returns the value 
saved in the Static variable if not:

PublicFunction GetName(varName As Variant) As String
Static strPreviousName As String

    If Not IsNull(varName) Then
        strPreviousName = varName
    End If

    GetName = strPreviousName
End Function

Now, in your query, create a calculated field and pass the Name field to it:

GoodName: GetName([NameField])
-- 
Dave Hargis, Microsoft Access MVP


"Okstate student" wrote:

> I have a database that was just imported that has approximately 388000 
> records.  The problem is that there is information about a person in multiple 
> different records but the name did not come across with each record.  (So I 
> have 10 records with information for a certain name, but the name only 
> appears in field 1 of the first record and not the subsequent 9, etc.)  I 
> need to create a query or expression that will fill field 1 with the 
> preceding value if it is null.  This way I will have all the information for 
> field 1 in a manner that I can link and combine data.  
> 
> Simply I need to fill field 1 with the previous records information if it is 
> null.  
> 
> Any help would be greatly appreciated.  
0
Utf
1/23/2008 4:24:00 PM
That may produces erroneous result if the end user decide to navigates 
backward. The remembered value in the static variable, then, COULD be, in 
theory,  the one from a record coming after the current one actually 
displayed after the "move to previous" record.


Vanderghast, Access MVP


"Klatuu" <Klatuu@discussions.microsoft.com> wrote in message 
news:3F6304C3-47B6-47A9-99C5-9AC487C6910C@microsoft.com...
> Create a Public Function in a standard module with a Static variable that
> will return the value is receives if it is a good value or returns the 
> value
> saved in the Static variable if not:
>
> PublicFunction GetName(varName As Variant) As String
> Static strPreviousName As String
>
>    If Not IsNull(varName) Then
>        strPreviousName = varName
>    End If
>
>    GetName = strPreviousName
> End Function
>
> Now, in your query, create a calculated field and pass the Name field to 
> it:
>
> GoodName: GetName([NameField])
> -- 
> Dave Hargis, Microsoft Access MVP
>
>
> "Okstate student" wrote:
>
>> I have a database that was just imported that has approximately 388000
>> records.  The problem is that there is information about a person in 
>> multiple
>> different records but the name did not come across with each record.  (So 
>> I
>> have 10 records with information for a certain name, but the name only
>> appears in field 1 of the first record and not the subsequent 9, etc.)  I
>> need to create a query or expression that will fill field 1 with the
>> preceding value if it is null.  This way I will have all the information 
>> for
>> field 1 in a manner that I can link and combine data.
>>
>> Simply I need to fill field 1 with the previous records information if it 
>> is
>> null.
>>
>> Any help would be greatly appreciated. 


0
Michel
1/23/2008 6:19:08 PM
Good point, Michel.  Had not considered that.  If the query is forward only 
(ie for a report), it would work, but you are correct, navigating backwards 
would surely return erroneous results.
-- 
Dave Hargis, Microsoft Access MVP


"Michel Walsh" wrote:

> That may produces erroneous result if the end user decide to navigates 
> backward. The remembered value in the static variable, then, COULD be, in 
> theory,  the one from a record coming after the current one actually 
> displayed after the "move to previous" record.
> 
> 
> Vanderghast, Access MVP
> 
> 
> "Klatuu" <Klatuu@discussions.microsoft.com> wrote in message 
> news:3F6304C3-47B6-47A9-99C5-9AC487C6910C@microsoft.com...
> > Create a Public Function in a standard module with a Static variable that
> > will return the value is receives if it is a good value or returns the 
> > value
> > saved in the Static variable if not:
> >
> > PublicFunction GetName(varName As Variant) As String
> > Static strPreviousName As String
> >
> >    If Not IsNull(varName) Then
> >        strPreviousName = varName
> >    End If
> >
> >    GetName = strPreviousName
> > End Function
> >
> > Now, in your query, create a calculated field and pass the Name field to 
> > it:
> >
> > GoodName: GetName([NameField])
> > -- 
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "Okstate student" wrote:
> >
> >> I have a database that was just imported that has approximately 388000
> >> records.  The problem is that there is information about a person in 
> >> multiple
> >> different records but the name did not come across with each record.  (So 
> >> I
> >> have 10 records with information for a certain name, but the name only
> >> appears in field 1 of the first record and not the subsequent 9, etc.)  I
> >> need to create a query or expression that will fill field 1 with the
> >> preceding value if it is null.  This way I will have all the information 
> >> for
> >> field 1 in a manner that I can link and combine data.
> >>
> >> Simply I need to fill field 1 with the previous records information if it 
> >> is
> >> null.
> >>
> >> Any help would be greatly appreciated. 
> 
> 
> 
0
Utf
1/23/2008 7:00:02 PM
I should move my s from produces and navigates onto decide  :-)   ( I would 
like the possibility to edit those messages after they are posted! )

A possible solution, if navigation must be maintained, would be to do an 
update of the table:


UPDATE myTable
SET field1 = Nz( field1, GetName( field1) )


but that CHANGES the data in the table, in a manner that I don't necessary 
buy blindly. On the other hand, once the update is done, using the table 
would produce a 'repeatable' experience, independent of the navigation... 
but that is obvious.


Vanderghast, Access MVP



"Klatuu" <Klatuu@discussions.microsoft.com> wrote in message 
news:F4D52CFA-9635-4532-804B-C277C3D4094B@microsoft.com...
> Good point, Michel.  Had not considered that.  If the query is forward 
> only
> (ie for a report), it would work, but you are correct, navigating 
> backwards
> would surely return erroneous results.
> -- 
> Dave Hargis, Microsoft Access MVP
>
>
> "Michel Walsh" wrote:
>
>> That may produces erroneous result if the end user decide to navigates
>> backward. The remembered value in the static variable, then, COULD be, in
>> theory,  the one from a record coming after the current one actually
>> displayed after the "move to previous" record.
>>
>>
>> Vanderghast, Access MVP
>>
>>
>> "Klatuu" <Klatuu@discussions.microsoft.com> wrote in message
>> news:3F6304C3-47B6-47A9-99C5-9AC487C6910C@microsoft.com...
>> > Create a Public Function in a standard module with a Static variable 
>> > that
>> > will return the value is receives if it is a good value or returns the
>> > value
>> > saved in the Static variable if not:
>> >
>> > PublicFunction GetName(varName As Variant) As String
>> > Static strPreviousName As String
>> >
>> >    If Not IsNull(varName) Then
>> >        strPreviousName = varName
>> >    End If
>> >
>> >    GetName = strPreviousName
>> > End Function
>> >
>> > Now, in your query, create a calculated field and pass the Name field 
>> > to
>> > it:
>> >
>> > GoodName: GetName([NameField])
>> > -- 
>> > Dave Hargis, Microsoft Access MVP
>> >
>> >
>> > "Okstate student" wrote:
>> >
>> >> I have a database that was just imported that has approximately 388000
>> >> records.  The problem is that there is information about a person in
>> >> multiple
>> >> different records but the name did not come across with each record. 
>> >> (So
>> >> I
>> >> have 10 records with information for a certain name, but the name only
>> >> appears in field 1 of the first record and not the subsequent 9, etc.) 
>> >> I
>> >> need to create a query or expression that will fill field 1 with the
>> >> preceding value if it is null.  This way I will have all the 
>> >> information
>> >> for
>> >> field 1 in a manner that I can link and combine data.
>> >>
>> >> Simply I need to fill field 1 with the previous records information if 
>> >> it
>> >> is
>> >> null.
>> >>
>> >> Any help would be greatly appreciated.
>>
>>
>> 


0
Michel
1/23/2008 7:28:51 PM
I don't know about changing the data in the table. What if you get a power 
failure in the middle of that?

If the recordset needs to be navigable, I would consider using a temp table 
and make the query into an append query, delete the records before appending.
-- 
Dave Hargis, Microsoft Access MVP


"Michel Walsh" wrote:

> I should move my s from produces and navigates onto decide  :-)   ( I would 
> like the possibility to edit those messages after they are posted! )
> 
> A possible solution, if navigation must be maintained, would be to do an 
> update of the table:
> 
> 
> UPDATE myTable
> SET field1 = Nz( field1, GetName( field1) )
> 
> 
> but that CHANGES the data in the table, in a manner that I don't necessary 
> buy blindly. On the other hand, once the update is done, using the table 
> would produce a 'repeatable' experience, independent of the navigation... 
> but that is obvious.
> 
> 
> Vanderghast, Access MVP
> 
> 
> 
> "Klatuu" <Klatuu@discussions.microsoft.com> wrote in message 
> news:F4D52CFA-9635-4532-804B-C277C3D4094B@microsoft.com...
> > Good point, Michel.  Had not considered that.  If the query is forward 
> > only
> > (ie for a report), it would work, but you are correct, navigating 
> > backwards
> > would surely return erroneous results.
> > -- 
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "Michel Walsh" wrote:
> >
> >> That may produces erroneous result if the end user decide to navigates
> >> backward. The remembered value in the static variable, then, COULD be, in
> >> theory,  the one from a record coming after the current one actually
> >> displayed after the "move to previous" record.
> >>
> >>
> >> Vanderghast, Access MVP
> >>
> >>
> >> "Klatuu" <Klatuu@discussions.microsoft.com> wrote in message
> >> news:3F6304C3-47B6-47A9-99C5-9AC487C6910C@microsoft.com...
> >> > Create a Public Function in a standard module with a Static variable 
> >> > that
> >> > will return the value is receives if it is a good value or returns the
> >> > value
> >> > saved in the Static variable if not:
> >> >
> >> > PublicFunction GetName(varName As Variant) As String
> >> > Static strPreviousName As String
> >> >
> >> >    If Not IsNull(varName) Then
> >> >        strPreviousName = varName
> >> >    End If
> >> >
> >> >    GetName = strPreviousName
> >> > End Function
> >> >
> >> > Now, in your query, create a calculated field and pass the Name field 
> >> > to
> >> > it:
> >> >
> >> > GoodName: GetName([NameField])
> >> > -- 
> >> > Dave Hargis, Microsoft Access MVP
> >> >
> >> >
> >> > "Okstate student" wrote:
> >> >
> >> >> I have a database that was just imported that has approximately 388000
> >> >> records.  The problem is that there is information about a person in
> >> >> multiple
> >> >> different records but the name did not come across with each record. 
> >> >> (So
> >> >> I
> >> >> have 10 records with information for a certain name, but the name only
> >> >> appears in field 1 of the first record and not the subsequent 9, etc.) 
> >> >> I
> >> >> need to create a query or expression that will fill field 1 with the
> >> >> preceding value if it is null.  This way I will have all the 
> >> >> information
> >> >> for
> >> >> field 1 in a manner that I can link and combine data.
> >> >>
> >> >> Simply I need to fill field 1 with the previous records information if 
> >> >> it
> >> >> is
> >> >> null.
> >> >>
> >> >> Any help would be greatly appreciated.
> >>
> >>
> >> 
> 
> 
> 
0
Utf
1/23/2008 7:45:01 PM
The temp table would be a better alternative, indeed.


Vanderghast, Access MVP


"Klatuu" <Klatuu@discussions.microsoft.com> wrote in message 
news:E53DBECE-DEF2-4BE6-B8AE-C32E8AEDF998@microsoft.com...
>I don't know about changing the data in the table. What if you get a power
> failure in the middle of that?
>
> If the recordset needs to be navigable, I would consider using a temp 
> table
> and make the query into an append query, delete the records before 
> appending.
> -- 
> Dave Hargis, Microsoft Access MVP
>
>
> "Michel Walsh" wrote:
>
>> I should move my s from produces and navigates onto decide  :-)   ( I 
>> would
>> like the possibility to edit those messages after they are posted! )
>>
>> A possible solution, if navigation must be maintained, would be to do an
>> update of the table:
>>
>>
>> UPDATE myTable
>> SET field1 = Nz( field1, GetName( field1) )
>>
>>
>> but that CHANGES the data in the table, in a manner that I don't 
>> necessary
>> buy blindly. On the other hand, once the update is done, using the table
>> would produce a 'repeatable' experience, independent of the navigation...
>> but that is obvious.
>>
>>
>> Vanderghast, Access MVP
>>
>>
>>
>> "Klatuu" <Klatuu@discussions.microsoft.com> wrote in message
>> news:F4D52CFA-9635-4532-804B-C277C3D4094B@microsoft.com...
>> > Good point, Michel.  Had not considered that.  If the query is forward
>> > only
>> > (ie for a report), it would work, but you are correct, navigating
>> > backwards
>> > would surely return erroneous results.
>> > -- 
>> > Dave Hargis, Microsoft Access MVP
>> >
>> >
>> > "Michel Walsh" wrote:
>> >
>> >> That may produces erroneous result if the end user decide to navigates
>> >> backward. The remembered value in the static variable, then, COULD be, 
>> >> in
>> >> theory,  the one from a record coming after the current one actually
>> >> displayed after the "move to previous" record.
>> >>
>> >>
>> >> Vanderghast, Access MVP
>> >>
>> >>
>> >> "Klatuu" <Klatuu@discussions.microsoft.com> wrote in message
>> >> news:3F6304C3-47B6-47A9-99C5-9AC487C6910C@microsoft.com...
>> >> > Create a Public Function in a standard module with a Static variable
>> >> > that
>> >> > will return the value is receives if it is a good value or returns 
>> >> > the
>> >> > value
>> >> > saved in the Static variable if not:
>> >> >
>> >> > PublicFunction GetName(varName As Variant) As String
>> >> > Static strPreviousName As String
>> >> >
>> >> >    If Not IsNull(varName) Then
>> >> >        strPreviousName = varName
>> >> >    End If
>> >> >
>> >> >    GetName = strPreviousName
>> >> > End Function
>> >> >
>> >> > Now, in your query, create a calculated field and pass the Name 
>> >> > field
>> >> > to
>> >> > it:
>> >> >
>> >> > GoodName: GetName([NameField])
>> >> > -- 
>> >> > Dave Hargis, Microsoft Access MVP
>> >> >
>> >> >
>> >> > "Okstate student" wrote:
>> >> >
>> >> >> I have a database that was just imported that has approximately 
>> >> >> 388000
>> >> >> records.  The problem is that there is information about a person 
>> >> >> in
>> >> >> multiple
>> >> >> different records but the name did not come across with each 
>> >> >> record.
>> >> >> (So
>> >> >> I
>> >> >> have 10 records with information for a certain name, but the name 
>> >> >> only
>> >> >> appears in field 1 of the first record and not the subsequent 9, 
>> >> >> etc.)
>> >> >> I
>> >> >> need to create a query or expression that will fill field 1 with 
>> >> >> the
>> >> >> preceding value if it is null.  This way I will have all the
>> >> >> information
>> >> >> for
>> >> >> field 1 in a manner that I can link and combine data.
>> >> >>
>> >> >> Simply I need to fill field 1 with the previous records information 
>> >> >> if
>> >> >> it
>> >> >> is
>> >> >> null.
>> >> >>
>> >> >> Any help would be greatly appreciated.
>> >>
>> >>
>> >>
>>
>>
>> 


0
Michel
1/23/2008 9:31:47 PM
I'm working on the same problem, but when I type the below code into the 
Access module the first line is in RED.  I keep getting an error on the "As" 
before Variant.  

"Klatuu" wrote:

> Create a Public Function in a standard module with a Static variable that 
> will return the value is receives if it is a good value or returns the value 
> saved in the Static variable if not:
> 
> PublicFunction GetName(varName As Variant) As String
> Static strPreviousName As String
> 
>     If Not IsNull(varName) Then
>         strPreviousName = varName
>     End If
> 
>     GetName = strPreviousName
> End Function
> 
> Now, in your query, create a calculated field and pass the Name field to it:
> 
> GoodName: GetName([NameField])
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "Okstate student" wrote:
> 
> > I have a database that was just imported that has approximately 388000 
> > records.  The problem is that there is information about a person in multiple 
> > different records but the name did not come across with each record.  (So I 
> > have 10 records with information for a certain name, but the name only 
> > appears in field 1 of the first record and not the subsequent 9, etc.)  I 
> > need to create a query or expression that will fill field 1 with the 
> > preceding value if it is null.  This way I will have all the information for 
> > field 1 in a manner that I can link and combine data.  
> > 
> > Simply I need to fill field 1 with the previous records information if it is 
> > null.  
> > 
> > Any help would be greatly appreciated.  
0
Utf
2/7/2008 11:51:00 PM
On Thu, 7 Feb 2008 15:51:00 -0800, Two Thousand 2000 <Two Thousand
2000@discussions.microsoft.com> wrote:

>I'm working on the same problem, but when I type the below code into the 
>Access module the first line is in RED.  I keep getting an error on the "As" 
>before Variant.  

If it's actually as posted:

PublicFunction GetName(varName As Variant) As String

you need a blank between Public and Function.

             John W. Vinson [MVP]
0
John
2/8/2008 12:01:28 AM
Reply:

Similar Artilces:

If value in cell Then perform function #2
Hi Guys Many thanks for the solutions - they both work! Emm -- emm808 ----------------------------------------------------------------------- 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 a workbook. 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
Hi, 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
Hi All, I have a form with an email address field in which the domain is often the same for each new record. For example, test@domain.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
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hello, <br> 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 &amp; time, and use subtotals etc. on change of date &amp; 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" values. TIA Zorro 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 use =IF(A1=17,"Yes","") you don't get FALSe. Similarly with other formulae. -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "Zorro" <zorro@nexia99.freeserve.co.uk> wrote in message news:W9COa.18225$pd.8840@news-binary.blueyonder.co.uk... > I know h...

Counting cells which have some numerical value...
Gurus, 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 non-blank cells? -- Spin =count() will count all the cells with numbers =counta() will count all the cells with anything--including formulas that evaluate to "" =countblank() will count the number of blank cells--including the formulas that evaluate to "" Spin wrote: > > Gurus, > > 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. Hi Kim use =LARGE(A1:A100,2) -- Cheers JulieD 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 news:1614CDDD-AADF-4EE1-B8CB-A752A1975DC8@microsoft.com... > 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...

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. Thanks! =countif(a4:a12,1)/count(a4:a12) Format as percent Regards, Fred "clbritt76" <clbritt76@discussions.microsoft.com> wrote in message news:28AFAD8A-88BC-4151-AE7F-5463AA6E9B68@microsoft.com... > Ok so I have a column with values in it. Say A4:A12, the values entered > will > 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. "ashwilliams" wrote: > 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 part numbers and their details--the formula used in the previous quote, for example. 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). Hi Michael, 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: ANTALL.HVIS(I:I;"16") (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 requirements.) How do I write this formula? (Please feel free to write the english formula, I will find out how to write it in norwegian.) Regards Johannes Hei Johannes 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 ?? Thanks, Howard 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 rented? "Y" 4. Have you made collection calls to clients that owe money? "N" 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: PROCEDURE [dbo].[new_tbox] -- Add the parameters for the stored procedure here @slide_id int, @shape_id int, @cnt_typ_id int AS BEGIN 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 RETURN @tbox_id END That returns the variable. This works in SQL Server, and I get the return value My VBA code from my ADP is as such: With cmd .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 values. 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 ? Thanks... 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 50 cents). 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 -- si ----------------------------------------------------------------------- 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 Value ($). I believe t...

Using SUM to calculate values of cells containing formulae
Hi 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
hi, 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! =index(1:1,match(max(2:2),2:2,0)) Will find the first largest value in row 2 and return the header from row 1. Michael wrote: > > hi, > 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! -- Dave Peterson "Michael" wrote: >...

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...