copying formulas, but not values
I have a spreadsheet where I need to continue to add new rows. I want
to be able to copy a row with all the formulas intact, but none of the
values that may be in the row being copied. For example, if I have my
cursor on row 1, which may already contain values, I want to be able to
copy Row 1 to Row 2 with the formuals, but not values that may already
be in Row 1. Here is my VBA code. It copies and inserts a new row and
the formulas, but it will also include any values. Any suggestions?
Dim rnRow As Range
Application.ScreenUpdating = True
Set rnRow = ActiveCell
rnR...Linking Combo box to pivot table?
I have a pivot table which has teams on the left, months on the top and a
count of the calls in the data area.
What I would love is that if I could have a combo box on a worksheet which
when I select a team from its list it only shows me that teams data in the
Is this possible please??
Windows 98 + Office Pro 97
You could move Team to the page area of the pivot table.
Select a team, and the pivot table will only show its data.
> Hi All,
> I have a pivot table which has teams on the left, months on the top and a
> c...Migration Wizard
I red this at Microsoft:
"When you use the Migration Wizard to migrate mailboxes, single instance
storage is lost for the migrated data in the target information store"
Is there a way to fix this after the migration in the target information
George, There is not a way to establish Since Instance Storage (SIS) after
the mailboxes have been migrated to the target store.
This posting is provided "AS IS" with no warranties, and confers no rights.
Note: Please do not send email directly to this alias. This a...Changing color of a range of cells dending up on a value in anothr
I need to change the color of a range of cell, depending up on the value in
another cell. I am using Excel 2003. And it allows me to give only 3
conditions when using conditional format. But I have 7 differnt conditions.
Thanks in advance for any help.
Unless you move to XL2007, you will need to use VBA. Here are some sites
that will help
Alternatively, there is an add-in here
Does anyone know what the ESI_Location_XREF table is or what product it is
The ESI_Location_XREF table (ESI Location Cross-Reference, dbo.ESI40300) is
a part of vSync's EDI for Dynamics -- the SOP portion. You can find more
information about vSync's products at http://www.vsync.com
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com
> Does anyone know what the ESI_Location_XREF table is or what p...count of records in a table in external database
I would like to get the record count of a table in an external database
(using the name of the table in the loop of the current database).
What I've tried (doesn't work):
SourceDbCount = DCount("*", "[GOOD_DATA_SAR_Recovery_Tracking_db.mdb].[" &
rs!Name & "]")
The procedure (**** is where the problem line is):
Public Sub AppendTableRecords()
Dim strSelectAllTables As String
strSelectAllTables = "SELECT NAME FROM MSysObjects WHERE Type=1 AND Name not
like 'MSys%';"...# value in spreadsheet
When i copy spreadsheets and build a master worksheet #
value shows up in any cell that doesn't have a number or
zero in it. I know there is a way to fix this but can't
seem to find it---can anyone help?
Does the cell just turn to #value!
Or is it the result of a formula? If it's a formula that's being copied, what's
ron cooley wrote:
> When i copy spreadsheets and build a master worksheet #
> value shows up in any cell that doesn't have a number or
> zero in it. I know there is a way to fix this but can't
> seem to find it-...New user in need of assistance Re: excel pop-ups, forms, +more!!
Hello to all that read this!
I have been asked to complete a mini project and have no idea how to make it
happen in excel!
I require a pop-up to ask me to enter a date for items to be ordered, what
the items are, and also a date for when they are required by.
Once that has been entered, this needs to populate cells in excel.
Working from the dates supplied in those cells, a pop-up is required when it
is time for an item to be ordered to remind a user to do so.
I would also require a pop-up indicating when an item is late for being
ordered i.e it has passed its order-by date. And fin...Table Default Values
Is it possible to set the default value of a field in the table to either a
value entered into a message box or a value entered into another table?
I'm importing data from a text file into an existing table. I have been
manually changing the default value for the report date field each time I
import a new report, but since I won't be doing the importing anymore, would
like to automate or simplify this process.
If you want the current date you can just use =Date(). If some other
date, you can do something like this:
Public Function Lo...Nulls in Access 2007 Fields
Here is my issue:
I have to generate a report which breaks down specific information,
specifically a count derived from when the information was created. I have
accomplished this. My problem is when I run a specific query for one piece
of information it returns what I am looking for. When I run a different
query with the same information looking for the other data I need - it does
not give me anything. Now the problem is there is nothing for the database
to pull up because the answer is zero, but I can not get Access to give me a
zero in the field to show there were no new enrollie...Recording a new loan in my checkbook ledger
This is a very simple question. I've got Microsoft Money Plus for 2008. I
have gotten a new loan and it was deposited into my checking account. The
loan is from the same bank as my checking account. How do I record that in
MS Money? Do I just put in my bank's name, or what? I know this question
is very simplistic, but what I'm concerned about is not confusing myself, at
least, with what I see in MS Money when I review the ledger there.
It's not clear what part of this activity you want to record in your
checkbook ledger. The receipt/deposit of the loan proce...Counting the number of unique different values in a set
Operating System: Mac OS X 10.6 (Snow Leopard)
Hi everyone, I need to count the number of different values in rows of data. <br><br>For example, in 100 rows of data about Gender, there are only two values, F and M, so I would like the function to return 2, even if I select 100 rows all with data. <br><br>Another example: <br>
Given the following list: <br><br>apple <br>
apple <br><br>The function would ret...Matching numeric values in XPath
I use Xpath to retrieve nodes in a document like :
- however this is not very robust as I'm only query on
text. What if width='0.200' instead of width='0.2', then
the above Xpath line wont match the node in question. Is
there a way to query for the value instead of the text?
Jesper Denmark wrote:
> I use Xpath to retrieve nodes in a document like :
> - however this is not v...grab a variable
i have a report that is currently using some code to grab a variable from a
form for display on the report..problem is that the underlying data changes
when there is more than one page to display, but it gets the same data from
the same displayed form.
hope this exaplination makes sense and somebody knows what i need to do to
i fixed by moving the code to the detail section.
...automatically calculate table values via formulas in sheet
Hi, I am stuck with a challenge, maybe someone can help.
I am creating a table with the outcomes of various calculations. I got
a worksheet with 2 variable inputs (A1 and A2) and the outcome
presented in A7. Now I need to create a table with the possible A1
values above and the possible A2 values on the left.
As there are over 100 options for A1 and over 300 for a2 you can
imagine I don't want to type all these values in A1 and A2.
So I am wondering, there must be a function to calculate the outcomes
A7 in this table using the formula's existing in my sheet. Does anybody
know how th...Restart automatic table numbering in Annex
Operating System: Mac OS X 10.6 (Snow Leopard)
I use the automatic numbering of Tables and Figures when creating captions. This generally works ok (although I sometimes get numbering out of sequence when more than one figure on a page), but I've run into a problem. <br><br>I have multiple tables in the main part of the report that I want numbered in the normal way. I also have multiple tables in the annex to the report (which is part of the same document) and I would like the numbering of these tables to start from 1 again (perhaps A1, A2 e...Matching data in two tables
This is probably pretty rudimentary but I'm doing an Update Query in 2007 and
I am having an issue with my criteria.
I have two tables:
tbl_1 contains a column with a serial number (14,000 rows) .
tbl_2 contains a column with user description information that includes the
serial numbers I want to match. (45,000 rows)
tbl_1 field: 673567
tbl_2 field: FTN 673567 333, Doe John, W, X,
Here is my query in SQL:
UPDATE tbl_2, tbl_1
SET tbl_2.[Compare Match] = "Match"
WHERE (((tbl_2.Descriptor) Like '*' & [tbl_1]![Serial Number] & '*'));
I ...Handle missing values
Hi! I have a data set that sometimes contains missing values (#MISSING!). I
want to make graphs of the data and I want the graph to just skip the missing
values. How can I do that? Most thankful for any help!
The cells say "#MISSING!"?? How unfortunate.
Either delete all the #MISSING! entries in the table (to leave blank cells),
replace them with #N/A! (works best for line and XY charts), or build a
table linked to the first with formulas like
where NA() produces #N/A in the cells. Use this second table as the chart
Jon P...How do I reference values from 200 worksheets onto a summary sheet
I'm using Excel 2003. I have a workbook with over 200 worksheets. (Each tab
named for an individual in a group.) I'm trying to set up a summary sheet
which needs total values from each of the individual worksheets. I'm using a
macro which creates a Table of Contents, then sorts the sheets and the list,
providing a link to each sheet. I expect new names to be added so the macro
will have to be rerun occasionally. A separate macro places the sheet name
in cell A1 of each sheet in case that can be of any use. Is there a way to
get the total values from each sheet onto th...Parameter Make Table Queries
I have an excel spreadsheet that establishes a link to an Access 2003
DB. I need to be able to run a pre-saved make-table query with 4
parameters that is stored in my DB, and then use excel to run a pre-
saved crosstab query off of this table and a few other queries. The
results of this crosstab will ultimately be pulled into Excel. I
tried changing the make-table into a select and modifying the cross-
tab, but because of the parameters and complexity of the cross-tab, it
The first problem is:
I "dummed" down my make-table query to eliminate all parameters. Upon
...formula does not recognize value
i have a workbook that calculates exp dates. When the date lands on a
saturday or sunday, I want it to bump out to the following monday.
How can I do this?
Message posted from http://www.ExcelForum.com/
Make your formula consider the WEEKDAY result of its own calculation and add
1 ot 2 according to that.
HTH. Best wishes Harald
"Vato Loco >" <<Vato.Loco.firstname.lastname@example.org> skrev i melding
> i have a workbook that calculates exp dates. When the date lands on a
> saturday or sunday, I want it to bump ...Recordsets-Old and New
I am trying to create two recordsets from a single form, one when the form is
first opened and a second after updates (not entry) are entered. The first
recordset, rsOpen, is defined on the form_open event:
sNewVal = "Select * from tblClasses where Itemid = " & Me.ItemID
Set rsOpen = CurrentDb.OpenRecordset(sNewVal) ''' get orginal values
The second I would like to define when a Submit button is clicked:
Me.Dirty = False
sNewVal = "Select * from tblClasses where Itemid = " & Me.ItemID
Set rsSubmit = CurrentDb.OpenRec...Static Chart using Pivot Table and other data
Using Excel 2000, I am trying to create a Static Chart where the source data is a pivot table and data from a couple of adjacent columns. In the past I have been able to do this. My latest chart is automatically creating a Pivot Chart without the adjacent data. How do I prevent this automation?
There are instructions on Jon Peltier's site, for creating a normal
chart from PivotTable data:
> Using Excel 2000, I am trying to create a Static Chart where the source data is a pivot table and data from a couple of adjacent...Keeping Column Widths the same in a pivot table when pages increase
I've created a pivot table and because of the length of the characters
in the page, the column width keeps changing. How do I keep the column
width the same?
Set the pivot table to preserve formatting:
On the pivot toolbar, choose PivotTable>Table Options
Add a check mark to 'Preserve formatting', click OK
Remove the check mark from AutoFormat Table
> I've created a pivot table and because of the length of the characters
> in the page, the column width keeps changing. How do I keep the column
> width the s...Message POP of new email at web
i m willing to have a setup like ..if i recieve a email
at my address thats email@example.com account of exchange
server......what i want to do is , if i recieve any email
on my accout of exchange firstname.lastname@example.org, i shell get a
messege in my yahoo accout that i have recived an email.
Wating for the valueble advices.