Does the cell contain...
I'm trying to do the following:
if the cell G2 contains the value "april" then cell M2 equals April2,
knowing that the cell G2 can contains the value "april" at the beginning or
at the end or anywhere else.
One solution is as follows:-
Create a fuction as follows:-
Public Function CellG2(txtCellG2 As Variant) As Variant
Dim numIntPos As Long
Dim txtTemp As Variant
CellG2 = "" 'set as default
txtTemp = LCase(txtCellG2) ' make sure everything is
...need help creating formula based on cell value
Is it possible to create a formula using a cell value to create a formula.
basically I have multiple sheets with the name as a month. I have one sheet
that does my reporting. So Instead of having a to create a seperate formula
in each cell to reference each month (ie. ='jan report'!$A$14, ='feb
report'!$A$14, etc) I want to be able to do something like create a formula
to reference a cell that would contain the month name. For example.
a2 ="'" & A1 & " Report'!" & $A$14
b2 ="'...How to COPY from cells to cells w/o affecting respective formulae underneath ? #2
Would highly be appreciated if can be told on:
How to copy data from
cells ( formulated with "a") to
cells ( formulated with "b")
without affecting respective formulae underneath after being pasted ?
Thanks in advance.
Message posted from http://www.ExcelForum.com
...Return an empty cell
The following formula will return an empty value if the condition is true:
My problem is that when I go to chart the data, the chart treats this entry
as zero. I have the options set to leave gaps for empty cells, but it still
treats this entry as zero instead of empty.
Is there a way I can have my formula truly return an empty cell so it will
not be plotted?
You could try:
Then use conditional formatting to hide that error (so it's prettier).
Mike D. wrote:
> The...copy sheets values
i have a question;
Is it possible to copy values (A2:Z500) from a sheet name "Data" in a
worksheet name "BdData.xls" to an other worksheets but not in the same
folder as the first, and in this worksheet name "BbData.xls" values would be
in the sheets "Data".
Thanks a lot for all your advices.
Open both files and use Edit|Copy (xl2003 menus) to copy the range.
Go to the other worksheet in the other workbook and select the top left cell of
the destination range.
Then Edit|Paste (or Edit|Paste special|values).
If ...Protected Cells
Is there a way to protect certain cells, in a spreadsheet, so that the user
cannot modify (destroy) the formulas?
Just use regular protection. Before you protect the sheet,
right..click on the cells you want the user to be able to modify and
select Format Cells. Uncheck the Locked box. Now, when you protect
the sheet, only those cells that are unlocked will be able to be
> Is there a way to protect certain cells, in a spreadsheet, so that the user
> cannot modify (destroy) the formulas?
Yes this works great... thanks.
"JW" <JW...lock text box to cell
I created a text box through the control toolbox. I want to lock it to a
cell, so that I can create a macro which saves the invoices in another folder.
Not sure about saving invoices to another folder but in design mode, right
click the textbox, select properties and enter the releveant cell address (eg
F2) in the LinkedCell field.
Hope this helps
> I created a text box through the control toolbox. I want to lock it to a
> cell, so that I can create a macro which saves the invoices in another folder.
Yes that worked thank you. Do you know how...Multiple copies per sheet #2
Like many others, I never had this problem until Publisher 2007. I'm used to
creating a postcard (1/4th page) or 1/3rd page document and having it
automatically fill a page with four postcards or 3 copies of my document.
With 2007, it's part of the print choices instead of document choices. So, I
have to triple or quadruple my print number to get what I need then it takes
extra long for the job to send to the printer. Is there a way around that?
Maybe there is a default setting I'm missing that needs to be reset?
I've tried just changing my document settings and it doesn...changing numbers in a column by a percentage
I have a column of product prices and want to increase all prices in that
column by 10%. I'm new at this and sure could use some help.
put 1.1 in an empty cell
Edit|copy that cell
Select the range to adjust
edit|Paste special|check multiply
clear that cell with 1.1 in it.
> I have a column of product prices and want to increase all prices in that
> column by 10%. I'm new at this and sure could use some help.
I would personally use a help column, format it as currency then a formula
the...Conditional Formating multiple cells in 1 row based on a list of d
I need to highlight various cells in 1 row based on a list of dates in a
Look in help for conditional formatting or post a more concrete example
"Clinton" <Clinton@discussions.microsoft.com> wrote in message
> I need to highlight various cells in 1 row based on a list of dates in a
> separate column.
In CF you'll need the "Formula Is", rather than "Cell Value Is" option.
If the formula evaluates to TR...How can I find the last populated cell in a column
I need to automatically read the last populated cell in a given column.
Example:Cell R4 will pick up & display the last item in column B.
There are a list of items in column B, ranging from cell B5 to B16.
B16 is $200.00, then R4 should read $200.00 as well.
If column B gets added on and B17 is $275.00 then R4 shoul
automatically read $275.00.
This should hold true for text or numbers
Message posted from http://www.ExcelForum.com
find below a couple of possible formulas (depending on the type of your
data): In your case take A.2.d or A.2.e
A. Collecti...How do I open up an emailed .xls document without Excel?
I don't think I have Excel on my computer. I just know I want to open an
..xls file I was sent. I do have Microsoft Works. If a copy of Excel is
required to open any .xls document, is it available for download online? Or
must I go purchase a copy of it? Or is there another alternative? The
cheaper the better.
Use the free Viewer
Regards Ron de Bruin
"Edward" <Edward@discussions.microsoft.com> wrote ...fill cells with a value from two previous cells
I try to copy the letter value from column A and B to column C in the
example below with a formula:
A B C
2 I I
Maybe this in Cell C1 and copy down........
Vaya con Dios,
> I try to copy the letter value from column A and B to column C in the
> example below with a formula:
> A B C
> 1A A
> 2 I I
Assuming that there is a value in only column A or column B, ...Same column, different cell width at different row
I have tried inserting a break and split, didn't work. I just want to
separate the top half of a page with the bottom half so I can apply different
cell width on the same column. Or how would I be able to do this? Same
column but different cell width.
You can't do that.
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"Glenn Chung" <GlennChung@discussions.microsoft.com> wrote in
>I have tried inserting a break and split, di...Is there a way to use the "Cell Style" option the same way you can use it on PC?
Operating System: Mac OS X 10.5 (Leopard)
I am in a comp class in college and the book is written for PC user only not for macs. I see that in Mac's version of Excel there is a pop out tool bar "Formatting Plate" and @ the bottom of it there is a section labeled "Document Theme" I believe this is what I am looking for, however there is only a select few, and the options are not laid out like they are on a PC. <br>
Is there something that I'm just not seeing?? It would be EXTREMELY disappointing if Mac's don't offer m...counting cells with time criteria
Here is my dilemma . . .
I have a large amount of data where one column has log in time ( eg -
8:00 AM). I need to figure out the formula that will let me count the
number of cells that are between 8:00 AM and 12:00 PM in that
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/
XL counts one day as 1, therefore 0800 = 1/3, 1200 = 0.5. Try
if you mean between 0800 & 1200,
=SUM((A1:A...Changing the order of columns in a custom view
Hello, I am creating a custom view of a simple worksheet. This view i
being created to summarise the contents of the 30+ column worksheet t
a 5 column snapshot for our management.
However, I would like to be able to shift the order of display of th
columns when I create this custom view.
I.E In the full worksheet I have columns
A B C D E F G H I J
In the custom view I would like to display
A D C B E
I do not need to alter any data in any column, simply to change th
order in which the columns are displayed. Is this possible or ca
someone suggest an alternative approach?
--...Editing cell that has a formula in it, but displaying content and
how do you Edit a cell that has a formula in it, by displaying content and
not the formula it has in it?
If you want to replace a formula by its result, Copy, then Edit/ Past
"thechad987" <email@example.com> wrote in message
> how do you Edit a cell that has a formula in it, by displaying content and
> not the formula it has in it?
Reading between the lines in your post, what exactly is it that you want/need
to edit, based on the value...send to group members without their names showing
How do you send an email in OE to a group and not have everyone's name show
up in the "to:" field ? I'd like every member of the group to see only their
own name in the email.
Use the BCC (Blind Carbon Copy) field
"Pat Jones" <Pat@marketingguy.org> wrote in message
> How do you send an email in OE to a group and not have everyone's name
> show up in the "to:" field ? I'd like every member of the group to see
> only their own name in the email.
...Sheet hidden and VeryHidden
What is difference between sheet Hidden and Very Hidden?
The biggest difference is whether the user can see (and show) the worksheet via
> What is difference between sheet Hidden and Very Hidden?
And that you can make a sheet very hidden only via VBA
"Dave Peterson" wrote:
> The biggest difference is whether the user can see (and show) the worksheet via
> Format|sheet|Unhide dialog
> Marc wrote:
> > What is difference...easy way to find location of the named cells?
Is there an easy way to find the location of a named cell?
If I am looking for a cell named SOMECELL.
I cannot just select it in the "Name Box" on the formula bar. Because it
will change the current cell to that name.
is there a good way to find the location? or get to the location?
Maybe you'd be better off using: Insert|Name|Define and looking at that list.
Get Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp
It'll make working with names much ea...Cell: date and day format
I use Excel 2003 in English. I use custom format for date output in a
cell . For example:
dddd-dd-mmm-yyyy gives me Sun-04-Oct-2009 . Do I have to write a macro
to obtain the:
Dim(French) instead of Sun or is there a another way?
"Bobby" <firstname.lastname@example.org> wrote:
> dddd-dd-mmm-yyyy gives me Sun-04-Oct-2009 .
> Do I have to write a macro to obtain the:
> Dim(French) instead of Sun or is there a another way?
Well, you could change your computer's Regional and Language control setting
But note that that could also change the month ab...Total up cells from sheets
I have a workbook that contains 18 sheets, with the final sheet showing two Total cells one contains the totals from adding together all the cells C66 and the other all the cells C67 in all the previous 17 sheets. I will be alway's using the same cells as I have made a template. Is there an easier way of adding all the cells without typting every sheet reference and cell
However, that depends on sheet *position*, i.e. Sheet1 on the left
and Sheet17 on the right. If the user moves Sheet2 to the left of
Sheet1, Sheet2 will no longer be inclu...Changing Font Colour of row depending on selection made in 1st col
I am currently trying to work out how to change the font colour of all cells
in the row, depending on the selection made from a list in the 1st column
Column A is based on selecting from a List, with about half of the
selections already conditionally formatted to a certain colour.
My question is - how can I get the other cells in the row to change to the
same colour as the selected cell? Given that the list contains 17 different
cells to select from, and 10 of these when selected display as a different
Currently I am manually selecting the row and changing the font ...Default format for text in a cell
I have three cells that people will be populating but i need to make
them with a default format and length.
Field 1 defalult will be character field 7 long 0123456
Field 2 defalult will be character field 9 long 012345678
Field 3 will be Numeric 06/01/2010 format
so if for the first field a person enters " 123456 (without the
leading 0) then an error message should pop up "enter 7 digits" ,
leaving the Field 1 blank until the 7 digits get entered..... same
with field 2
and Field 3 should have mm/dd/yyyy format