I often have 2 or 3 diff options on estimates. I do this
on separate sheets in the same workbook by copying
estimate 1 and making the changes. How do I get the same
header on all the work sheets without having to retype
every time? Thanks.
One way...when you open a workbook. Select all the sheets (Click on far
left, hold down shift and click far right).
Type the Headers/Footers and the Header/Footer will be the same on each
sheet. Don't forget to un-group. Right click a tab and select un-group.
Anything entered on grouped sheets happens on all those selected.
If you want ...Autofill Formulas
Hello. I have a workbook with 31 sheets of data. Each sheet is a form with
a record of data. I would like extract the data into a list. For example,
range A5 is the same field on each sheet, so I want to autofill a formula
reference the same cell on different sheets. I suspect I need a macro for
this, but wanted to make sure first. Can you autofill like this
=Sheet1!$A$5, =Sheet2!$A$5, =Sheet3!$A$5?
=INDIRECT("Sheet" & (ROW()) & "!A5")
Entered in A1 of new sheet.
Copy down to A31............will increment the Sheet numbe...how to i control autofill
Suppose I enter a formula in cell B2 and I want this formula to autofill
down to cell B55000. If I click and drag down to cell B55000, there are
two problems: it takes too long to scroll all the way down to cell B55000,
and by the time i actually get there the scroll speed is so fast that i
usually pass the cell. Is there a way to autofill a formula so that I
don't have to do it by manually dragging the formula down to cell B55000?
Message posted via http://www.officekb.com
two options that i know of:
1) if there is data in column A down to A55000 (or co...How? compare two worksheets and identify rows of data that appear on both.
Suppose I have 2 worksheets.
Sheet 1 has 1000 lines of data spanning xNumber of columns.
Sheet 2 has 500 lines of data, also spanning the same number of columns.
An unknown number of identical lines of data appear on both sheets.
How can I get to a point where I can build one worksheet that is a composite
of both sheets - such that no identical line of data appears twice on the
Thanks in advance, Simon.
You could do this several ways. It really depends on your data
integrity and what sort of data it is.
-- Use a VLOOKUP OR INDEX/MATCH formula to compare the 500 row...Print Worksheet Contents
is there a way to print the contents of a worksheet? Say I change the prices
of 20 items and I want to print new shelf labels. Or I want to notify my
remote stores of those price changes.
After your worksheet has issued (and maybe before) - you can open it, go to
Contents tab> right Click> Copy As Table> OK (on copied to clipboard)> Open
Excel or similar> Paste.
Unfortunately - there is not an import function on the label wizard. You can
however filter the wizard for the Last Updated date.
> is there a way to print the content...command line switch to open a specific worksheet?
Is there a way to open a specific worksheet in a workbook using command
I'm trying to create a menu selection in AutoCAD that will open an Excel
workbook to a specific worksheet inside of that workbook. Typing the
command "start" <CR> followed by "Excel /filename/" <CR> will open that
particular file, but how do I get it to open to a specific worksheet?
Thanks for any help,
There's no command line switch that will activate a worksheet. You have to
do one of the following:
-Save the workbook with the desired sheet active. Exc...Pivot Autofilering Items
I'd like to see only the valid items on the drop down list on the "Page
Field" area .
Model - Color - Full Optional - Qty
FIAT BLU YES 10
OPEL GREEN NO 12
BMW RED NO 14
In the Page field I put the Model, Color and Full Optional field.
When I filter the item FIAT on the field Model and then I want to filter the
Color field, I'd like see only the color regarding the FIAT model (BLU), bu...Way to make non-selectable worksheet?
I have a spreadsheet with 4 form buttons on it that will run different
macros. Is there any way to make it so the user can't click on any
part of the worksheet except for the buttons?
I don't want a message to pop up, I just want Excel to ignore if the
user tries to click on anything except the buttons.
A couple of possibilities: You could protect the sheet (Tools -
Protection - Protect Sheet. You'll want no locked cells (Format - Cells -
Protection - Locked). In Excel 2002, you can specify that locked cells
cannot be selected. Can't do that in XL97...Autofill on addressing
Trying to set up Outlook on new PC as I had it on old one where e mail
address is automatically offered when part of a name is inserted, but no
luck in finding it.
Trevor Appleton <email@example.com> wrote:
> Trying to set up Outlook on new PC as I had it on old one where e mail
> address is automatically offered when part of a name is inserted, but
> no luck in finding it.
What version of Outlook? For OL 2003, it's Tools>Options>E-mail
Options>Advanced E-mail Options: Suggest names while completing To, Cc, and
Brian Tillman ...Change worksheet radian default
Is there an easy way to change the worksheet radian default to degrees
so that I don�t have to use the 180/PI or PI/180 or the degrees
The following is a formula to derive the Log Haversine of the number
and the following is to derive the number 30 from the Log Haversine,
(Acos(1-(10^((D18-10))*2))*180/PI())/24 D18 being the cell
with the log haversine.
Surely there is an easier way to get a log haversine than this. Any
help will be most appreciated.
----------------------------------------------------------...Hidden worksheet problem
I have a workbook in which one worksheet is hidden. (I know that because
some formulae refer to values of cells in a worksheet that cannot be seen.)
However, when I tried unhiding the worksheet by clicking format --
worksheet, both the "hiding worksheet" and "unhiding worksheet" menu items
are dimmed. What else can I do to unhide the worksheet?
could be that this sheet was hidden with VBA (using xlsheetveryhidden).
Try the following code in VBA
dim wks as worksheet
for each wks in worksheets
Fran...better search: "make worksheet visible"
How come, I enter "make worksheet visible" and I can't find any relevant
information?? It should be pretty straightforward to figure out what I am
asking about and how to do it. Moreover, if I put the words in quotes, many
search engines assume I look for the phrase, not the words separately. So how
come I still get many irrelevant results? Either the search doesn't work
right, or the information it is searching is not well organized and complete,
Some search engines respond to 'How do I ..? " type interrogation; others
respond (better) ...Ranking within a Filtered Worksheet
Anyone know if you can rank filtered data in Excel? I want to filter
out some rows and rank the rest but the hidden data seems to be
screwing up the ranking. Essentially, the worksheet has three sets of
data that I would like to rank separately.
You can add columns to your table, to calculate the rank.
For example, to rank an item in column G, with data starting in row 2:
--In cell H1, add the heading RankAmt1
--In cell H2, enter the following formula: =SUBTOTAL(9,G2)
--In cell I1, add the heading Rank1
--In cell I2, enter the following formula: =RANK(H2,$H$2:$H$400)
When entering data into fields that verify against a database, such as vendor
IDs, accounts, checkbook names, and so forth, have the program suggest a
logical selection, much like Excel does with its autofill feature.
AutoComplete functionality was added in v8.0 of Great Plains and remembers
the Key field values that you use.
David Musgrave [MSFT]
Senior Development Consultant
MBS Support - Asia Pacific
Microsoft Business Solutions
Any views contained within are my personal views and
Can you tell me how to autofill a hyperlink formula so that both the file
path and the friendly name change incrementally? I'm using the hyperlink
function, and need to copy it down so that the file name changes (dec 8.pdf,
dec 9.pdf...) and the friendly name also changes (Dec 8, Dec 9...)
The formula I am using is: =HYPERLINK("g:\public\DP\dec 7.pdf","Dec 7")
=3DHYPERLINK("g:\public\DP\dec "&ROW(A7)&".pdf","Dec "&ROW(A7))
ROW(A7) will return 7, and so will be equivalent to what y...How do I cycle through all of the worksheets
I need to check all of the worksheets to collect and sum contents. Is there a
with method or something along those lines?
if a = b then
Total = a1 + a1
For Each sh In Activeworkbook.Worksheets
If Range("A1").Value = Range("B1").Value Then
myTotal = myTotal + Range("A1").Value
(remove nothere from the email address if mailing direct)
"DMB" <DMB@discussions.microsoft.com> wrote in message
> ...Find and TRansfer from another Tab in same Worksheet
Hi, I have a worksheet in which contact information (names/add/postcode etc)
are stored in a secondary tab. I need to update the information onto the
first (main) tab. Is there a way I can put a postcode into the postcode
column and for it to search the secondary tab, match the postcode there and
transfer full contact information to the main sheet?
Thanks in advance. David
If you have one simply use a macro using FIND. If many, use
data>filter>autofilter>filter for you desire>copy>paste. Record a macro
while doing and clean it up.
Micro...values/formats in a cell get randomly replaced in some worksheets
values/formats in a cell get randomly replaced in some worksheets with
values/formats from some of the drop down menu lists.
Unprotected cell values are getting changed after some time, may be hours or
days , from drop down lists. Excel sheet files are quite big, like 4/5 mb,
having big data and near to 4000 formats.
...Autofill ID fields
I am using Microsoft Access 2002 SP3. I have one main table named Issues
and several supporting tables all related on the ID field. The Issues table
ID field has an Autonumber to populate the field when a new record is
created. What I would like to accomplish is to have the supporting table’s
auto fill the ID field using the last ID created in the Issues table.
On my Issues form I have a combo box that depending on the selection
will open the corresponding form to the particular table. When the form
opens I want the Issues form to close and the ID field to auto fill with the
ok i have a File.. preety big that has tabs like these on the bottom ..
Z1, Z2, Z3, Z4.. all the way too Z979
Now i have a summary sheet in the Fron where i have this in the cell:
now is there a way i can auto fill this all way down.. someone pleas
help.. i need to get this done than
Message posted from http://www.ExcelForum.com
I don't think you want sheet name that could be confused by
Excel as cell addresses or defined range names.
You can rename your sheets to anyname that you want to
use and you build a table of contents at front, see
http://www...Need Help linking worksheets
I recently took a part-time job as bookkeeper for an organization where I
need to be able to print a report on demand of current finances. I have a
workbook with 3 sheets. The first one is the General Ledger Report, 2nd,
receipts, and 3rd, lists the disbursements. My general ledger is: Begin.
Bal. plus Receipts minus Disburs, plus or minus transfers =ending balance.
The Ledger is listed by categories while the other two sheets are entered by
the date each action occurs. Although I have used Excel for some time, I have
only dealt with the simpler formulas and have never linked sheets ...double worksheets?
A co-worker was working in a regularly-used workbook. Suddenly a second, seemingly identical workbook opened; the two were named *.xls:1 and *.xls:2 respectively. A week ago, a similar situation happened with the same workbook, except the duplication occured upon opening, rather than after it was already open. At that time, when we closed one of the "versions" the colon-number additional extention disappeared. But after closing the remaining document and reopening, the duplication appeared again upon opening. Can anyone tell us what this means? Did she accidentally hit a comman...Excel worksheets
This is a multi-part message in MIME format.
How do I copy one excel worksheet and into another worksheet to make it =
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<META http-equiv=3DContent-Type content=3D"text/html; =
I'm having a problem with autofill. I can drag and drop but when I want to
see the options, before I can mouse over to the options box, the box vanishes
as soon I've moved off the initial cell that was copied. I'm not fast enough
to get to the options, so it would be good if someone could tell me how to
keep the options box available.
The Auto Fill Option button is displayed after you select the AutoFill option
in the Series dialog box. To show the Series dialog box, use one of the
In Microsoft Office Excel 2007, click the Home tab, and then cl...How do I delete one worksheet from a different one?
I am trying to take all the entries from one worksheet and delete them from
another sheet if they are listed. The master file is over 56,000 rows and I
want to know if there is a faster way to find these rows in the master and
delete each one. Can anybody help me with this???
Mandy: On the sheet with items that you want deleted from the larger sheet,
type a "Y" or "delete" in the first blank cell to right of the item and copy
it down the list. Go to the larger sheet and click on the first blank cell
to the right of its first item. Put in a VLOOKUP formula referri...