cell in cell
Okay, I got the money cell in cell formula to work, but now I want to do one
with a date, and can't seem to get the date to show up correctly.
If I just enter the formula in the text string as &(A1) & where A1 is the
date January 7, 2005, I get the numeric value of the date 38359. How do I
switch the format in the calculation? Is there a way to bold is within the
rest of the sentence?
(This is Excel 97...I'll be getting an upgrade to Xp and 2003 in a week...)
This will convert the 38359 to a date format in your formula:
=TEXT(A1,"mm/dd/yy")&" your c...Automate unprotecting of blank cells
I have a spreadsheet that has several cells with values entered int
I want others to add some values, but I don't want them to change cell
that already have values.
I know that I can use goto, special, constants and protect thos
Can someone recommend a macro or method to review each cell within
specified range and unprotect it if it is blank?
Any suggestions would be appreciated.
Jim Palmer's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=521
View t...Unrecognized database format.
I've recovered data from damaged partition and then it was saved. But after
I open the file, it can't open and show the popup menu "Unrecognized database
format". The size of file is 3,460 kb.
How can I do for recovery that file.
Any of suggestion.
Is the data in a table(s).
Try creating a new DB and then use the import data facility from the old
into the new.
(File - Get External Data)
It's worth a try before you start on any more complex.
...IF in cells
Is there a maximum amout of multiple IFs you can have in one cell?
I have a formula that should work but says there is an error, cut it down it
I have 24 ifs in the cell.....
It's seven in versions up to 2007, 2007 has more. Consider a data table and
using VLOOKUP will normally work better
Microsoft MVP - Excel
"Craig Coope" <firstname.lastname@example.org> wrote in message
> Is there a maximum am...Find cells with conditional formats
Is there an easier way to find the cells with conditional formats than
going to each cell and selecting format, conditional formatting?
On Aug 28, 9:37=A0pm, mjones <mich...@quality-computing.com> wrote:
> Is there an easier way to find the cells with conditional formats than
> going to each cell and selecting format, conditional formatting?
Found it. It's under Edit, Go To, Special. Thanks.
My free "Formats & Styles" Excel add-in provides a count of CF cells that includes a count of cells with multiple conditions.
http://excelusergroup.org/m...Inporting data to cell that exceeds the size of the cell.
When I import data to the excel-document that exceed the size of the cell, the cell will expand vertical. I want the text to be horizontal. How can I do that? I use text-boxes for import.
When I use the fuction display "full screen" in some of my excel documents, I dont see the full screen. It is problably someting in the setup, but what
Can somebody help please?
Prior to the import, set the column width of the cell that will be receiving
the data to be as wide as you want it to be after the import. That will
allow Excel to spread the data out rather than expanding the row ...Replacing "*" in a cell
Got a quick question,
I downloaded my bank statement in txt format. When there is a skip i
check number, the bank mark the next check number using "*". Example
chk 1011 1013*.
I want to get rid of the "*" using 'Replace' but Excel consider it a
'all' so excel replaces all of the value.
All I want to do is to change '1013*' to just '1013' without * so I ca
sort it by check number.
Message posted from http://www.ExcelForum.com
Use this instead
Regards Ron de Bruin
...Returning the formula in a cell
I have umpteen sheets in my excel, so I think it would be very useful if I
could have hyperlinks to each of these sheets on the summary page.
Let us say I have, in Sheet 1, Cell A1 the formula "=Sheet2!A2". Lets also
say that Cell Sheet2!A2 contains "Hello"
In colum 2 (B1), i want to be able to use the HYPERLINK formula - if I click
on this cell, it shud take me to Sheet 2
I want to pick up the text "Sheet2!A2" from A1 and not the value returned by
"=Sheet2!A2"(which is "Hello").
Which formula would I use to pick up the formula in cel...Stop excel emedding (opeing in mail format) when outlook is open.
When I have outlook open and I then try and open a excel file it opens in a
mail format and try as I have I can't find the setting to stop it from doing
so. Please help because it is really frustrating and laborious having to
close outlook just to open a excel file normally.
Does mail format mean that you see the address boxes at the top?
If it does, maybe just toggling this off would be easier.
File|Send to|Mail Recipient
(that icon in front of "mail recipient" is difficult to decipher, but it can be
toggled up or toggled down.)
Help Hey wrote:
> When I have out...How do I display summary of different cells in one cell?
I am trying to summarize in one cell, all the different shoe sizes worn
through the week. my spreadsheet looks like this
Mon Tue Wed Thu Fri
Shoe size worn 5 6 4 8 9
Shoe Sizes Worn through week 5,6,4,8,9
I'd like to somehow display 5,6,4,8,9 in one cell. Is there an easy way
to do this without a huge nested IF thing?
Thank you in advance
> I'd like to somehow display 5,6,4,8,9 in one cell. Is there an easy way
> to do this without a huge nested IF thing?
you can use st...Cell matching issue!
Ok here is my issue: I have a workbook containing 2 sheets. One sheet
contains a list of ETFs with livefeed data from Bloomberg about two
different volume measures.I then use this data to do a simple
calculation for the performance of each ETF.
The issue is the fact that in the other sheet I want a table with the
top 5 performers and the worst 5 performers.
What I did in order to receive the names of the ETFs, provider,
performance etc, I used the VLOOKUP function. The performance is the
column on the far left.
data is the...How to sort cells by dates when some cells are links to other cell
When try to sort by date column, with some cells having dates created by
reference/link to anther cell not in the same column, the sorted cell/s
changes to a reference error (guess thats what Id call it, not sure what the
official name is) =#REF!
Any idea how to sort referenced date cells without getting that error ?
Thank you for your help.
Make the date reference absolute: =$A$10
Microsoft Excel MVP
"akm" <email@example.com> wrote in message
> When try to sort ...custom format changes to special
I need to regularly reformat numbers in cells into a custom number
(000000000). I create a formatted cell and place a number 1 in it
(000000001). I thne copy it and use paste special multiply. This has work
great reformatting cells until this week. I tried it and now the cell
formats itself into Special Social Security (Portuguese) format. It changes
all of the cells even the orginal. Any ideas? I can not change it back. I
need the formatting to help with vlookups
...please help: find nonblank cell in group of cells...
Please help. How can I find nonblank cell in group of cells (one row)
and than get value from cell in that column and specified row and
return in another cell.
And repeat that in many (about 300 rows)?
|Filename: book1.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=5162 |
------------------------------------------------------------------------...Cell formatting protection
Here is my delima. I have excel 2002 and i have a sheet that i do no
want anyone editing without entering in a password to unprotect it.
Currently i have every cell on the sheet locked. and I wanted to setu
some advanced permissions so that certain users dont even have t
unlock the file that part works great. This sheet background color
are used to indicate if the data has been processed or not ( not m
idea to do it that way ) so i want to make it so that if some rando
person that is not specified in my permissions list tries to change th
background color it wont let him without typing in a...find cell value
i have this formula that identifies the last column with data in it.
this works a treat
what i would like to do is us the result of this and subtract 3 column
ADDRESS(35,MATCH(6.022*10^23,33:33)) = N35
i would like to have a formula that takes N35 and sub tracts 3
columns fro it giving K33
i then intend making that my range
ie k12:n33 and copy the data else where to work on
If =ADDRESS(35,MATCH(6.022*10^23,33:33)) returns $N$35
then =ADDRESS(35,MATCH(6.022*10^23,33:33)-3) will return $K$35...selecting cells #3
When working with a large group of cells, I find it easier to click the top
cell, scroll to the bottom, and click the last cell while holding the shift
key down. The problem is, when I want to select a second column of data, I
have to select the top cell using the control key, the pull down the cursor
manually. Is there a way to select 2 or more non-adjacent columns without
having to scroll down using the mouse?
Nevermind - figured it out myself. The easiest things are often so obvious
they are hard to see.
> When working with a large group of cells, I...dang cell format
I'm trying to set an Excel cell format like I can do in Access to prevent
Cell input is 1234-123-1234-123, that's to say, non-sequential numbering but
using 4 numbers dash, then 3 numbers dash, 4 numbers dash, then 3 numbers.
If the entry is wrong, go to halt/stop.
What hasn't worked;
1. conditional format
2. Format, cells, 'custom'
I've tried ####-###-####-### under custom but it fails because it
allows more then 4 numbers at the very start.
I "can" get, 123456-789-1234-234 The last 3 sequences work but
not the 1st seque...Merging cells in a shared workbook......
I ran into something I haven't as of yet and am wondering
if it is due to working within a shared workbook.
I am unable to merge cells as the option to do so is
greyed out. The sheet is not protected by the protect
Forgive me if this has been asked but since I access this
from work, I have to use the web browser and not an
actual reader and I haven't found the search function (if
there is one).
Thanks for any and all replies. :)
It is due to being shared.
>I ran into something I haven't as of yet ...Formatting cells with numbers and text
I have a column of numbers with text; i.e., 23L252350, 06K100997, 50J304888,
etc. These numbers and text need to have a hyphen inserted after the first
two numbers/characters and the next four numbers/characters, i.e.,
23-L252-350, 06-K100-997, 50-J304-888. I tried using
Format-Cells-Number-Custom, but cannot seem to get it to work since the 3rd
character is usually (but not always) text.
Any assistance would be greatly appreciated. Thank you.
change the cell # to fit your needs
"Dazed and...Outlook XP. incoming mail now only in text format
Something happened yesterday that had all of my incoming
mail appear in text format rather than the HTML (layed
out with photos and art included as it isued to).
I cannot think of anything that I may have done to affect
this. Does anyone know of a setting change that may
...How do I select a cell 5 cells from the active cell?
seems simple enough, but i can't figure it out.
i am at a particular random location, and I need to select the cell 5
Public Sub SelectCell5RowsFromActiveCell()
If .Row <= Rows.Count - 5 Then _
In article <firstname.lastname@example.org>,
JasonK <JasonK@aol.com> wrote:
> seems simple enough, but i can't figure it out.
> i am at a particular random location, and I need to select the cell 5
> rows over....Sum variable range of column entries in offset cell #2
Many thanks those of you who tried to help with this question last week, but
my simplified example wasn't good enough. I'll ask the question again but
with a more detailed explanantion. Sorry for any induced headaches in advance.
I have 900 lines of data (A3:AT902). Below this I am using an INDIRECT
formula to pull down (copy) certain lines from this mass of data (depending
on certain entries within the data) into 60 separate tables below. First
table (A915:AT943), second table (A951:AT979), etc. Much of the lines of data
copied down into my 60 tables is numeric, and o...Blank Cell vs Cell equal to Zero (0)
When creating a formula, is there a way to differentiate between a blank
cell and a cell that's equal to 0?
Here's my formula:
=IF('2005 Sales'!O12>0,'2005 Sales'!O12,"")
I wanted to change this to if O12 is greater than or equal to 0, then
return O12, but I want it to return ("") if the contents of O12 is blank.
Is there a way to do this? ...
What happens if O12 is *less* then zero?
This will leave O12 blank ( "" ) if less then zero:
=========...Cell formatting in Excel Pivot Diagram
We use Excel 2000 to display a Pivot diagram. The datasource is an olap cube
on a SQL server 2000
I would like to format the column headings of the pivot diagram so they are
displayed vertically. In excel I go to FORMAT menu, then FORMAT CELLs, then
ALIGNMENT. Here I can set the orientation of the header cells to any angle I
want (90 degrees).
The pivot diagram allows the user to click on a header cell (year) to
display the next more detailed level (week number). We have 3 such levels.
I expand all levels of the header cells and set the orientation to 90
degrees. After a collapse of the he...