How can I asign a number value to a text line in Excel?
tI have a Backgammon Club with Internal Club Rankings that are in Text. I
want my members to report their wins - 1st, 2nd, and 3rd place - in
tournaments to my web site . I would like them to imput: their nickname, date
of tournament, tournament Room #, tournament points played for, and host of
the tournament. I would further like the calculation to display any change in
Ranking as a text cell. Additionally, I want to show for the member, how many
more points are needed to get to the next levle and what that (Text)level
is. If there is a "what if" for this please advise.
Ri...Control can't be edited; it's bound to a replication system column 'TableName'.
I have an Access 2007 accdb database. It hast a table in it with field
names TableName and FieldName and a bunch of others. This table has had a
small number of records added to it (37) over the life of the database.
Suddenly, the two fields TableName and FieldName cannot be edited. That is
even if the table is open as a table and you try to type a new record. When
you do that an error message is briefly displayed in the left hand end of
the Access main window's status bar. For example if you try to input
anything into the TableName field you will be blocked and you will briefl...Combo Box from data in another tab
Is it possible to create a combo box from data that's in another tab?
I have a combo box that's from data in hidden columns. But now I need to move
all that hidden data to another tab in the same file. But when I go into
properties and go to the ListFillRange option, it won't let me enter another
Message posted via OfficeKB.com
I have called data from another tab with a combo box by doing th
First - name the range of the data on the other tab. This can be don
with the shortcut on the toolbar (l...Preventing Column Cell Values from Printing
I have a summary worksheet with several columns of information.
There is one particular column (E25:F39) where I don't always want to print
the values. Is there some simple way to toggle this column's values on and
off just before hitting the print command?
I was thinking of using an adjacent tick box outside the print area, which
when ticked would print the column values, and vice-versa.
I'm looking for a formula that would examine the tick box, and if the tick
(letter 'a' in Marlett font) is not present (i.e. cell value = ""), then the
values in column (E25:...Adding a new data series to an existing chart
I have a stacked bar chart that shows monthly sales by customer type. I want to add an additional data series to show the monthly forecast, to be displayed as a point or line against the existing stacked-bar data. Any ideas?
Select the data you want to plot. Drag onto the chart. XL will pop up
with a dialog box asking for some information. Provide it and you will
be all set.
MS MVP Excel 2000-2004
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office
In article <1418C2D7-4DF6-4945-A08A...Link subform ID control
Link subform ID control
Using Access =9203=85
I have a form with one subform. The subform holds an ID field/control
(called synopsisID) and a textbox for narrative entries (memo datatype
The main form and subform are properly linked in the Child/Master
field setting (synopsisID to synopsisID).
My question/issue is how to update the synopsisID textbox on the main
form when it=92s assigned (by autonum) in the subform.
I tried the after update event of the synopsisID on the subform, but
couldn=92t get it to populate the main form. Maybe there=92s a better
...How do I change the value in cell based on a future date
I would like certain cells to be cleared after a certain date
Put this in the ThisWorkbook code module (Right-click the workbook title
bar and choose View Code):
Public Sub Workbook_Open()
If Date > #12/15/2004# Then _
Note that this won't work if the user opens your workbook with macros
Note also that there's no way to prevent a user from accessing your data
- i.e., they can set their clock back and open the file. You can make
things more difficu...How to find a value with multi-column, multi-record list
I have a spreadsheet with $costs arranged according to values in both rows
How do I create drop down lists for both the rows and columns and then
return the cell value?
eg if I have 5 rows (2-6) and 6 columns (B-G) with costs arranged in each
cell within this group how do I create drop downs for each selection, 2-6 and
B-G, and return the value to a selected cell.
In a typical table of this nature there would be descriptive row and column
headers. To do a lookup on this table you would then look for the
intersection of a specific row header and a specific column ...Excel 2000
*First off:* I have created a spreadsheet that has a dynamic range an
*Next:* i know i can add options to the 'dynamic range' and my dro
down menu will add the option to its list.
*Problem:* How can I make it so the user can add data into th
validated data list/drop down menu rather than having the user addin
it into the dynamic range?
*Example:*this drop down menu indicates how often a system is updated
weekly, monthly, etc. etc... i don't want to restrict it, so i want t
make it so they can indicate their own time on how often those update
-...Data within a cell
If names are in cells as "last name, first name, middle initial" can a macro
be run to change the order to "first name, middle initial, last name"?
A formula will do
Assuming you have spaces between commas and names,
Then just copy down. If you want this more permanent, copy the cells with
formulas, right click, paste special.
*Remember to click &q...How do I make the x axis data the y axis data?
My graph automatically makes certain data the y axis and other data the x
axis. I need to just reverse it for the graph I am looking for. Any
One option is to reverse the positions of the data on the spreadsheet.
Assuming an XY chart, you would change a setup like this:
If this doesn't work, can you post back to let us know what type of chart
and data you're using?
> My graph automatically makes certain data the y axis ...Data entered from list automatically enters number in another cell
I am using Excel 2007 and here is an simplified example of what I need.
Items Column is List for Valid entries in table below
Prices are the numbers I want entered when I enter the Item
If I enter Oranges from Dropdown List in A16 , I want Excel to automatically
enter the number 7.95 3 columns over in D16,or if it is quite a bit easier,
just 1 column over in cell B16
Example of table I want:
My Entries: Excel Enters
...Prevent auto recoloring of chart when using Pivot Table data
I have crated a Pivot tabel with data I want to show in a Chart. My problem
is when I refresh data the colours of the "data series" changes.
For exaple I want one of my series "OK" always be shown in green colour, but
after a refresh of pivot all colours changes.
This problem is not only colouring. If I have made changes to my chart so
that one series ar plotted on a secondary axis, and I change it to another
chart type (e.g. the secondary should be shown as a line instead of column)
this also changes when refreshing the data. Most irritating!
Anyone that knows how to p...converting plain data to table format
It has been a while since I used excel but I am almost sure there is a way to convert just plain rows and columns of data into a table format. This way I believe that adding rows to this block of data is as simple as tabbing after the last cell and it should just drop down to the next row and insert one??? correct??
Excel should recognize a contiguous range as a table, or list. Include
headings in the first row, and leave at least one blank row and column
between the table and other items on the worksheet.
You can use the built-in data form to add records to the table
(Data>Form). Or,...Data Migration Framework 08-04-04
I am not understanding something pretty fundamental regarding the Data
Migration Framework (DMF). I am looking to migrate a limited number of
records (approx 1K) from Goldmine into MS CRM as Account records.
I understand that the DTS in SQL can be used to load the data into the
CDF database. On page 73 of the Data Migration Framework Guide (v1.2)
it states that you use the Goldmine export utility to extract data
from Goldmine. However, how do you use DTS to load this data into the
CDF database as Goldmine exports it into Excel?
Also, using the export facility in Goldmine you can only e...Trapping empty or null value
I have a subform with a textbox txtAmount bound to field Amount. On the
subform's footer is txtTotal with control source =Sum([amount]).
When there is no data in the subform I see #Error in the txtTotal. How can
to something that will test for this condition and display 0 when there is
no data in the subform instead?.
Unless you are using Access 2007, you can check the RecordCount of the
RecordsetClone of the subform. Unfortunately, the new version errors with
that, so you have to use a function call.
Public Function FormHasNoRecords(frm As F...Transfering a changing cell value progressively through a workbook
I am creating a payment application form in excel 2007 using windows 7. Each
worksheet represents one months invoice. Say I have a formula in "sheet 1/
cell Q7" that sums the total billed to date for a particular budget line
item. This value will be transfered to a the next months payment application
"sheet 2/ cell K7" this becomes the total amount of previous applications,
then "sheet 2/ Q7 is =sum(K7,M7,O7) this value is transfered to "sheet 3/ K7,
and so on and so on, until the completion of the job. How do I acomplish
...Automatic open locked excel file through access control
We know,Excel has prompt password
to open it files.
Is it possible to create a code that can
supplies the excel prompt password?.So that when we
open the excel file through our access control in a form,
the excel files can be opened automatically.But when the
excel files opened from its default icon,it will prompt a
On Jul 10, 8:43 pm, "shiro" <s...@play.com> wrote:
> We know,Excel has prompt password
> to open it files.
> Is it possible to create a code that can
> supplies the excel prompt password?.So that when we
> open the excel file throu...Data Range with more than value
I am trying to assign codes to one of my worksheets based on pricing. The
problem that I am running into is that the data that I am taking my
information has a range of pricing Example below
PRICE POINT/RANGE PP
$0.01 TO $1 PP0100
I need to have a formula that looks at "PRICE POINT/RANGE" and compare it to
a column that has pricing (i.e .95). This formula/function should give me
the "PP" code.
Break the range up into two columns (Data - Text to columns). Assuming you
don't have gaps/overlaps in your price range (why would you?) your formula
...Clear cell value without macro?
Is there any way to clear a cell value when the sheet is selected or
the workbook opened without using a macro?
Click on the cell and press the <delete> key.
Is this what you mean?
On Dec 9, 1:44=A0pm, Ed from AZ <prof_ofw...@yahoo.com> wrote:
> Is there any way to clear a cell value when the sheet is selected or
> the workbook opened without using a macro?
> On Dec 9, 1:44 pm, Ed from AZ wrote:
> > Is there any way to clear a cell value when the sheet is selected or
> > the workbook opened without using a macro?
On Dec 9, 7:02=A0a...ignoring null values in graph
I've built a data sheet to represent monthly result data that is appended to
each month. In the out-months a formula exists to prevent a DIV/0 error in
the data sheet. Problem is, the out months graph with a zero value. How do
I fix without having to update the chart range or formulas that calc the
percentages each month?
Take a look at Jon Peltier's Dynamic Chart information
Is this what you are looking for?
"Kris B" <Kris B@discussions.microsoft.com> wrote in message
news:1FC0C63B-61DD-4FC8-A90...How to change data range for Pivot Table
How can I make pivot table query for parameters like data range, I have
done something like this in normal query by adding [Ask For Date:] in
Does anyone know how to achieve similar result for Pivot Table.
You can use the pivot table's page fields to filter the data. Or, import
the external data to a worksheet, where you can use parameters, and base
the pivot table on that.
> How can I make pivot table query for parameters like data range, I have
> done something like this in normal query by adding [Ask F...Sending data to SQL Server
I am building a solution to allow a user to upload shape data to SQL Server
and I have a couple of questions.
1) Is it possible to include more attributes than just the shape data, but
also details of the process model file such as it's title, version, etc? I
could do with having the file name in every column of shape data for example.
2) It's quite a few clicks to export the data to an SQL database (about 5 or
6). Is there a way to have this feature available say next to the floppy disc
Since you're building a solution, then you understand t...rearrange data by day
Hi I have daily data for some ten years listed downwards, and I woul
now like to know if there is possible to rearrange them so that I ca
get all the data for each day alone I.e. one group of just Monday dat
and another group and just Tuesday data as one group, all the number
are currently listed from A1 to A2000. I only Monday to Friday as m
All help is greatly appreciated!!!
If my explanation weren’t good enough, this is what my data look
Tuesday 12/31/2002 279,73
Monday 12/30/2002 279,73
Friday 12/27/2002 281,982
Thursday 12/26/2002 278,829
Wednesday 12/25/2...puzzling data mismatch error
I have a table where one of the fields contain values such as
15-13-1C-R3-K6 and so on.
this is a text field
I am trying to find records based on the part: R1-K3, R2-K4, R5-K1
I am getting weird data type mismatch error when searching with my
For example this works:
SELECT myfield as P FROM myTable
WHERE myfield Is Not Null AND right(myfield,len(myfield)-9) Like 'R1-
ORDER BY myfield
but this one using a nested OR doesnt and fails with a data mismatch
SELECT myfield as P FROM myTable
WHERE myfield Is Not Null AND