Is this an Index/Match formula?
I have 2 tabs of data.
The first tab has info by product and its qualifying vendors and
non-qualifying vendors (the vendors are in seperate columns)
ColumnA ColumnB ColumnsC ColumnD ColumnE
Product X Vendor1 Vendor2 Vendor3 Vendor4
Column A will always be unique
Columns B and C are qualifying vendors and Column D and E are non-qualifying.
In the second tab I have the following:
What formula do I use to tell me if the vendor is Qualifying (Q) or
Non-Qualifying (N) or blank if the vendor is not included in the li...copy rows in range if data in first cell
I need to select from a range of rows - only those having data (formula
result) in the first cell. There will be some rows without data in the first
cell but they would contain a formula - these shoudl not be selected for copy.
I want to select and copy all the rows having data in the first cell in
order to paste these rows as values into a separate sheet in another workbook.
Try some code like the following:
Dim Destination As Range
Dim RowNumber As Long
Set Destination = Worksheets(2).Range("A1")
For RowNumber = 1 To 10 ...Format when viewing formulas
I'm trying to get two prints of the same document: one showing absolute
numbers and the other showing the formulas.
When I'm pressing ctrl+~ to show functions for my 2nd print, the format is
changing. The cells are getting too wide, so I end up getting only the left
half of the document in print preview. How do I change the document to show
functions with the format staying the same so I can print it??? Thanks.
Page Setup/Page/Fit to 1 page(s) wide.
In article <D2EAE056-FC1E-4F40-BA48-974B191C4B8D@microsoft.com>,
"bruno" <email@example.com...A formula problem.
Hi all Im new to the forum. I have 2 questions re excel 97. I hav
created a spread sheet for a friends lawncare business. It has
columns. The first one is a description of the service. The second on
is the quantity of service. The third one is the unit price. And th
fourth one is the total price. I have it set up so that the fourt
column multiplies the coulmn with the quantity. It works fine excep
that it prints a $0.00 in the total column if there is nothing in th
quintity column. Is there a formula that would allow me to make it s
if there is no number in the quantity column , it would ju...Help with If formula
This is my formula which works ok.
What I am trying to do is extend this and in the same formula have
D6,D7,D8,D9,D10 included,ie if any are greater than D16 then "over"
but I get to many arguements.
I have tried D6:D7<D16 and a couple of other things but cant get it to work.
"ss" <firstname.lastname@example.org> wrote:
> This is my formula which works ok.
> What I am trying to do is extend this and in the same
> formula have D6,D7,D8,D9,D10 included,ie if any are
> ...moving from cell to cell #2
When I first start up excel and click on a cell, it will not let me release
that cell. It's like its stuck on there. It will high light every cell, but
it won't let go of the very first cell, and I can't use any menus after
This may help:
> When I first start up excel and click on a cell, it will not let me release
> that cell. It's like ...Array Function to do a Sum of VLookUps that translate a letter gra
Suppose I have letter grade data in a range of cells, B12:D12 as follows
A-, B-, B+/A-
I have another range of cells, named GradeList that gives numerical
equivalents for each of these letter grades. I want a function that will look
up each of these grades in the table, and return their sum, average etc.
I tried the following as an array formula:
And it didn't work; looks like it just returns the value of the lookup of B12.
Actually, I want this formula to sum the highest N grade values; I know how
to use an array formula to ...How do I average a formula without calculating zero values?
I want to average a column, except for the cells that have zero as a value.
How do I do this?
which is an array formula so commit with Ctrl-Shift-Enter.
"KMHarpe" <KMHarpe@discussions.microsoft.com> wrote in message
> I want to average a column, except for the cells that have zero as a
> How do I do this?
...Send the Document, may differ some place due to cell contents...
I think subject wasn't so good that tell you what i wanted to do...
I have a text that is about an a4 page in Word.
I have an Database of about 150 firms. And two colums; Company name &
Company name Producktivety
And so goes.
And the text is like;
Company name: a
Company productivety: 90
-..."grep" the formulas?
Is there some way to ask Excel to identify all the cells that contain some
particular content in the formulas themselves? For example, I'm finding that
the MROUND function is causing some trouble, so I'd like to find every place
in a very large workbook where "MROUND" occurs in a formula and rewrite that
formula. I've run into this before, where I've wanted to find every formula
that contained some particular character sequence, and some of the formulas
are very long and very numerous. What I'd like to be able to do is "grep"
the formulas and highlight t...long text not visible in the cell of excel
Operating System: Mac OS X 10.6 (Snow Leopard)
when the number of words get beyond a certain (a small) amount, the text suddently becomes unreadable, turning into the #####. the formatting has been set to "wrap" the text. was there some setting i should change? <br><br>the following text is ok: <br><br>"2009 deficit is $1.4tn, an all time record in dollar terms. It was $958bn above the 2008 deficit, the previous record holder. It's 9.9%gdp. The record percentage belongs to 1945 of wwii-era 21.5% <br&g...DTD translation to Schema
I need to write a DTD to XML Schema translator in C#. Is there some source
code available I can start from? It does not have to be C#, it can be C, C++
> I need to write a DTD to XML Schema translator in C#. Is there some source
> code available I can start from? It does not have to be C#, it can be C, C++
> or Java.
There is a perl script at W3C site that does the job -
Also in .NET you can make use of Chris Lovett's Dtd2Xsd tool -
http://www.gotdotnet.com/Community/UserSamples/Details....Some help in refernceing some cells
I need help.
I have a long list of numbers, and in each cell next to each number,
want to display a color if that number appeared in the last 42 numbers
So if it did - color "Green"
and if not - color " RED"
PLEASE HELP!! Anybody!!
kylekoopman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2736
View this thread: http://www.excelforum.com/showthread.php?threadid=46869
Use conditional formatting.
Select row 43 down and use this form...CELL FORMATTING #9
WHY IS MY CELL FORMAT AT WORK DISPLAYED PROPERLY
-1,500.OO IS DISPLAYED AS (1,500.00)
AND WHEN i DO IT AT HOME Ii CANNOT GET
THE CLOSEST I GOT IS -1,500.00
is there a diffence between the formatting cell options
between Excel's programs..
Excel takes it from your Windows Regional options.
Start > Settings > Control Panel > Regional options
Change the negative number format
You'll find that you now have () options.
Norman Harker MVP (Excel)
Excel and Word Function Lists (Classific...Adaptation Of A Formula
I have the following formula in a cell.
This gives me the number of how many Bank Holidays have passed in this tax
(courtesy of N. Harkawat)
In any one tax year there can be up to 10 Bank Holidays. (This year there
are only 6) I would like the cell to read "1 of 6", "2 of 6" etc, but in an
On a separate sheet, I could put in A1 to J1 for instance, the dates of the
Bank Holidays in the current tax year. In cells B1 to J1 I could have the
numbers 1 to 10.
(Or any other way o...hyperlink formula
=HYPERLINK("#a123"; "Next week")
The first week is placed on a123, then the second 41 rows under at
a164, and then third further 41 rows down at 205. Is there a formula I
could use in this case.
kimare's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16501
View this thread: http://www.excelforum.com/showthread.php?threadid=320384
=HYPERLINK("#" & ADDRESS(ROW()+41,1,4),"next week")
Will allow you to put the formula on any cell in the...#VALUE! on correct formulas
I am using 'vlookup' and 'match' functions that result in #VALUE!, but when
I hit function wizard, it shows the correct number value I am looking for
and expecting in the result area. Apparently my syntax, etc. is accurate.
Why, then, does #VALUE! show up when everything is ok? thanks, mike allen
mike allen wrote...
>I am using 'vlookup' and 'match' functions that result in #VALUE!, but
>I hit function wizard, it shows the correct number value I am looking
>and expecting in the result area. Apparently my syntax, etc. is
>...Unlocked cell formatting
I have some cells locked & sheet protected. After the sheet is
protected, all formatting tools get inoperative. I want the users to
have choice for formatting unlocked cells. How to solve this?
Message posted from http://www.ExcelForum.com/
I hope this make sense.
If you go in the menu to TOOLS -> PROTECTION --> PROTECT SHEET
Then you have the chance to allow user to format Cells, Columns and
Rows by ticking in the small square boxes that are right below the
space where you write your password.
So, by ticking in these options you choose what level of protection you
wa...How to Clear autoshapes out of a cell
I have a cell that has 10 autoshape squares in it. how can i just clear the
cell of them?
its part of about 100 other cells full of these shapes. the squares are tiny
and to click on each one to delete or to group them and then delete is too
time consuming. is there a better way? deleting the cell causes issues with
the rest of the form, with me having to insert a new cell in its place..
I also have another Q if your a cell making wizard!
this is basically an inventory worksheet for bunch of toys we use as decor.
the stock on hand changes daily
its set up as follows
Item number: item...Shading cell in custom colour
Can I shade a cell in a custom colour, or is there a bigger choice than
the default choices (I want a lighter grey than the 25% grey)
Turnipboy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24527
View this thread: http://www.excelforum.com/showthread.php?threadid=486869
XL can display only the 56 colors in the color palette. However, if you
open the palette (Tools/Options/Color) and click Modify, you can replace
any of the colors with a custom color.
In articl...Pasting into merged cells
I have a merged cell in which I want to paste text from another cell. Every
time I try get an error message saying that the cellsI have to unmerge the
merged cells first, paste, and them remerge. Is there any way to avoid doing
this? It is a real pain.
If you copy only the text from the formula bar of the first cell you can
paste it into the formula bar of the second cell without going throught the
Alternately you could just put a formula in the merged cell referencing the
cell you want to copy from. You can than Copy-PasteSpecial-Values in the
merged ...How do I add a formula?
How do I add a formula to find 15% of an amount? The amounts are listed in
column D. I want to see 15% in Column E
> How do I add a formula to find 15% of an amount? The amounts are listed in
> column D. I want to see 15% in Column E
On the off-chance that the 15% is included in the amount, you can use
(replace somewhere in email address with gmail if mailing direct)
"Jaleel" <Jaleel@discussions.microsoft.com> wrote in message
news:717ABC7C-2FAD-4C35-9759-CFA83A86844...Count numbers formed from another formula
Hi. I'm trying to count a set of #s that is formed by another formula. For
example, I have a list of numbers from c1-c6 which all have formulas in the
cell which creates that number. When I try to do functions counting the #s,
etc., it will not bring back a value. It doesn't want to count what has been
formed from another formula. Please help! Thanks!
Normally, COUNT(C1:C6) will count all of the values in column A, including
the zeros, but will ignore nulls ( "" )..............
Normally, COUNTA(C1:C6) will also include the nulls..........
What is ...cell reference
i have formula in cell A5 and when i double click, it takes me to the
respective cell. my question how can i go back to that cell (A5)
Hit the F5 key / enter A5 / hit OK.
If my comments have helped please hit Yes.
> i have formula in cell A5 and when i double click, it takes me to the
> respective cell. my question how can i go back to that cell (A5)
As ALWAYS, post your code for comments.
Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
If Target.Address <> Range("g...Formula?
I'm trying to add a formula in a cell which will leave
the cell blank if column A is blank. I use the following:
ActiveCell.FormulaR1C1 = "=if(+RC[-10]>0,2.5,"")"
but I get a run-time error (1004). I know the problem is
the quotes in the third argument. I tried replacing the
quotes with zero and it enters zero correctly with no
error. But I need the cell to be blank, not zero, if
column A is blank.
Try four quotes instead of two:
HTH. Best wishes Harald
Followup to newsgroup only please