Using Excel 2002:
I will open a text file into excel. This file has no formatting, but the
end result would be using two columns.
The text file for example has:
Music1 from site1 and site2 special 90876
Music100 extra from site1 87654
Music02 misc sites 10020
I want to put the numbers, 90876, 87654, 65409 in the same column "B" and
have the other text combined into one column "A". When I use space
delimited I can move the numbers in the same column. How do I combine the
rest of the text which are word for word in columns A,B,C,D etc into one
End...How to return a range of values in a drop-down.
Is there a way to have Excel return a range of values in a drop down in one
cell based on input from another cell.
As an example:
Cell A1 has the text "PVC Pipe"
I want cell A2 to get input from A1, read through a table, and return the
corresponding values (sizes) of PVC pipe. The results in A2 would be the
following in a drop down:
If cell A1 contained the text "Metal Pipe" it would return values (sizes)
of metal pipe from a table and not display the sizes of PVC pipe.
The bottom line and...Using SUMIF to add data between a range of dates
Hi, I am developing a cashflow spreadsheet, and need to add a range of values
(in column B) based on the criteria that they are relating to a set week, ie
in column B has the amount to be paid, and column C has the date the amount
is due. I need to find out the total amount due between 2 dates. Does anyone
know how I can do this?
With start date in B20 and end date in B21 try this:
"Jaspa" <Jaspa@discussions.microsoft.com> skrev i meddelelsen
news...how do you copy the value of a cell, but not it's source?
U�ytkownik "hugosharp" <firstname.lastname@example.org> napisa� w
paste special - paste values
try to write your questions in the body of message instead of subject
...Out of Range
This is my VBA macro. I'm getting a "Subcription out of range #9" error
message on: Windows(fname).Activate
Can someone help fix it, if possible?
fname = Application.GetOpenFilename
Drop the line:
The opened workbook is the active workbook.
"Jeff" <Jeff@discussions.microsoft.com> wrote in message
> This is my VBA macro. I'm getting a "Subcripti...Formatting cell time format
I'm having real problems with Excel 97 when I format a Cell for time it
seems to put the date in as well I'm really confused is there something
I am doing wrong ??
Message posted from the Excel Tip Forum at http://www.ExcelTip.com/forum/
-- View and post usenet messages directly from http://www.ExcelTip.com
-- Hundreds of free MS Excel tips, tricks and solutions
Dear he / she;
It may sound simple and I expect you to have tried it, but did you try on
the menu :
Format | Cells | C...macro to move cursor one cell right
If I need to move the cursor to the right to paste a value copied from
another sheet , what macro command should I use
You don't need to. The Copy Method accepts a Range argument which
would be the destination for the paste operation.
On Sep 22, 3:31=A0pm, Kodak1993 <Kodak1...@discussions.microsoft.com>
> If I need to move the cursor to the right to paste a value copied from
> another sheet , what macro command should I use
If JP's reply does not do...How to make comments move with cells when cutting pasting, or inserting rows
I have to insert / delete rows or move cells often. When
I do this, my "comments" boxes don't move with the cells
(they end up all over the place), and they often grow
Is there some way to make the comment boxes autosize and
follow the cells???
Debra Dalgleish has some code that does lots of things to comments:
Including resizing them:
(and repositioning them to their original spot (just scroll up on that last
I would like to know how to change the top of the cells. Instead o
A,B,C,D,etc how can I put what I want in it instead of the letters
any help would be appreciated I know zilch about data base.
Message posted from http://www.ExcelForum.com
AFAIK, you can't, sort of!
You can change the column headings to numbers:
Tools>Options>General>R1C1 reference style
But, if you don't want either letter or number
designations you can choose not to have row or column
headings displayed and use the first row and column of
cells and enter your ow...Unable to delete cell range.
Operating System: Mac OS X 10.5 (Leopard)
New to excel and following a video tutorial. I am using auto-fill to create a range of numbers. The cells are selected and I try to delete all the content in the range by pressing the delete button per the tutorial but only the first cell in the range is deleted. Please help a newbie.
Are you using a laptop or a condensed keyboard by any chance?
If so, the key labeled 'delete' is the equivalent of 'backspace' & does
delete only the content of the active cell in the range. You need to hold
I have a spreadsheet listing members of staff, dates they have attended
training and their status regarding their training. They are either
operational or suspended. Conditional formatting will only allow me to
change the font of text in the cell. Is there a function that will
automatically change the text depending on different conditions ie once a
date has expired the cell changes from operational to suspended automatically?
Let's say the dates attended are in column B and we want status in column C.
Just for this example, I am assuming that after one year (365 days) the
tr...Count if cell contains a comma
I want to count the cells which contain a comma. Is it possible to do
Please reply to newsgroup, not e-mail
Try this out
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/
~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements
On Sun, 7 Dec 2003 20:02:29 -0600, "Fred Smith" <email@example.com>...Summing a range within a range
I need the formula that will sum a range within a range. Example: the primary
range consists of the following numbers: 1,2,3,4,5,6,7,8,9,10 and I need to
know how many numbers are >3 and <8. First I need to know the sum ot the
qualifying numbers; second I need to know how many items there are.
Stumped and exhausted...anyone's help will be greatly appreciated!
Need to Know
For the SUM:
For the COUNT:
...How do I add a date formula to a cell but hide the contents with .
I'm got a spreadsheet with many different sheets in it. The first sheet is
the main page, and it holds a master table with data like customer name,
salesman, registration number, date of registration etc. The other sheets
are the individual salesman sheets, with just their relevant information
which is copied from the main page. I'm experiementing with two ways to do
this, VLOOKUP or just a formula like =sheet1!A12 etc. In the date column I
want to put the date of registration, so I can copy that from the main page.
However, sometimes a date is not always needed, so when I ...Data Range
I have searched through this forum cannot find a solution to what I need so
here goes (or perhaps I have missed it).
I have a workbook containing 11 worksheets - one for each country with the
first worksheet containing a set of standard charts which are reused for each
country. I am currently manually amending the data range and selecting the
worksheet and highlighting the data for each country (so have to do this 10
times). The data is in the same location on each country worksheet so really
the only amendment is the worksheet name.
How do you recommend that I speed up t...Display range name when a cell within the range is selected
How to display the name of a range when a single cell within that named range
This displays the named range(s) that the cell belongs to, if any, in
the Status bar.
Put this in the ThisWorkbook code module of your workbook.
Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, _
ByVal Target As Excel.Range)
Dim sRanges As String
Dim nmName As Name
Dim bInRng As Boolean
On Error Resume Next
For Each nmName In ThisWorkbook.Names
Debug.Print nmName.Name, nmName.Re...How to get last cell in "Avg()" calcualtion
I am trying to average a series of numbers of a second sheet within
the same workbook. My equation is:
xlApp.Range("K7").Formula = "=AVERAGE('Earned Value'! H2,H500)"
I would like to use something similar to the following to ensure I get
the last cell.
xlApp.Range("K7").Formula = "=AVERAGE('Earned Value'! H2,H &
Where have I gone wrong?
not tested but try:
xlApp.Range("K7").Formula = "=AVERAGE('Earned Value'...Percentage on x axis and period range on y axis
I'm trying to plot the length of time staff have worked for a company
on the y axis in ranges i.e 0-6 months, 6-12 months etc. I have a
seperate column that has converted the months worked into each of
I then would like to show on the x axis the percentage of staff that
fall into each period.
I would certainly appreciate any assistance!
Wayne Beasley wrote:
> I'm trying to plot the length of time staff have worked for a company
> on the y axis in ranges i.e 0-6 months, 6-12 months etc. I have a
> seperate column that has converted t...Extracting Multiple Values From One Cell
I copy/pasted an address list from an HTML source. One column contains cells
that have City, State and Zip information separated by a space. How can I
move those three [or two] pieces of information, each into its own cell.
Use the text to columns feature for this.
Select the range of data.
Goto Data>Text to Columns.
Select: Delimited , Next
Check: Space , Finish
>I copy/pasted an address list from an HTML source. One
column contains cells
>that have City, State and Zip information separated by a
space. How can ...Match the Cell From Two Different Columns
How can we match/lookup cell value / variable from "A" cloumn with "B" column. The match value come on "C" column otherwise its FALSE
A B C
RB123 RB127 RB127
RB124 RB128 RB128
RB125 RB129 FALSE
RB126 RB130 FALSE
RB127 RB131 FALSE
RB128 RB132 FALSE
EggHeadCafe.com - .NET Developer Portal of Choice
Enter this in C1 and copy down as needed:
<Nasir Naqvi> wrote in message news:firstname.lastname@example.org...
&...How do you point to a named range in linked workbooks?
Workbook #1 has a range named "Gross Sales" in Sheet1 Cells A2:K2. How can I
point to this range so that I can link it to Sheet1 Cells2:K2 in Workbook #2?
Is this what you're looking for...?
In workbook 2 enter "=[Book1]Sheet1!$A$2" in cell A2. Do the same for cells
B2 through K2 with the correct respective links.
Hope this helps.
> Workbook #1 has a range named "Gross Sales" in Sheet1 Cells A2:K2. How can I
> point to this range so that I can link it to Sheet1 Cells2:K2 in Workbook #2?
Or, if you don't like to ...Changing many cells from absolute reference to relative
Yesterday I changed all the formulas from relative to absolute references and
now I need to change them back. I used this macro to make them absolute.
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlAbsolute)
just have a look at ConvertFormula in the VBA help
"Woody13" <Woody13@discussions.microsoft.com> schrieb im Newsbeitrag
news:67D64550-E37B-45BD-BF8D-F971B3918E10@micros...How to split a cell diagonally?
I have a cell that I'd like to put a diagonal line through, like a calendar
that has 2 dates on the last Sunday.
Do you know how?
Format Cell, Border.
> I have a cell that I'd like to put a diagonal line through, like a calendar
> that has 2 dates on the last Sunday.
> Do you know how?
Also, you'd have to fake the text, type the first date, then do a carriage
return (Alt+Enter), put a few spaces, and input the second number.
"Bob I" <email@example.com> wrote in message
news:%23NTN1...subtracting a range from another range and getting a range result
I have two ranges of XYZ coordinates. One is named R1 and the other
R2. Is there any way in Excell 2002 to bascially say R2-R1=R3 so that
R3 is a range the same size as R1 and R2, where each cell in R3 is is
subtraction of the corrisponding cells in R1 and R2? Sort of like
of course I need to do this on a two dimentional range, there are
multiple XYZ points.
Are the three values of R1 (3,4,5) in separate cells?
Are you adding or subtracting?
Please spell out the problem in more detail
Bernard V Liengme
remove caps from e...Vba Cell Address Question #2
No, I have a drop down menue that quaries the internet for stock dat
based upon the stock selected in that drow down menue. Since the dro
down menue has a control cell linked to it I would like to utilize tha
number which is in D5 on the sheet as a of determining where to pu
data, i.e., the closing price of the stock selected.
What I am looking for is a way to reference a cell address with th
value of another cell.
MJSlattery's Profile: http://www.excelforum.com/member.php?a...