Vlookup but two rows below
I have a requirement to extract some data which under normal circumstances
would be ease as the formula =VLOOKUP(C2,$R$12:$T$1010,3,0) would return the
correct result if R12 contained 301 and T12 contained XYZ i.e. XYZ would be
returned. However, I want to return the contents of cell T14 which is
always 2 rows below the match in column R.
Appreciate any help.
...freezing row and column headings
This should be simple, but the words I want to use don't help in HELP
All I want to do is make the column headings in the first few rows stay on the screen when I scroll down and make the row headings at the left stay put when I scroll right
In ((Lotus 1-2-3)) this was called freeze titles
Thanks for your help, temayer
Thanks for your reply, but I don't think that is it, because that seems to only work in one direction and there doesn't seem to be a way to tell it,"Don't scroll the top three lines or the first two columns, as appropriate, so I can see what the cell i...Column To Rows
I often get lists of contacts that are generated in one long column. They are
often may be 4 or 5 rows per record. In order to be able to use the data I
need to transpose the rows in each record (one column) to new columns. It may
include Company Name in Row A1 and contact name in Row B1 and perhaps Phone
or Web Site in C1 etc. The records are always the same amount of rows. I have
tried copy, transpose, and text to rows and anything else I can think of. Can
anyone make a suggestion ?? Thanks in advance Tony Pass
If desired, send your file to my address below. I will on...Picture numbering past 65536?
I am using an excel spreadsheet as a template for webpages. For each page I
insert 5 pictures which I create elsewhere. As each picture is inserted,
Excel gives it a number. When you select the graphic you see the number in
the Name box.
The problem I have is that when the number of Pictures gets to 65536, VBA
gives an error message when trying to insert the next picture. The excel
file keeps a running total of the pictures inserted over time i.e. it doesn't
go back to zero when you close the file. Does anyone know how to set this
back to zero?
Post your VBA code so we can see ...More on Excel row height units?????
The row height is indeed in points but then what measure
is the font height????? -- read on...
Try this. Put a single 48 point Capital letter in a cell
and autofit the row height. Check the measurement (Excel
should report back that it's 90(120 pixels)). Now 1 pica
point = 1/72 inch, so 90 points x 1/72 = 1.25 inches. Use
MS Draw to create a circle 1.25 inches in diameter -- this
circle *will* just fit in the row and therefore, the row
height is in pica points.
But, our 48 point letter should be 48 x 1/72 = 0.67 inches
tall. It is not however, it is approx. 0.72 inches high on
t...Negative numbers turn positive automatically on data entry
When I try to enter a negative number into excel, the number turns positive
as soon as I enter it. I have tried deleting book1.xlt to remove any bogus
formatting but this problem appears to be some sort of default setting that I
do not know about.
After the number turns positive, what do you see in the formula bar for that
If it still shows negative there, then maybe it's just a formatting problem.
Format|cells|number tab|make it general.
If it really shows positive, then it sounds like you may have a macro that's
Can you reopen your workbook with macros disa...Averaging numbers but ignoring < and
I have a column of data as illustrated below than contains numbers, blank
cells, dashes and < entries. Is it possible to average only the numbers
ensuring that the divsor is the number of cells that contain number entreis
rather than all cells containing an entry (eg for below example answer should
On Wed, 23 Feb 2005 06:29:03 -0800, KIM <KIM@discussions.microsoft.com> wrote:
>I have a column of data as illustrated below than contains numbers, blank
>cells, dashes and < entries. Is it possible to average only ...Average of Averages
Another seemingly simple process which I can't figure. :)
I have a list of averages, and I want to get the average of them.
I need a formula to average something like the above.
=AVERAGE(A1:A4) is one possible solution.
Hope it helps.
goober's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19838
View this thread: http://www.excelforum.com/showthread.php?threadid=513937
It really depen...Max rows reached?
I'm trying to import the results from a test that I have
run. The results have 500000 data points and excel will
only let my import 65536 of them. Is there a way around
this or should I be looking for another application to
no this is the maximum for one sheet.
> I'm trying to import the results from a test that I have
> run. The results have 500000 data points and excel will
> only let my import 65536 of them. Is there a way around
> this or should I be looking for another app...averaging less than values
How do I average a column of numbers where less than values are calculated as
In the average I want the<2 to = 2. So the answer should be 3
Assuming source data as posted in A2:A7
Paste this in say, B2,
then press CTRL+SHIFT+ENTER to array-enter the formula:
Adapt the ranges to suit
Downloads:17,500 Files:358 Subscribers:55
> How do I average ...outline numbered list not auto-numbering
I have 2003 version of Word at work. I am trying to create an outline
numbered list with 3 levels. When I hit the enter button after typing the
text for an item, word does not seem to recognize that it should continue the
list. The next line just has normal formatting, and I'm forced to use the
format painter brush to copy a previous item from the list, and then Word
seems to recognize it as part of the list. I have verified the "Apply
Automatic Numbered Lists" is checked in the Tools/Autocorrect
Options/Autoformat as you type menu.
An observation about Word ...Custom fields showing as Number type in Crystal but Picklist in CRM
I've just installed Crystal Reports (9.2.2) and connected it to my CRM
1.2 database. I notice that custom fields I've added as Picklist type
fields in CRM are appearing as Number type fields in Crystal, and
therefore showing a number (eg 3) rather than the expected picklist
item (eg 'Mobile'). Any ideas?
Picklist values are stored in the StringMap table
"Roger Strain" <email@example.com> wrote in message
> I've just installed Crystal Reports (9.2.2) and connected it to my CRM
> 1.2 da...Sort with header rows
Using Excel 2000
I have a table to sort, but the top three rows are all header rows. This
prevents me from using the Data | Sort option because the "header row"
option only eliminates the first row from the sort.
All I know to do is select all the rows in the table that need to be sorted
and then use the Data | Sort option and check the "no header row" box. This
gets cumbersome when there are hundreds of rows in the table to include in
the sort and only three rows in the table to exclude from the sort.
Add in an empty column into your data and ta...pivot table error (25000 rows!!)
I am trying to run a pivot table on a file containing 25000 rows. I have
5 duplicate (identical structure) files. The pivot works on each but
one. On this file I get an error saying it has too many columns or rows
in the pivot table. Drag at least one column or row off the table...
Does anybody know what the reason for this message is, are their limits
to rows or columns? The identical table works on the other files,
therefore this message is so strange.
Who has an idea?
Craig_Richards's Profil...grouping highlighted rows
I have a spreadsheet consisting of 100 rows, with 11 columns in each.
Different rows are highlighted via "conditional formatting" according to
number ranges in some of the cells. I wish to have the highlighted rows
move to the top of the list, that is if conditions are met to highlight a
row that row would move to the top of the spreadsheet joining all other
highlighted rows. I would want the highlighted rows to be sorted
alphabetically, followed by the non-highlighted rows sorted alphabetically.
Typically there are approximately 15 to 30 out of 100 rows that are
highlighte...How can I get current cell row number
I need to reference the row value of the current cell
in a worksheet function: = row()
in VBA: activecell.row
Hope this helps
> I need to reference the row value of the current cell
...sorting names alphabetically when names may start with numbers
Anyway to sort names alphabetically when names may start with
I have a list of name of chemicals. The names may start with
numbers, like 1,2-dichlorobutane. Right now, if I sort the list,
the names start with numbers would be on the top. How do I make it
ignore nubmers and sort alphabetically?
> I have a list of name of chemicals. The names may start with
> numbers, like 1,2-dichlorobutane. Right now, if I sort the list,
> the names start with numbers would be on the top. How do I make it
> ignore nubmers and sort alphabetically?
I'm not aware of any ...Averages
I have an interesting situation. I basically inherited an older
database that is used to enter dates, usage and charges for accounts.
The situation is that we are attempting to do averages filtered by
account, and averaged against the number of days that data has been
entered. So, for example, we had 7 months of data entered, our query
should total the number of days in the seven months (x), then average
that with the total usage number (y). This data currently resides in
the same table labeled "data" in the form of from and to dates
(6/1/1999 to 7/1/1999) and usage (32...column headings in numbers vs letters
For some reason all my excel files now display columns as numbers rather than
letters. How can I change back to letters? Why are all my old files also
now reading in numbered columns?
This is a setting of Excel, not of your files.
Tools>Options>General, uncheck R1C1 Reference style
Microsoft MVP - Excel
"garyflood" <firstname.lastname@example.org> wrote in message
> For some reason all my excel files now display columns as numbers rather
> letters. How...Extra row in CTE
I have a table (#qed_missing_quarters) with the following rows in it:
1 2002-09-30 00:00:00.000
2 2002-12-31 00:00:00.000
3 2003-03-31 00:00:00.000
4 2003-06-30 00:00:00.000
5 2003-09-30 00:00:00.000
And I have the following code as a test to traverse across the table:
;WITH qed_missing_values(row_num, quarter_end_date)
SELECT row_num AS [row_num], quarter_end_date AS [quarter_end_date]
FROM #qed_missing_quarters WHERE row_num = 1
SELECT a.row_num + 1, a.quarter_end_date
FROM #qed_missing_quarters AS a
INNER JOIN ...hidden value in each cell or in each row in Excel
I need to store a two information in one cell with one value as hidden
,kindly reply if any one know the way
without creating hidden rows or columns or worksheet
The need is to store two information in one cell,with one value in
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/
Using comments is one option.
>I need to store a two information in one cell with one
value as hidden
>,kindly reply if any one kn...Average Lead Time
Does anybody know if the average lead time in Item Vendor Maintenance counts
weekends, or only business days?
The average lead time is calcualted as follows: Enter the average number of
days that pass between the time you place an order with this vendor and the
time you receive the order. If you're using Purchase Order Processing, this
field will be updated each time a shipment or shipment/invoice is posted in
Purchase Order Processing. The average lead time is calculated using the
following formula: [(Number of Receipts) * (Average Lead Time) + (Received
Date - Ordered Date)] / (...How to join auto number and letters
I have a form for issuing a letter number for our letter book. In this form
, I have a text box with auto number . what I need is to issue auto number
start with letters. For example: APP&P1, which 1 is the auto number and it is
start with “APP&P”. once the user complete the letter subject, letter receive
and the sender , will receive a control number or letter number.
What about having two fields, where the first field contains the letter
prefix and the second field contains the number? You could display the two
fields in a calculated control combining the data.
I have a formula in a cell that takes the average Sales of
the 12 rows (for Jan to Dec)
like so: =Average(A1:A12)The problem is that for example,
since therea are no sales figures from August to Dec, the
average is underestimated because it averages it over the
entire range of cells (though they are zero from Augus to
How can I make this Average function to be smarter and
only average those months that the sales is not zero
(August to Dec is Not BLANK but its zero)
Average is a very simple thing: SUM/COUNT. So
should do for all years with 12 mont...averages
Trying to work out the best way to get an average answer based on a
set of answers
The answer will be 1 - 10 across a number of questions
B C D E F
Answer Q1 Q2 Q3 Q4
1 1 1 3 3
2 1 0 1 2
3 0 1 3 3
4 1 0 1 1
5 1 1 6 7
6 2 2 5 2
7 2 3 3 4
8 2 1 7 6
9 1 2 1 3
10 2 2 8 7
Total 13 13 38 38
Average ? ? ? ?
What is the best way to get the average response to each question.
i've tried =SUMPRODUCT($B$2:$B$11,C2:C11)/SUM(C2:C11) but not sure
this is right....