How can I run a spreasheet over range of input data ?
I have set up a work sheet that takes input data (a number) from a cell, goes
through all kinds of calculations based on other cells, and finally puts out
a results in a output cell.
Using this sheet, how can I sweep the input data over a range (min,max] and
record the output data over the sweep?
Thanks for any hint.
...Collections Query Problem
When I try to run a new collections query I now get the message "Unhandled
script exeception: Sql Server Could not find Stored procedure
Does anyone know how I can check or install this stored procedure
...Autosignature not displaying text
I have created a advanced signature within outlook, and
saved this as a web page within the signatures file. When
I apply this to 1 computer the signature details are
picked up. If I recreate the details on another computer
and save in the same way, it does not display the
signature. However if I go back in and edit the signature
the details appear.....................
...Splitting Text in a Cell
Using Excel XP, I have a cell that has full names of people (First & Last
Can I split these two text somehow recognizing the space in between them?
Brig, have a look at data, text to columns, post back if you need any help
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **
"Brig" <firstname.lastname@example.org> wrote in message
news:ed9422dXEH...MS Access Query Case Locking
I have recently been writing my own database's for my company and have
founds these posts to be very helpfull for the problems that i have
hit. Unfortunatly i have hit one problem that i can not find the
I have written a query so that when i click a button it runs and asks
me for the part number of a field. It works fine other than the case.
The tables are from a ODBC databse from another peice of software. All
the information has been written in uppercase and my databse will only
show results if the caps lock is on when inputting the search
criteria. I can not cha...Simple But Stumped
Maybe it should be "Simple AND Stumped"
Attempting a very simple VLOOKUP formula and can't get the result in the
cell with the formula. =VLOOKUP(B4,testrange,2,FALSE).
The Fx or Function Arguments wizard shows me the correct answer but it does
not appear in the cell. Only the formula appears there.
I have used VLOOKUPS successfully before, but stumped here. Any hints or
solutions would be appreciated.
Is the cell formatted as text?
Also, check Tools>Options>View that the Formulas box is not checked.
(remove nothere from the email address if mail...How do I convert monthly data to quarterly data?
I would like to convert high frequency (such as monthly) time series data to
lower frequency (such as quarterly) data.
I'm assuming that you have a column of dates (daily) that you also want
to be able summarize for quarterly purposes in a Pivot Table or some
Probably the best way I have been able to come up with a solution in my
experience is this:
1. Create a helper row that gives you the month with the following
formula (this assumes that you have the daily date in one of the
columns of existing data):
If A1 is the cell at the top of the ...How do I count unique items in a pivottable data field.
I have a pivottable and I am trying to setup a data field that will count
ie. Listing customers as the row and setting up a field to count the number
of sales people that sold to the customer. The base data has multiple rows
identifying the customer, sale id, $ and sales person. If I have 100 rows
with sales person A, B & C each selling multiple orders to several customers
I want the field counter to tell me if 1,2 or 3 sales people sold to each
Not the most elegant answer, but I'd probably create my pivot with Customer
in the row field, sales rep...filtering: sometimes data below blank lines is &isnt available?
When filtering: some of my sheets have blank lines inserted & i am still able
to filter the data below the blank lines. Sometimes I can not. Why?
Depends on if you had all the data selected when you activated the
AutoFilter, or if you just had a single cell selected (in which case, XL
"helps" and picks the complete set of data up till the first blank line).
*Remember to click "yes" if this post helped you!*
> When filtering: some of my sheets have blank lines inserted & i am still able
...How can I make cell data automatically shift up when deleting
A B C
1) Dog Bone Leash
2) Cat Catnip Litter
3) Bird Seed Newspaper
4) Cow Grass Manuer
5) Duck Fish Filter
6) Mouse Cheese Hanta Virus
If I were to delete the information in row 1, I want everything in row two
to shift up automatically. Or... If I were to delete "dog," I would want
everything in Column A, and only Column A, to shift up, therefore removing
any blank cells in the table. How would I do this?
One way ..
Assuming this table is in Sheet1, cols A to C,
data from...How to count last column of data
Using excel VBa, I can count last row of data using lastrow =
But how to use it for column?
"crapit" <email@example.com> a �crit dans le message de news:
> Using excel VBa, I can count last row of data using lastrow =
> Worksheets("Spare2").Cells(Rows.count, 1).End(xlUp).row
> But how to use it for column?
Worksheets("Spare2").Cells(1,...text page format on new email
When I start a new email, my curser drops down the text page about half
way (10 to 15 lines).
It is almost like it is set up for a letter head (I don't have one).
People recieving my emails receive them in this same format. It only
happens on my new emails. All forwards responses, etc are normal.
Go to start -- run -- outlook /safe
and check if this helps ... if yes i can suggest further steps...
<firstname.lastname@example.org> wrote in message
> When I start a new email, my curser drops down the text page abou...Change Event can't reference .text property
I have a procedure to set the RecordSource of a form in the AfterUpdate
event of a ComboBox. Using the Change event of the came Combobox I want the
AfterUpdate event to run when the user hits the delete key to clear the
ComboBox value. Here is the method. I moved the code to a button hoping I
could resolve this.
Private Sub TruckFilter_Change()
If TempVars("aUpdate") = False And [TruckFilter].Text = "" Then
[TruckFilter] = Null
TempVars("aUpdate") = False 'This variable is set to true in the AfterUpdate
ev...Pivot Tables Resizing Columns
I am working with pivot tables. When I introduce another variable into the pivot table or run a show pages to create individual reports based on a field, it seems to change all the fonts and resize all of the columns and the like. Is there any way to lock this down so that the columns and formats are the same for all pages in the workbook
Any help would be most appreciated.
Rightclick the table & choose "Table Options" from the pop-up menu.
Uncheck "AutoFormat Table".
Thanks Andy, I will give it a try
...Data>Filter>Auto Filter in excel 2007?
I have Excel 2003, but oversee a large group that has Excel 2007 (just got it).
I can't find simple instructions on how to Auto Filter. Please help
On the Data tab you can turn AutoFilter on and off
Or use Alt dff to Toggle
Regards Ron de Bruin
"TIJ" <TIJ@discussions.microsoft.com> wrote in message news:DCAD1A38-EC91-4CAC-B090-8355BEED1148@microsoft.com...
>I have Excel 2003, but oversee a large group that has Excel 2007 (just got it).
> I can't find simple ins...VLookup and text strings
Hello, I am using the vlookup function. I have values that have three to nine
text characters followed by numbers. Examples are COM-123, COM-124, ABCD-12,
ABCD-13, etc,. The number of letters may be 2-9. I want to modfiy my lookup
formula so that COM-123 returns the value COM. Or ABCD-13, returns the value
Any help appreciated.
That is not what VLOOKUP is used for. See
This returns the letters: =LEFT(A1,FIND("-",A1)-1)
This returns the number: =--MID(A1,FIND("-",A1)+1,99)
best wish...vlookup and Access Database Table
Can vlookup be used to get a result from an Access Database Table?
The vlookup function itself is an Excel function and as such can only
retrieve data from an excel sheet.
However you may be able to use the import external data menu option in the
Data menu to retrieve your data from the access database, pull it into your
excel workbook and then use the vlookup function. I never tried this myself,
but maybe the idea can be applied.
Jeroen van Nieuwkerk
> Can vlookup be used to get a result from an Access Database Table?
> Thanks....Publisher, why can't I see background and text on screen but can .
In Publisher when making posters etc I cant see the coloured background while
typing yet I can see it in Print Preview. Have tried all the help facilities
but haven't got anywhere.
...How to Transfer data from Excel to SQL SERVER 2000
i want to transfer data from MS Excel Sheets to SQL Server. is there any
Ahmad Jalil Qarshi
The simplest way is to use the DTS Import Wizard.
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
"Ahmad Jalil Qarshi" <ahmaddearNO@SPAMhotmail.com> wrote in message
i want to transfer data from MS Excel Sheets to SQL Server. is there any
Ahmad Jalil Qa...Averaging Time from mulitple tables
im currently making a database that logs time for how long people are waiting
i want to make a query that averages all that infomation i have 1 table that
logs when people arrive and another one when people leave i also need to be
able to use it when there is more data added to it. i know nearly nothing
about queries in databases so can you type in plain english, thanks in
You will not be able to do this unless you have a way to tie your tables
Presumably you have a table of clients, with a ClientID primary key. Or
perhaps you just assign a ticket number when the person...Create a linked table
I need to create a 2nd table that will link to my cust table. The 2nd table
should contain info on freight companys and the cust account numbers to be
used for shipping. How do I format the 2nd table?
I think what you want are the following tables:
<freight company fields>
In TblFreightCompanyCustomerShipping for each freight company you...Data Form Deletions
Annoying for years, but perhaps the is an alternative I haven't discovered;
When finding records using the Data Form based on Criteria, the recordset is
restricted to only those which match the Criteria. However, if you delete
those found records one at a time (using the Form's delete button), once you
delete the last _matching_ record the next records in the list appear in the
form -- even though they _do not_ match the criteria. I realize it is a
matter of user recognition to notice that, but I can't begin to quantify the
number of users who have gon on to delete those add...Problem with query
I am trying to create a pivot table of sorts with the following query:
Select o.Rep, count(o1.Order_Type) as [Quotes], sum(o1.subtotal) as
[Value], count(o2.Order_Type) as [Orders], sum(o2.subtotal) as
[Value], count(o3.Order_Type) as [Invoices], sum(o3.subtotal) as
from OEOrdHead o
left join OEOrdHead as o1 on o1.Rep = o.Rep and o1.Entered_Date =
o.Entered_Date and o1.Order_Type = 'Q'
left join OEOrdHead as o2 on o2.Rep = o.Rep and o2.Entered_Date =
o.Entered_Date and o2.Order_Type = 'O'
left join OEOrdHead as o3 on o3.Rep = o.Rep and o3.Entered_Date =
...How do I merge two cells without deleting data from the cell?
I'm highlighting two cells in the same row, hitting format cells, alignment,
merge cells and I'm getting the error message, "The selection contains
multiple data values. Merging into one cell will keep the upper-left most
data only." I want to be able to make the two seperate cells one without
deleting anything and without having to cut and paste.
You can't, you can concatenate 2 cells into one by using a formula and
however stay away from merging cells, always cause more problems than what
it's worth and layout wise ...Cut and paste specific data
Have been working on a problem which I think I can now simplify
Cell A1 contains the following:
15 Smith Street Blacktown
How can I cut the last item (Suburb) from the cell and paste it into B1?
Many thanks in advance
Try this in B1:
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))
Please keep all correspondence within the NewsGroup, so all may benefit !