how to paste formula and retain all or part of the cell references
I have following kind of problem:
I want copy a cell from A5 to B5.
In A5 I have a formula that takes values from A2 and E5, i.e. "=A2/E5".
In the pasted cell the formula changes to corresponding values: "=B2/F5".
In some cases this might be what I want, but in current situation I would
like to change only column to corresponding - I would liket the resulting
cell to include formula: "=B2/E5".
How can I do this?
Of course, in case of such simple formula it wouldn't be a problem but if I
have very long formula with lots of references, it's a nightmare..
A...Named Ranges #5
I am having trouble clearing named ranges. When I first named the ranges in
a spreadsheet, I did it all wrong... I now know how to do it correctly, but
the previously named ranges are messing me up! Can someone help?
Go 'Insert' 'Name' 'Define' then delete all the named ranges you no longer
"Donna" <Donna@discussions.microsoft.com> wrote in message
>I am having trouble clearing named ranges. When I first named the ranges
> a spreadsheet, I did it all wrong....Surpressing rows and columns with zero values in a pivot table
Let's say I want a report showing the number of times that my employees,
grouped in offices, markets and regions, makes a particular kind of error.
At the end of the month, I'd like to create a pivot table that says, "This
office had this number of errors of this type." I can create a pivot table
that does that, but it includes all the employees that have zero errors,
making the report unwieldy.
How can I format the pivot table to hide any row or column that has no value
Your issue is that it has a value. Something adding up to zero is just as
va...how can text be set up to generate a number?
i would like to a word generate a number, i.e.> enter "apples"in one cell and
excel generate "352" in a different cell for items like inventory.
You will need to set up a 'Lookup' table somewhere with
In two columns. Complete this for all entries and then if you are going to
enter 'Apples' on another sheet in A1 then in A2 enter
Microsoft MVP - Excel
"jwmaes" <firstname.lastname@example.org...Want use results of Drop down list to look up a value
I am using Excel 2002 and I have a multisheet workbook and on the first sheet
I have created a series of 6 drop down lists pulling from predefined named
ranges. I also have limited subsequent choices based on what is selected in
the other lists. The end result is the user will be selecting parameters that
identify a product and now I am stuck with an easy way to look up the
associated part number based on the parameters they selected. Essentially,
once they have chosen the parameters in the list, I would like to create a
macro that uses those values to locate the associated part...100% Stacked Bar chart, two bars, want percent as well as real value
I am attempting to chart the following:
Series 1 - Values over the prior 30 days
Series 2 - Values over the prior 12 months
I would like to show both data series as 100% bar charts, so users of
the information can compare percentage of the last 30 days with
percentage of the same piece of data over the last 12 months.
So, for example the two data series might look like this:
I want to display both the actual value, and the percentage of the
total, for each series, in the labels. For some reason, showing
percentage is not an option. Why is th...Show which cell has MAX, MIN values?
At the bottom of a couple thousand rows of data, I have =MAX and =MIN
formulas. Is there some way I could make the cells beneath my MAX and MIN
formulas show me the address of which cell has the displayed MAX or MIN
value? At least the row number?
To return the row
=MATCH(cell with Max or Min value,range starting in row 1,false)
or to return the address, say, in Cell N3000, for a value given in N2999
or to return other matching information, like a name in column A
MS E...Most logical value
I would like to know if there is a method of determining "the most
logical" value in a table.
These are some data that I put in the table below. These numbers are
obtained from some dedicated software to compute the influence of an
investment on some projects. I would like to fill in the gaps myself.
I can add the data from column 10 en 20 and divide it by 2 to compute
the value for column 15, but is there a better method?
year 0 5 10 15 20 25 30
2005 72,8 72,8 72,8 72,8 72,8 72,8 72,8
2006 79,4 76,7 71,5 60,9 50,3
2007 92,3 87,4 76,9 55,6 33,5
2008 108,6 102,3 8...require cell completion in a form?
I have a very simple form in Excel. I'm not sure if it matters, but
the machine I'm on uses Excel 2002, and most of the users are on Excel
2000. The first 2 questions on my form require that the user select
from a list of names, and then select their relationship to that
person. Then the users answer a series of questions about this
person's job performance.
I'm using data validation to manage the two drop-down lists - the
names and the relationships. However, about a third of my users end
up leaving one or both of these cells blank. I have a comment box set
up as a remin...copy sheet with range names
I'm making 2 copies of an existing worksheet within a workbook. The original sheet has lots of range names. Now, when I make my
copies, the range names are screwed up and are referring to the new sheet instead of the original one. What's up with that? I don't
see any options related to range names. Deleting and resetting the names would take forever, as would "re-defining". I shouldn't
have to do that. Any ideas?
I think if you look at your range names, you'll find that xl localized the
copies. When it copied the worksheet, it had to do something ...how do I set-up a interveiw outline
how do I set-up a interveiw outline
"brazal" <email@example.com> wrote in message
: how do I set-up a interveiw outline
make a bulleted or
numbered outline via
DatabaseBen, Retired Professional
- Systems Analyst
- Database Developer
- Veteran of the Armed Forces
- Microsoft Partner
~~~~~~~~~~"share the nirvana" - dbZen
"brazal&...Average range of discontinuous cells
I have a block of columns (P-CT) that I need to average but only every 4th
Example: I need to average P, T, X, AB, AF, AJ, AN, AR, AV, AZ, BD, BH, BL,
BP, BT, BX, CB, CF, CJ, CN, CR. If I just type =AVERAGE(selecting these
cells) it gives me 40238 when the only one with a value is cell P. These
cells contain dates if anything. They are payment dates. I need the average
number of dates it took to pay something. Any ideas for Excel 2003? Thanks.
Never mind. I forgot to include my beginning date. It works. Just user
> ...Default numeric value
I am creating a spreadsheet dealing with dollars. There are several formulas
that I have created using percentages. One particular formula multiplies a
percent in one cell by the $ value in another. If I have no $ value in the
cell the formula uses a '1' instead of what I would expect '0'. How can I
set up my sheet so that if a cell is blank the default value will be '0'?
Would help to see your formula, but:
VBA Project Manager
"thomson" <firstname.lastname@example.org...Last cell with data in a range
I enter weekly data into a spreadsheet with a summary page at the front.
After every week, when new data is entered, I want the formula at the front
to use the cell with the new data instead of me having to change the existing
For example, my data looks like this:
A B C
I need a formula that will automatically detect the last number >0 in column
Thank you for your assistance!
so your main page formula might be something like:
=...Excel macros page breaks but not on row 1 and column value in footer
Hi, I have seen some posts that are similar, but no one seems to have
the problem that I am having. I will post my macro in this, for anyone
that is interested.
My users get a csv file every month, and we have to clean it up. This
macro does that.
My last issues are this:
1) having the spreadsheet create page breaks whenever the value in
column B changes. Below is just that code.
col = 2
LastRw = ActiveSheet.UsedRange.Rows.Count
For X = 2 To LastRw
If Cells(X, col) <> Cells(X - 1, col) And Cells(X, col) <> Range("B1")
ActiveWindow.SelectedSheets.HPageBreaks...display cell value in msgbox formatted as %
I have been trying to come up with a way to display a cell value in a
msgbox so that it formats properly as a percent.
I have tried:
Productivity = Format(Range("A1").Value, "###,# %")
This always gives me a leading 0 (e.g 015%) and I want it to display
So I tried this:
Productivity = Format(CStr(Range("A1").Value) * 100, "#,###.0") & "%"
and it works ok but...
I want to use the value of productivity in computations - which I can't
formatted as a string ... Am I missing something - or is it as simple...Can multiple cell results be displayed in a single cell?
Is it possible to display the contents of multiple separate cells in another
If the results of several cells say B1, B2, and B3 are the amounts $10, $15
and $20, I would like to display them in cell A1 as follows:
I could do this statically by using text and the 'Alt|Enter' function within
the cell. However, these amounts will change from time to time so I'd like a
Is this possible or not?
Thanks for any help.
="$"&B1&CHAR(10)&"$"&B2&CHAR(10)&"$"&B3&am...Using color to ignore cells in a formula?????
Is it possible to set up a formula in excel to ingore certain cells if you
should change the color of that cell to a color.
if cell A25 was blue how can I ignore it so that it is not calculated?
only with VBA. See:
"Mike A." <MikeA@discussions.microsoft.com> schrieb im Newsbeitrag
> Is it possible to set up a formula in excel to ingore cer...Find Future Value of a Savings Account
I am trying to set up a spreadsheet that determines the future value of a
Take a look at the FV function in XL Help.
In article <EC8EEB72-AF22-47D8-A266-839CA9356725@microsoft.com>,
"gingerjane" <email@example.com> wrote:
> I am trying to set up a spreadsheet that determines the future value of a
> savings account.
...Dynamic Range #3
I'm sure this has been answered before but nothing I've read here has helped
me. I had a chart in a workbook I made in Excel 2003. Worked fine until I
"upgraded" to 2007. The chart gets its data from column K on another
In 2003, it was:
Series Y Values: =Table!$K$4:$K$60000
This worked fine in 2003, but in 2007 it seems to be trying to calculate ALL
those extra rows. I only put them in there to make sure all values got added
to the chart as the number of rows with actual numbers changes (realistically
it would never take up that many rows). 2003 ig...Excel: When printing some cells will not print text in them
Operating System: Mac OS X 10.5 (Leopard)
When I print an Excel 08 spread sheet, two of the cells that have text in them will not print the text in the cell. All the other cells with text prints fine. The text does not even show up on print preview in the print dialogue box.
Any suggestions on how to get it to include the text in these two cells?
...SetFont does not set font
Yet another problem I am afraid and I would once more like to call upon a
little external wisdom. I am having a major problem with CFont and the
SetFont. Mainly: It doesnt want to work.
I am using the CreatePointFont method on a CFont object defined in global
scope at the top of my dialog.
I then attempt to try and use SetFont on a dialog, and on a button, and on
anything else for that matter and the effect it has is zero. I am using it
in my InitDialog routine yet I still get nothing when I paint text to the
window, or when I view the buttons on the dialog.
Has anyone g...Connecting Cell with Lines
I have seen MSExcel printouts that have dotted or solid lines of different
colors connecting different cells in a sheet. Is that something that can be
done in MSExcel or would they have been added outside of MSExcel? If it can
be done in MSExcel, how?
In article <QbYYg.13615$GR.firstname.lastname@example.org>, "jerry" <email@example.com> wrote:
>I have seen MSExcel printouts that have dotted or solid lines of different
>colors connecting different cells in a sheet. Is that something that can be
>done in MSExcel or would they have been added outside of...Appending data in cells that utilize a Dropdown list.
I would like to append entries in cells that utilize a dropdown list.
Currently, when I select (in this case names) from my dropdown list I cannot
append them if I want to add more than one to a cell. If I select another
name from the dropdown, it erases the first entry. Is there some way to
append entries within cells that use this form of validation so that I can
enter more than one in a cell from the dd list?
Office XP Professional
Debra Dalgleish has an example workbook showing how to do this
http://www.contextures.com/excelfiles.html...Worksheet Change Event log for multiple cells
I have a spreadsheet that is available here:
(although I scanned it for viruses please make sure you do it again prior to
opening it as I cannot guarantee it's worm free).
I would like to create a log file in an additional sheet (hidden probably)
that would record every activity from column E after clicking a button
assigned to a cell in that column and show these records in a worksheet
Totals in a specific row.
For example: Column A from a worksheet „Totals” corresponds with column A in ...