Referencing merged cells
I'm having trouble referencing a merged cell in another workbook.
Say I merge cells A1 to C1 in workbook 1. When I make this the active cell,
the Name Box says "A1". When I go to another workbook (say, workbook 2), go
to cell B1, type "=" and then go to the merged cell and select it, I get
'workbook1'!$A$1:$C$1 as the formula and, of course, the "#VALUE" error.
I've successfully tried typing "=sum(" without the quotes hen clicking on
the contents of the cell and then adding the ")" and it works O.K. but there
should be an easi...The most occurence of a value in a column
I have 1 to 5 as values in a column. How can I find the number with the
most occurrence in that column?
"cpliu" <chanciusliuDeleteThis@yahoo.com> schrieb im Newsbeitrag
> I have 1 to 5 as values in a column. How can I find the number with
> most occurrence in that column?
Array-entered, meaning press ctrl/shift/enter.
H...Multiple SEARCHes within same TEXT string
I have a column which contains text strings that represent colours.
However, the *actual* colour is often buried in other text i.e.
I have a list of 12 single-word colours i.e. BLACK, and want to identify in
one formula which (if any) of the 12 is buried in the text string (and then
have the formula return that colour)
If I had just 7 colours, I'd just go with nested IFs and SEARCH, but as I
have 12 that is not an option.
Any ideas would be greatly appreciated.
Assume your 12 single-word colours eg: BLACK, WHITE, etc are in a defined
co...Referencing another field if result is N/A
I have a situation where I am referencing data on another workbook. The
reason behind this is the data in Column H can be entered incorrectly and
often is. I am able to prevent this going forward but can not do so for the
items already entered. I enter a formula via VBA, however, sometimes the
result will be #N/A and if so I would prefer to leave the old value and just
highlight it that it needs to be verified by hand.
I am not sure if I can just "leave" the value as it has pretty much already
been replaced. Due to this, I copy the column to a different colum...This forum does have value!
As year end approaches and I am considering upgrading to Money 2005, I found
the message traffic extremely valuable. After this summers fiasco with the
update to MSN Money locking us out of our local data file, I thought, or was
hoping M$ would make an extra effort to make sure that Money 2005 was a
significant improvement and without the assorted problems that I have found
posted here. Some still unresolved.
I have made my decision and I do not care how much work it is going to take
me, bt I am going back to Quicken after giving Money a two year trial. This
program is just not ready f...This workbook is currently referenced by another workbook and cannot be closed.
The message is given when I try to close a workbook / kill
Microsoft Excel. I'm using Microsoft Excel 2002 / XP.
The spreadsheet includes a reference to another sheet that
someone else referenced in another message here.
I put the AddIn sheet in via:
Tools, AddIns..., Analysis ToolPack - VBA.
I've not referenced any function or procedure in the add-in.
I get a Triangular Warning / Error ? It's got a graphic of a Yellow
Triangle with an Exclamation Mark inside.
The subject is the exact text of the warning. A Microsoft Search on
the subject yielded nothing. Google yielded one ...Printing only certain rows
Is there a way (custom view, filter, etc) to only print rows in a print area
that have a value greater than x amount in a certain column? For instance, I
have a worksheet that shows all outstanding accounts. Each account has it's
own row (and the sheet has a couple of header rows). One of the columns
shows the total days overdue the account is. However, I only want to print
the header rows and the subsequent rows with account balances greater than 90
days. It seems incredible there is no easy way to do this, but I have been
searching Excel help without any luck.
I'...Group rows / columns: +/- symbol at top or to the left?
If you group columns or rows, you will see a little symbol "+" or "-"
to indicate the grouped section.
I am wondering if there is a way to tell excel to display the symbol on
the top of the grouped section (instead at the bottom in case of
grouped rows) or to the left (instead to the right in case of grouped
Help greately appreciated!
Data/Group and Outline/Settings...
In article <firstname.lastname@example.org>,
> If you group columns or rows, you will see a little symbol "+" ...how to highlight current row & column automatically by a color
I'm using Excel 2003, can u plz help me out that how to highlight current row
& column with a color automatically to identify my position. Though excel
help us by highlighting column & row number, but i want whole column & row to
be highlighten to better finding my cell position and contents. So When I
change my cell position, the highlighted row & column move appropriately.
Take a look at
In article <4076508D-B500-4491-8BE9-C70A825EEBC2@microsoft.com>,
asif4u <email@example.com> wrote:
> I...Move Column Data into a row
I have text in a column that I want to move into a row instead, is this
possible without typing it all over again?
avidcat's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29659
View this thread: http://www.excelforum.com/showthread.php?threadid=493706
Copy the data and then do a Paste Special using Transpose.
wrote in message
news:avidcat.202gsm_1134625201.0378@excel...Worksheet doesn't permit F4 absolute referencing tool in formulas
I've never seen this before in a worksheet and can't find a Tool-Option
that would change it.
You can of course type the dollar signs in, but the F4 key won't
Sure it's something simple, any help appreciated!
ronthedog's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26504
View this thread: http://www.excelforum.com/showthread.php?threadid=397725
...Keep main title at top row, even when scolling down
Hi, how do you keep the main title at the top of the worksheet, even i
you have scrolled down 30 rows? Thanks
Message posted from http://www.ExcelForum.com
To keep row 1 at the top put your cursor in A2 and select window from the
menu, then Freeze pane.
You can also freeze columns at the left. The rule is the rows above your
cursor and the columns to the left will be frozen, so if you put your cursor
on B6 rows 1-5 and column A will be frozen. to remove use Window, Unfreeze
In excel this does not affect the printing if you want to something similar
for printing use File, Page s...Autofitting a row
Is it just me, or has something changed with Excel 2003?
If I have a cell with wrap text, shouldn't the row size automatically
increase if the text exceeds the cell size?
For some reason, my text just gets squished. I know I can go to
Format-Cells-Autofit, but if I do that, the row could SHRINK if I have too
little text in the cell.
Here's what I want: I want to increase the height of the cell to a decent
size (e.g. 30). IF the text exceeds the cell, then I want it to expand. But
if it doesn't, I want the cell to remain at 30 - not decrease.
Is there a way to do this? ...Formula Changes when data entered in referenced range
I have 5 formulas in a worksheet that refer to a range. When data is entered
in the range, the formulas adjust to exclude the cells with data. Range
B5:B211 When data is entered in B5, range in the formulas becomes B6:B212.
The workbook has nearly 200 worksheets with the same formulas. I'm working
in Excel 2003. Does anybody have any idea what's going on?????
Might help if you include the formulae that you are using.
"mac849" <firstname.lastname@example.org> wrote in message
>...referencing the cell above
I have a kin a formula wich gets the value from the cell above and then add
What I get is an list of values incrementing.
When I delete one row in this list, all cells below 'crashes' with #REF!.
What I need is that for the formulas to always look one cell up, even if I
delete a row. Now it seems that if I delete Row8, then when Row9 become Row8
it reference to it self and then ov course goes bananas...
Hope I made myself understandable ;-)
Any ideas folks?
Instead of using say =A19, use =OFFSET(A20,-1,0). This would go in A20, and
as it does...Scatter chart without numerical values
For some time now, when I create a scatter chart from tabulated (numerical)
data, the chart does not display the x/y values, but the serquence numbers
instead. This is similar to a bar chart, but should'nt happen with a scatter
The scatter charts have worked ok in the past. I'm using Excel 2000 in a
company office environment where I have little control over the Excel
Are you really making an XY chart?
If you make a Line chart with two numeric columns AND the first row have
labels, Excel thinks both columns are y-data and uses 1,2,3...vlookup probs
I want to put in a vlookup formula so that i can change a referance number
in a cell and it will give me the relavent information ie 3000 will refer to
bill 3002 will be harry etc.
The formula i have put in is :- =VLOOKUP(D3,data,3)
where d3 is the cell with the numbers i want to change to get different
information. So ideally it will look for the value i type in d3 in the
range i have called data and give me the value in column 3 on the same row
within the data field.
I hope i have explained clearly.
I should add that data is on sheet 1 of the workbook, i think this is w...Convert data from rows to columns
I have a range of information on a spreadsheet as follows:
1 Name: Tom Smith
Job Title: Sales
Organisation: Made up
Telephone: 1111 111 111
I have another 300+ entries of data (of 8 rows exactly as above) totalling
2629 rows. the example above is how it appears on my spreadsheet i.e.
headings and names in the same cell (Name: Tom Smith) and sometimes seperate
cells (Email: / email@example.com)
I need to create 6 columns for Name/Job
title/Organisation/Telephone/Email/Subject and then move the data into the
relevant columns....Pivot tables 2 row labels
I am trying to create a pivot table and want to have sales rep names and
company as the rows. Is there a way to have the sales rep name repeat
everytime with the company instead of how it looks below? I am going to have
to use the output of the sheet to do a vlookup on another sheet and in this
format I can't make it work. Thanks!
Nope--not if you want to keep it a pivottable.
But you could convert to values (or copy|paste values in a different locatio...Indirect Range Referencing
Hello, I work in a paper mill. I am building a spreadsheet with several
charts. I have a wide sheet of paper coming off of a paper machine of
varying widths. This paper is wound up on a "reel".I have a scanner
that measures the weight of the paper on the reel, and each scan gives
me a 600 point array. I can calculate where the edges of the paper lie
within the array, no problem, and plot the whole thing very nicely. The
problem is that the paper then goes into a re-winder, where the wide
sheet is cut into smaller sheets, that make up "rolls". I can
calculate the sta...Net book value should be zero when an asset is retired
When an asset is retired and has not been fully depreciated, the net book
value is calculated as cost basis less LTD depreciation. Our client believes
that this is bad accounting practice. The net book value should be zero when
an asset has been retired, even if it has not bee fully depreciated. I
understand that when running FA reports it excludes assets with a status of
retired, however, when using smartlist > fixed assets book, there is no field
to filter out all assets with a status of retired. This makes it difficult
for the client to correctly analyse their assets, especial...Words print with spaces within them that are not on the screen
When I print a document in Word 2007 that is formatted as justified,
sometimes a word will look like there is a space in it that does not belong
(example: "w ithin", instead of "within"). Also, there may be an extra space
showing between two words (example: "or have any obligation", instead of "or
have any obligation". This does not show up on the screen in print layout or
in print preview. Also, when I show formatting on the screen, there are not
any extra symbols for spaces where they are appearing on the printed
document. How can I...Search from value to value on text fields
I have a suggestion concerning search on text fields.
Fx the zipcode field. In Denmark we often want to search for an interval fx
5000 to 5999.
This isn't possible on Text fields in CRM. You have to create a search with
a lot of OR's in AX it is possible to use the syntax 5000..5999.
Or a selection of method Between or From and To.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
li...need help calculating intermediary values
I'm not well enough versed in spreadsheet use to calculate some
intermediate values I need. I would be greatful for anyone who can offer
a formula or suggestion.
I have 1 guidline column, numbered 1-30. Then 2 other columns, A & B are
beside it. The values in A & B change as they go from 1-30, they change
at different rates. I have some intermediary values at given points but
need to calculate what the values in between would be, based on the
fixed values at 1, 4, 8, 15 and 30. Below, I have shown the layout as
best I can without including an attachment. Thanks.
chris@cjalexander...Default value 07-11-07
Is there any way to initially put the Available qty = the In qty?
Because I need the available qty to be deducted everytime there is a
Message posted via AccessMonster.com
On Wed, 11 Jul 2007 03:08:54 GMT, "EMILYTAN via AccessMonster.com"
>Is there any way to initially put the Available qty = the In qty?
>Because I need the available qty to be deducted everytime there is a
You can use the AfterUpdate event of the Form to set the default value...