Fill the entire column with a function
Maybe it is too simple - But I have no Idea :-(
I have a sheet - which all columns are retrieves data using SQL Query
(From Sql Server), but the first column is calculation based on the
other columns - How can I have the first cell in each row populated
with this formula? (Currently - after refreshing data - I am dragging
the formula for the new rows added. I am sure there is a better way)
Can anyone help please?
for a macro equivalent of Ctrl+D
It will look to the cell to the left to see how far to copy down.
Ctrl+D wi...AND conditions before summing up
Let say my data are as follows:-
A B C D
100 6 T 10'
200 7 E 15
300 6 D 10
400 5 F 20
500 6 T 11'
I will sum up the numbers from Col D if the number in Col
B is "6" AND the text in Col C is "T". How do I come up
with this formula? Does sumif works here? If yes, how?
In this case, my answer should be 10 + 11 = 21.
Hope somebody can help me out here.
"Ginger" <Ginger@hotmail.com> wrote in message
> Let say my data are as f...Short date format
When I enter a birthdate that is prior to 1925, the display is 20.. instead
of 19.. I would like to make the default year 1901. For instance, if I
enter 2/2/02, the display should be 2/2/1902 insteand of 2/2/2002.
Thanks for any help you may render.
Try enterin the year as 4 digits. The problem you are having is how a DLL
sets the dates for entries of 2 digits. Changing it, changes it for
everything on the computer. Right now the date of century change is 2029.
That can be altered in Control Panel >>> Regional Settings >>> Regional >>>
-- ...Adding overscore to character in a cell in an Excell spreadsheet
Can you do this?
I know how to do this for a floating object (insert object, equation editor,
add overscore) text box, but not how to do this within a cell.
I would like to create a column in a table with a reference to
crystallography. I would like to type <one one one bar> as <111> with a bar
over the third one (or the second, or the first). Some of the crystals have
indices other than 1 (i.e. <1122> with a bar over the first 2).
Is this possible to do?
...Conditional Format on "Active Cell"
I have a spreadsheet where the rows in Column A are descriptions and Columns
B thru H are Mon thru Fri.
As you arrow down entering dollar amounts by day for each description I
would like the Description is say A44 to bold or change color when the cell
in say H44 is active.
Is this possible?
I'd suggest you take a look at Chip Pearson's RowLiner add-in:
But if you just want to change the color of the cell in column A:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim rArea As Range
...Carriage return in a text cell
Is it possible to enter a carriage return in a text cell so that two
individual items appear on separate lines when the text is wrapped?
Press Alt and Enter together.
"Opinicus" <email@example.com> wrote in message
> Is it possible to enter a carriage return in a text cell so that two
> individual items appear on separate lines when the text is wrapped?
Alt + Enter
This is one line
Alt + Enter
This is nex...Change cell color on update
How do I change a cell's color if it has been modified? I
am sure I Use conditional formatting, but not sure how. I
have a contacts spreadsheet and I need to send it around
to other memebers of my company for them to update, when I
get it back, I need to know what has been modified. How
to do this?
Switch on Track Changes
Complete the dialogue box
Message posted from http://www.ExcelForum.com
Have a look at Tools / Track Changes. This will highlight any changed cells
and what the changes are.
I have the lookup function:
=LOOKUP(A3,'Item Price List'!A2:A1726,'Item Price List'!D2:D1726)
which finds the value in A3 on another worksheet in column A an
returns the value in that row in column D.
I'm trying to fill down, but I only want the "A3" to increment itself
How do I stop the A2:a1726, and the d2:d1726 from incrementing also?
Message posted from http://www.ExcelForum.com
And look up relative and absolute references in Help.
Please keep all correspo...Copying and Filling Cell With Functions
I have an Excel page that has refrences to another worksheet ie =May!B2 I
want to create a generic spreadsheet that I can just copy for each month but
when I do I get ='[May Commissions Sheet.xls]May'!B2 is there a way to advoid
this? And also when I try and do a fill instead of trying to copy I get
=May!B3 or I tried typing in cell M2, =May!B2 and in cell M3, =May!C2 and
then tried to do a fill that way for Excel to get an idea of what i was
trying to do and it ends up putting =May!B4 in M4 and =May!C4 in M5 and
=May!B6 in M6 etc. Can anyone help me cause I am about to lose i...XML commands
I found a very helpfull hint to find formula-cells. The trick is to
use the get.cell function. I would like to learn a little bit more
about that function. What does 48 or 6 mean in get.cell(48;.... or
I can't find documentations at the net ?
Get.Cell(num, cell reference) is a function from XL4 to return information about the formatting, location or contents of a cell.
48... if cells contain a formula it returns True; if a constant it returns False.
6 ... returns the cell formula
Peter Noneley has a free download at...
I have a excel worksheet that i want to copy the data and the formula
to a new worksheet .The worksheet was linked not embeded so it won
work without the link.
When i try to copy it i only get the data not the formulsa .
Any help would be a great help
philthedil's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=799
View this thread: http://www.excelforum.com/showthread.php?threadid=26406
Replace all equal signs with r=, copy what you want, then reverse the
edit&g...How can I use dates in a formula?
Operating System: Mac OS X 10.6 (Snow Leopard)
I want to grab todays date in an excel formula, and subtract it from another date. <br><br>For example, our quarter ends on march 31, 2010. I want to figure out the number of days left in the quarter. What would even be cooler is to only factor in work days.........anyone know how to do this?
> This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
charset=&qu...Auto fill multiple sections with different names
I have weekly time broken down by category, however, only the total line has
the corresponding names. How do i fill the above cells with the corresponding
name on the total line. For example(where blank insert last name on total
Col A Col D
1. Select the cells in column A
2. Press F5
3. Select 'Blanks...Making fields conditionally dependant on the values of other fields?
I am working on an order-entry form for my users, and need to make the value
of some fields conditionally dependant on the values of others. For
example, if FieldA = "NONE" then I need FieldB to have one value. If FieldA
= "YES" and FieldC = "OUT" then I need FieldB to have a different value, and
What is the best method for achieving this in Access 2000?
It can't be done through tables. You could probably accomplish it on a form,
using VBA, or else as computed fields in a query. Note that if you have
rules defined as that, then you should NOT be ...Enter the cell value without leaving the cell
Is there a way to use the keyboard to enter a cell value without exiting the
cell when you press the ENTER key? I use the mouse to click the green check
mark in the formula bar, but I would prefer to use the keyboard to
accomplish the same task.
CRTL - ENTER
"Chris Hedlund" <firstname.lastname@example.org> wrote in message
> Is there a way to use the keyboard to enter a cell value without exiting
> the cell when you press the ENTER key? I use the mouse to click the green
> check mark in the formula bar, bu...Cell Number Format
Is there a way to format a cell's number from 1.2 to 1,200,000? I
know how to format millions to ones but how do I convert from ones to
millions? (I don't want to have to multiply in another cell).
I could be wrong but, suspect there is no way to achieve what you want using
You could use the sheet change event to multiply numbers entered by one
million and format the result as you wish using code below. Just right-click
on the tab of the appropriate worksheet, click view code and paste. Note.
this will multiply value in column C only. You will need to modify ...Adding in another conditional format on the same cell
I have a cell containing "conditional format" when 2 weeks before the due
date ...the cell colour changes to "orange"....it turns red when it reaches
the "due date" .....
Now.......i need to add in another condition ....when i add in another
column e.g. "B" and the font colour will change to "yellow" ....i think its
something to do with....if equal to....then it would change to this
colour.... Can someone teach me the formula pls? urgent....thanks everyone....
What exactly are you trying to add as another condit...cell referencing from a Combo Box
I'm trying to make the result given in a combo box make data appear i
the cells (or text boxes) to the left and right of the combo box. Th
data being drawn from is a range of cells in a column that is part of
table with the same arrangement as the combo box/cell arrangement.
just want to be able to choose data from that table, and by choosin
one element in a row, have the rest of the row appear around it.
I've been trying to find a formula that will allow a cell to refer t
the result in the combo box, and give its result as the cell dat
adjacent to that given in the combo box, as th...SUM rows in column if cell in different column is empty
I have a spreadsheet that has many rows of data. I'm trying to get a
total for a column ("G") in a cell ("A2") but only on rows where the
cell in another column ("J") for that row is empty.I tried SUMIF and
couldn't get it to work. Can this be done with some kind of formula?
Any help would be appreciated.
(there's no email, no snail mail, but somewhere should be gmail in my addy)
<email@example.com> wrote in message
news:firstname.lastname@example.org...Copy text from cell and paste it in a separate text box (contents to be linked)
With some cells in a colum selected (say 10 selected cells in a
column: contiguous range) I would like a macro to copy the contents of
these cells and paste the contents of each cell in separate text
Now here it comes. The pasted contents in the text field should be
linked to the cell contents, i.e. whenever the cell's contents is
changed, the contents of the text field should reflect this. Is this
Help is much appreciated. Thank you very much in advance.
Again, Detail and before/after examples.
Don Guil...simple formula for some
Hi - I hope someone can help with a formula problem- with 3 columns covering
dates/ date ranges and using another for a % complete figure
I have 1 column with a range of week dates covering 6 mths ( Col A)
I have 1 column with lots of dates ( Col B) and another with a % complete
figure ( Col C) against the dates
what i would like to do is a "count if" ( or something similar - help) for
in a seperate cell - I would like col B to "look" at Col A and count the no
of times this date is the same but only if the date in Col C i= 100% .
any idea...How do I reference a worksheet name through a formula?
I would like to reference a cell to obtain a worksheet name rather than have
the worksheet name in the formula. So rather than have Sheet1! in the formula
I would like to have a cell that contains the text "Sheet1"
Check out the Indirect function.
"pv6901" <email@example.com> wrote in message
>I would like to reference a cell to obtain a worksheet name rather than
> the worksheet name in the formula. So rather than have Sheet1! in the
> formul...Adjust text to fit within an Excel Cell using C#
I am developing a console application in which I have exported data from
dataset to an Excel. I need to adjust the text to fit within the cells
without using wordwrap. As my requirement is to achieve the data in every
column in a single row. where my data in each column keeps varying.
Below is the method code used to generate the excel sheet report.
private static void GenerateWorkSheet(XlsDocument doc, DataTable dt, string
Worksheet sheet = doc.Workbook.Worksheets.Add(workSheetName);
if (dt != null)
...protection, formulas and sharing with dummies
I am having a protection problem and have tried the unlock and hide solutions
I have a workbook I am sharing with co workers. Many of the cells have
formulas in them that change cell contents based on inputs. Not all the data
is availabel at once and the workbook is meant to be a "tracker" type that
provids alerts when days have passed etc, based on formulas and conditional
I need to make it idiot proof, ie. when someone type a number into the
wrong cell (say one that should have remained empty) by mistake, then tries
to delete the er...How do I format a cell, so the user must use a user calendar?
I created a user calendar by means of a macro. I want to set a cell in the
workbook so when someone clicks on the cell the calendar automatically comes
up so they can select a date.
See Ron de Bruin's site for code.
Adjust the range to suit
Gord Dibben MS Excel MVP
On Mon, 16 Jan 2006 09:25:03 -0800, nathan <firstname.lastname@example.org>
>I created a user calendar by means of a macro. I want to set a cell in the
>workbook so when someone clicks on the cell the calendar automatically comes
>up so they can se...