I have a series of formulas that creates English Language labels out
of a variety of VLOOKUP's and the ever helpful & sign.
Basically, I used the VLOOKUPS to get "Big", "Bag", "Delicious", and
"Apples" in cells A1-D1, respectively.
Then, in E1, I've got
=A1&" "&B1&" of "&C1&" "&D1
so that it comes out
Big Bag of Delicious Apples
In E2 it says Small Bundle of Tart Grapes and E3 says Big Bundle of
Tart Apples, etc.
What I'd like to have, though, is some sort of formatting set u...delete row if 2 cells are empty
Hello I get a daily report and I have to delete the entire row if cell AA
and Cell AB are empty. staring from row 4 till the end. I have been doing
this manually but is there a VBA code that would make this faster. thanks
Unless aa a/o bb populated with a space bar this should do it.
lr = Cells.find(What:="*", After:=[A1], _
For i = lr To 4 Step -1
If Cells(i, "aa") = "" And Cells(i, "ab") = "" Then Rows(i).Delete
...Correcting a typo in large value list
I'm new to Project so this may be simple. I have several project files that
the value list for a Text column was incorrectly entered. Is there a way I
can add a string prefix to all 200+ values in the list? For example, the
data I have in the Text13 column is "1.3.1" and I want "ABC.1.3.1". I've
tried to do a formula but I keep getting a recursive error since I need the
new value to be in Text13 plus I need to keep the ability to be able to
select a new value from the list (in case something changes). If it was
Excel, this would be simple becaus...Too many different cell format #3
I have a quite big excel and i am trying to make some changes in format and
i receive the message "Too many different cell format"
Probably i have many different cell formats.
From where i can set up the format in order to know what must be the format
of the excel file?
I need away to identify the format from all sheets in order to give to the
all sheets as common format as i can.
XL: Error Message: Too Many Different Cell Formats
I'd remove some of the formatting. (maybe format per colu...Changing the number format
I am using Excel 2000. I am running Excel with the regional setting
set to Germany. So, Excel interprets "," as a decimal point. What
want to do is make one particular workbook interpret "." as the decima
point. However, I cannot change the regional settings on my PC.
Is there any way to tell Excel 2000 to interpret "." as the decima
point. I went to "Format", "Cells", "Number". However, I cannot chang
the "," to a ".".
I hope I am clear enough. Please let me know if I need to clarify a bi
T...Select Cells Macro
I don't like to take my hands off the keyboard, even to reach for the
cursor pad. So I have four macros: Up, Down, Right, and Left assigned
to Ctrl-k, Ctrl-i, Ctrl-l, and Ctrl-j.
I'd like to be able to select cells the same way, the equivalent of
Shift-Up, Shift-Down, Shift-Right, and Shift-Left. When I record a
macro to do Shift-Right for example, and assign it to Ctrl-Shift-l, all
it will do is select one cell to the right plus the initial cell.
I want it to continue to select to the right if I press Ctrl-Shift-l
' SelRight Macro
'..."Too many different cell formats" is preventing file from opening
An Excel 2000 workbook saved successfully but when attempting to open it
again I receive the message, "Too many different cell formats."
I have since learned that a maximum of 4000 format combinations are allowed
in a workbook. I've also learned how to correct the problem. Unfortunately,
Microsoft's instructions don't address how to get the file to open in order
to carry out the corrective actions necessary.
Any ideas about getting the file open?
A recent post from Dave Peterson on this subject..........
Saved from a previous post:
XL: Error Message: Too Ma...Copying data from cells A1,B1,C1 on tab1 to tab2
I want to copy data from cells A, B and C on Tab 1 of my workbook to other
tabs in my workbook dependant on which option is selected from a dropdown
list in cell D of Tab1
I have 'company name' in cell A, 'Account Number' in Cell B and 'Sales' in
cell C on tab 1, I then select 'Blue' from a dropdown list in cell D on tab
1. I then want excel to copy the data in cells A, B and C, on tab 1 into
another tab in the same workbook named 'Blue'.
can anybody help please????
Select the sheet tab which you want to wor...Format #3
I am trying to take a column on a excel spreadsheet and transfer it to
notepad or a general text without it changing all format. How do I do this?
What happens when you select the column, edit|copy
and paste into NotePad?
> I am trying to take a column on a excel spreadsheet and transfer it to
> notepad or a general text without it changing all format. How do I do this?
It takes everything with it, the cells, etc and changes the format so I have
words all over the page. Does this make sense?
"Dave Peterson" wrote:
> What happ...How to count merged cells
Can someone help me in developing a formula that will:
1. Count how many merged cells in a column that are created from 2 cells
AND contain both text and numbers?
2. Count how many merged cells in a column that are created from 3 cells
AND contain both text and numbers?
3. Council how may merged cells in a column that are blank.
If you look in the archives of this group you will see countless examples of
reasons not to use merged cells.
To concatenate cells is fine, to merge is asking for trouble.
"Excel Ella" <ExcelElla@discussions.microsof...Cell #2
I want to protect a cell after entering formula. But at the same time the entire worksheet except that cell should be unprotected/Open for edit.
Kindly suggest solution for the same.
It's easy. Select all the cells you want to allow
access, Ctl-click on the cell you wish to protect.
Go to menu, format>cells choose Protection,
uncheck "locked". and OK.
>I want to protect a cell after entering formula. But at
the same time the entire worksheet except that cell
should be unprotected/Open for edit.
>Ki...Error in AVERAGE(IF...) formula; involves Zero Value and Blank Cells
I am getting an error in this formula:
Excel states that a value used in the formula is of the wrong data
type, and the calculation steps underlines the first ($Y13:AH13 and
results in #Value!<>0.
The long-hand way of typing it is (up to column AG):
The data values, which are student scores on assignments, are
currently ...Have excel export cell format other than text; need time and date not number value
First thank you for taking the time to read about my problem. Secondly
I'm not an xml or excel hotshot. I can barely write a macro.
My problem is this.
When exporting my xml data, the time/date values lose their readable
format. A time of 01:30 am, for example, becomes something like
0.001353 for example.
What do I need to do to keep the same format as is in Excel? It would
sure be great if you could help.
...Formatting phone numbers
Operating System: Mac OS X 10.5 (Leopard)
Hi all, <br><br>I am in the middle of a project and I was hoping someone could help me. <br><br>I am merging mailing lists which contain phone numbers. Some of the phone numbers are formatted (800) 000-0000 and some are formatted 800-000-0000. I have to choose one of the formats. <br><br>Is there a way to change the formats on all instead of doing it manually? <br><br>Any help would be appreciated. <br><br>Thank you, Bonnie
highlight the column where the ...best formula for finding a cell value?
I have created a matrix which has three columns: "north", "south" and
"west". The rows below the columns are sequenced by their respective
row number and below each column has different values.
My questions is: If I create three cells- "Direction","Row" and
"Value", how can I populate the intersecting value in "Value" by when I
input in "Direction" and "Row"??
Example: Direction = north; Row = 2; Value = [would this be a DGET or
Thanks in advance for your responses!!
...Conditional Datapoint Formatting
I'm graphing time series data values in weekly buckets. I'd like to d
2 things that involve conditional formatting of a specific datapoin
within the series.
-I'd like the datapoint of the most recent week to have a differen
-I'd like the highest/lowest datapoints in the series to have
Is this possible & how
Message posted from http://www.ExcelForum.com
I show a couple techniques you might find useful:
http://peltiertech.com/Excel/Charts/FormatMinMax.ht...Conditional Formatting based on multiple criteria
I want to conditionally format blank cells in column B to blue when the
value in corresponding cells in column A contain the word "Custom".
However, once I place a value in a blank highlighted cell in column B I
then want the formatting removed.
Row 2 - - Cell A = "Custom" - - Cell B is blank - - therefore cell B2
Row 2 - - I enter a value in cell B2 - - the blue formatting goes away.
Use a formula of
(replace xxxx in the email address with gmail if mailing direct)
<twlo...Inserting a new column affects conditional formatting
I have got conditional formatting setup on a sheet that highlights a
row based on the value in two columns (K and L). The formula for this
I have got 6 varieties of this, and it all works well and highlights
each row correctly.
However, I need to insert a new column before K - and when I do I lose
the conditional formatting. I have tried manually altering the formula
=AND(INDIRECT("M"&ROW())="No", INDIRECT("L"&ROW())="Sent&quo...Excel: with an if condition display a row of cell if true
I am a relatively new user and I was asked to set up a contact list for my
office. I would like to have one master list on the first worksheet and then
kind of query different sections into new worksheets. Is this possible?
For example in the main worksheet i would have name, region and supervisor.
In the second worksheet I would want only Peel Region enteries to be
The main goal is: If a change something on the first worksheet I would like
the other worksheets altered automatically.
PLEASE HELP ME.
Copy the data that you want from the main worksheet and then paste...Ranking a column with some cells having #value!
I have a spreadsheet that has 20 rows. Depending on the input, not all rows
will be entered with data. Those without data will have #value!.
Problem is how can I formulate the ranking so that I can always see the
ranking of the 20 rows and the ranking will auto exclude those rows having
Why don’t you post your formula which is resulting #value!. Otherwise try
=IF(ISERROR(Your Formula),0,Your Formula)
Which will get you the result of 0 instead of #value!.
Remember to Click Yes, if this post helps!
---...What would the formula be for counting highlighted cells in Excel.
Does anyone know how you can count the number of cells that are highlighted
Try something like the following:
Dim CellCount As Long
CellCount = Selection.Cells.Count
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"chipsters" <firstname.lastname@example.org> wrote in
> Does anyone know how you can count the number of cells that are
If by hightlighted means selected
ms = 0
For Each c In Selection
m...How do I send a publisher document in HTML format
We are attempting to provide a brochure to our publicist in the requested
HTML format so she can use the brochure we created in an email camoaign.
Hi Robert Mitchell (Robert Mitchell@discussions.microsoft.com),
in the newsgroups
|| We are attempting to provide a brochure to our publicist in the
|| requested HTML format so she can use the brochure we created in an
|| email camoaign.
Which version of Publisher are you using?
Microsoft Publisher MVP
This posting is provided "AS IS" with no warranties, and
confers no rights...Custom formatting to force a decimal place
Oh wise ones,
I have some custom cell formatting that prefixes some
text to a value. I would like to force 1 decimal place even if it is zero.
Currently if it is zero it just displays the whole number. Any suggestions?
custom format is "LFE Scan="###.# "LBS"
Try: "LFE Scan="0.0 "LBS"
GMT+8, 1� 22' N 103� 45' E
"Mike K" <MikeK@discussions.microsoft.com> wrote in message
news:8E0D35E4-4023-4A10-AB8D-5599C923C...How can we find out different format combination in a worksheet?
Excel support 4000 different format combination in a excel sheet then
Is there any way to find out different format combincation in a worksheet.
...How do I change the default cell format?
I always have to change the cell format (Alignment, wrap text, etc) because I
don't like the default settings. Could anyone tell me where can I change
You can create a new workbook and save it as book.xlt in your XLStart folder.
For each sheet in that workbook, format the sheets the way you want.
When you click on the New icon on the standard toolbar, your new workbook will
inherit all the settings from this template workbook.
You can also create a new workbook and save it as sheet.xlt (also saved to your
XLStart folder). Then any worksheet yo...