Delete all sheets in workbook that contain "Dump" in the name
I have a workbook that generally contains 15-18 worksheets. Is it possible
to automate the deletion of only the worksheets with a name that contains the
word "Dump". The amount of worksheets that contain the word "Dump" ranges
Message posted via OfficeKB.com
You could use a macro:
Dim sh As Object 'could be any kind of sheet
Dim HowManyDeleted As Long
HowManyDeleted = 0
For Each sh In Ac...Cell Value as Named Range Reference
Little bit of a quirky question...
Trying to use a cell value as a reference in a formula, where that
cell value is the name of a named range.
So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2.
I want to get the correlation vale for A1:A3 and B1:B3
So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the
formula: =correl(D1,D2). But I get an error. Have also tried using
Indirect to no avail.
Any help would be hugely appreciated. Thank you.
http://www.wimgielis.be = Excel/VBA, soccer and music
"ste...00/01/1900 In the date cell when linked cell is empty?
Hi I have 52 worksheets all linked up to a total sheet that is working very
well thanks to RD
All I need to do now is sort out the date this is copied from sheet1 onto
the totals page and works fine when the date is put into sheet1.
However if the cell is blank on sheet2 the date is put into the totals sheet
as 00/01/1900 when the date is put in sheet2 all is well with the right date
entered into the totals sheet.
So how do I get the totals date to stay blank until the weekly sheets have a
date put into the sheet please.
the format I am using is below
many thanks for all the help I ...Need to reference a block of cells from another sheet.
apologies if this is probably a mundane question, but I didn't know what
'search terms' to put in google to return any results..
I've got a Spreadsheet with 12 tabs (sheets), I need part of tab 12 (sheet)
to appear on tabs 1-11 (sheets), so that if I change data in tab 12 (sheet),
tabs 1-11 (sheets) are changed too.. the cells themselves will never move
location, just the data within them..
Is it possible?
On your sheet1 select the cell that should receive the data from Sheet12;
Hold down the Shift key and Click on Sheet11 << this shoul...refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could
apply to both
please could you take a look at the following post
Is there some reason you can't post your question here instead of asking us
to look elsewhere?
Microsoft MVP Excel
"Blinds Nottingham" <email@example.com> wrote in
news:firstname.lastname@example.org...moving to next cell using "Enter"
With Excel XP (Windows EX also) Excel seems to have a problem
remembering what direction to move the focus when the Enter key is hit. Is
there a fix or a way of keeping it the same?
Go to Tools->Options and choose Edit tab. In that choose the direction
of the Enter key to the way you want.
Joseph Meehan wrote:
> With Excel XP (Windows EX also) Excel seems to have a problem
> remembering what direction to move the focus when the Enter key is hit. Is
> there a fix or a way of keeping it the same?
>...doc w/ same name open?
When I try to open a document i get an error message a
document with the same name is already open. Cannot open
2 docs w/ same name??
this is a Excel restriction. Excel can only open one file with the same
name (though they are in different directories)
"Jeff C." <email@example.com> schrieb im Newsbeitrag
> When I try to open a document i get an error message a
> document with the same name is already open. Cannot open
> 2 docs w/ same name??
I don...What is IVA# in Distribution Reference and Reference?
We did an inventory adjustment and now we get a unknown amount in Debit under
the Inventory account and Credit for COGS account in the Transaction Entry
Zoom. We're trying to figure out where that amount came from but we couldn't
zoom more than what it's currently shown. In both fields, Reference and
Distribution Reference, it's shown IVA#######... that is unknown to or setup
by us in anywhere, i.e. Audit Trail, Source Document.
Does this amount affect the dollar in the accounts mentioned?
And can anyone please help to where we could drill down the IVA details?...How to move data from one sheet to another automatically
I want the 3 cells that i have containing (cell one) Name, (Cell 2) Street,
and (Cell 3) City, state, and zip to go from my sheet one to another sheet
auto matically using a formula. (not macros or copying)
Bernard V Liengme
Microsoft Excel MVP
remove caps from email
"Harleyrider888" <Harleyrider888@discussions.microsoft.com> wrote in message
> I want the 3 cells that i have containing (cell one) Name, (Cell 2)
> and (Cell 3) City, state, and zip t...Data Validation in cell
I have a sales order form with the equipment on the second sheet as "PartNo"
"Description" "Cost" selected each column of data then clicked in the
cellname box and called them "PartNo" "Description" "Cost"
On the sales order form I have the columns "Qty" PartNo" "Description"
"Serial No" "QtyDes" "Price" Total"
I have set up data validation for the description as a list with
=Description what I want it to do is display the part no in the part number
Column and the cost in...macro button refers to prev. workbook?
When I create a macro in Excel, and assign a button to it, it works
fine the first time, but when I re-open the workbook for a second
time, and try to run the macro again, the macro seems to refer to the
previous workbook...For some reason, the macro saves the name of the
Dim name As String
Dim adress As String
name = ActiveSheet.Range("B6").Value
adress = ActiveSheet.Range("B4").Value
ActiveWorkbook.SaveAs Filename:="C:\WINDOWS\Desktop\Temp...How can I make a Chart data series treat blanks as "Empty" cells
My Data Series contains blanks as the result of formula calculations. The
location and number of blanks can change with each recalculation as input
criteria change. I can make my Chart ignore an "Empty" cell easy enough but
my cells still contain the formula which created the blank ("") cell and
therefore, not truely empty. I want to plot the series, about 7000 values,
without the blanks showing up as zeros and ruining my trendline fit.
Removing those rows is not an option as I have to retain the x-axis value as
place holder on the chart.
Thanks for your help; ...Finding emply sheets
How can you quickly identify completely empty sheets in a workbook? I get
workbooks from people that have lots of emply sheets, and need to quickly
identify and delete these sheets?
I select the worksheet and hit ctrl-end to see where the last used cell is. If
I'm taken to A1, then I check to see if that cell is used.
If A1 is not empty, then the worksheet is used.
If A1 is empty, then there's a good chance that the sheet isn't used and can be
But this isn't a foolproof method. The sheet could be used for other purposes...Column and Row reference
How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top and right hand side references
Uncheck "row and column headers".
Good idea to spend a few minutes browsing through the various Tools>Options
tabs to see what else is available to toggle on/off.
Gord Dibben Excel MVP
On Mon, 19 Jul 2004 15:53:02 -0700, "Dave" <Dave@discussions.microsoft.com>
>How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top an...Newbie question
Is there a way to flag selected cell ranges to always stay
together when they print? In Word they have a "keep lines
together" option -- is there anything comparable in Excel
to ensure that selected cells/rows stay together on a page
even when new information is added (not only within the
selected range but also when additional cell ranges are
I need to ensure that the cell ranges aren't split by page
breaks. I currently use print preview to see where they
are breaking once I add new information and then move the
page breaks before printing.
Since new...Function to get worksheet name
Its seems pretty simple.
1. which functions returns parameters like worksheet name ?
Same way can i get what is file name ?
Thanks in advance..
>Its seems pretty simple.
>1. which functions returns parameters like worksheet name ?
>Same way can i get what is file name ?
The only built-in function that returns these is CELL. You could use
CELL("Filename",A1) to get both the workbook filename and the worksheet
name corresponding to the range reference 2nd argument *IF* you've
already saved the file. In that case, it'll retu...Date Formats for Cells Do not match System Date Formats
Operating System: Mac OS X 10.6 (Snow Leopard)
I have just loaded Office 2008 and then update to the latest update 12.2.4 <br>
I have my system preferences set to Australia for date and time using the date format dd/mm/yyyy. But in Excel when I open the format cell dialogue and select date the only formats available for selection are in the mm/dd/yyyy formats. When I checked in "Numbers" the dd/mm/yyyy format is used so "Numbers" is picking up the system preference why not the excel.
Did you by any chance have ...How to create a calendar from date information in XL sheet.
I have an XL spreadsheet that contains multiple columns,
one of which contains data in date & time format. Is
there a way to export this data and associated information
from the record to another application, for example Word,
and create a calendar? Any suggestions how to manipulate
this XL data are welcomed. Thanks!
I had a problem today with a missing reference - Microsoft Forms 2 on a
colleague's machine at work (Office 2003; VBA 6.5; XP).
I've read Chip Pearson's "Missing References in VBA" which is a geat
explanation about how to fix problems like this.
Oddly the library didn't seem to be flagged as "missing" and was in fact
present in the system32 directory but didn't show up in the list of
Now to the questions:
I'm not clear on whether if I write code in a project that references a
library which is not part of the &qu...Creating Summary Report on Seperate Sheet
Im hoping this really isn't as complicated as I've found it to be, but
it's 4am and I don't seem to be getting very close.
I know 0 VBS stuff, so that may be a quick answer for me to learn a
little, but here is the general idea.
I have a spreadsheet with two sheets, the first is a checklist, where
you can enter numbers that correspond to the items that apply to you.
At which time formulas derive other information.
For example, column A is how much, on a scale of 1-10 the person likes
pie, and then a formula in column C decides how much that will cost the
So a user c...Nested IF that will ignore blank (empty) cells
I have a simple nested IF function (Excel 2003) which compares numbers in
two cells, then returns a word depending on whether one number is greater
than, equal to, or less than the other:
Either (or both) of the compared cells might contain the number 0 (zero) in
which case I want the IF function to display the appropriate word.
However if the compared cells are blank (have no data in them), my
unsophisticated IF function thinks that F3=G3 and returns the word "Equal".
How can I get the ...Reference
Is it possible to create a reference to another object such as:
int & x = i;
but using an image list, and referencing different objects depending on a
such as :
CImageList & imageList;
if (thumbnail == 1) imageList = m_ImageListThumb;
else imageList = m_ImageListFullSize;
Hope this makes sense!
You're better off using pointers for this. I believe, depending on the
class, that what you're trying to will make a copy of the object instead of
"Ben Williamson" <firstname.lastname@example.org> wrote in message
news:43ee4603...entering a date into cell and get ## symbols
i am entering a date value into a cell and i that it shows is "##" Even when
i print it shown "##" and no date why?
Make the cell wider
"Steve" <Steve@discussions.microsoft.com> wrote in message
>i am entering a date value into a cell and i that it shows is "##" Even
> i print it shown "##" and no date why?
> i am entering a date value into a cell and i that it shows is "##" Even when ...Macro to Reference Column Next to Current Reference
I'd like to automate the following procedure using a
macro. Please help.
1) i have 2 sheets: Sheet1 & Sheet2
2) In Sheet1, Cells B2,D2,F2 has the formulas "=Sheet2!
F2", "=Sheet2!K2", "=Sheet2!P2" correspondingly.
3) Every month when i do my work, i'd have to manually
rekey the formulas to reference the subsequent columns,
ie, Cell B2,D2,F2 will change from above to "=Sheet2!
G2", "=Sheet2!L2", "=Sheet2!Q2" and in the following
month, I have to change manually again to "=Sheet2!
H2", "=Sheet2!M2&qu...Help with #DIV/O! ...need cell value to come up with answer
When dividing a number by zero (0)
the following error appears #DIV/O! however instead of this I would
like the number 0 to appear.
How can this be done?
I have tried conditional formatting..but cant get it to work..any
=if(a2=0,0,b2/a2) is the way
On 28 Aug 2003 01:47:44 -0700, email@example.com (Tim Harding
Royal Mail) wrote:
>When dividing a number by zero (0)
>the following error appears #DIV/O! however instead of this I would
>like the number 0 to appear.
>How can this be done?
>I have tried conditional format...