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...Resetting report page numbers
I followed the instructions below on how to set the starting page number of
a report. However, the page number is now appearing as a static number on my
report (ie. I want the 3-page report to start at page 6..but every page now
reads page 6 instead of page 6, page 7, page 8)...what did I do wrong?
INSTRUCTIONS FROM MICROSOFT:
Do one of the following:
If the page number is located in the page header (page header: Used to
display a title, column headings, dates, or page numbers at the top of every
page in a form or report. In a form, the page header appears only wh...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...I need to increase a number which is held in a cell by 1
How do i increase a cell which has a number in it, eg, cell a8 has 4
i want to run a If statment that looks at another cell and if true i want
cell a8 to increse to 5
You need VBA.
The code is simple in itself (Range("A1").Value = Range("A1").Value + 1),
but what will trigger the addition to happen.
"Steve" <Steve@discussions.microsoft.com> wrote in message
> How do i increase a cell which has a number in it, eg, cell a8 has 4
> i want to run a If sta...number of fields exceeds 256 when importing from a dbf file
I'm having a small problem with importing some old data from a FileMaker Pro
database. According to what I found out in the groups archive the best bet is
to convert it to a dbf file and then import it. However, I the number of
fields in the dbf file exceeds 256, so the import wizard truncates the import
at field number 256.
I would now want to spread the data over two sheets. However, in the process
of converting it to a dbf, quite a few of the field names get truncated and
therefore have the same name, so I can't use SQL to retrieve them. My
question is: what i...Need function to return numbers at beginning of text
I have a column of cells that contain an id and description in one column. I
need a formula that will give me only the id. The id can be either 4 or 5
numbers. Some can be followed by a letter and some can be followed by a dash
and then a number. What they all have in common is that the id is followed
by a space and then the description.
So, the =LEFT formula will not work. I need on that can give me all
characters before the space regardless of whether that's after the first 5
characters or after 7 or 8 or however many there are before the space.
Anyone know of such...how do i put a power to a number?
i dont know how to raise a number to any power .
will some1 pla help me
^ is the power operator, like in =A1^3
Microsoft MVP - Excel
"help needed" <help email@example.com> wrote in message
>i dont know how to raise a number to any power .
> will some1 pla help me
Search XL help for 'exponentiation' (w/o the quotes). One of the
suggested topics will be 'About calculation operators' and the subtopic
'Types of operato...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" <firstname.lastname@example.org> wrote in
news:email@example.com...Converting Leads #2
When we convert leads and choose disqualify as the option, we get a drop down
with a few selections (lost, canceled, etc.)
Can we control this drop down to add more reasons to the field?
If so, how do i go about making that change?
On May 13, 5:55=A0am, Tiffany <Tiff...@discussions.microsoft.com> wrote:
> When we convert leads and choose disqualify as the option, we get a drop =
> with a few selections (lost, canceled, etc.)
> Can we control this drop down to add more reasons to the field?
> If so, how do i go about making that change?
> T...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?...Custom Number Formats #2
How do I create a custom number format to return thousands e.g. if the value
is 1000 the format shows 1
Under Format -> Cells in the Custom format box type
(make sure you put the comma in). This will only show the thousands part,
note that it will round numbers like 2598 up to 3 instead of 2.
"Mary Ann" wrote:
> How do I create a custom number format to return thousands e.g. if the value
> is 1000 the format shows 1
Try this custom number format:
...Date Formula #2
I am trying to write a formula that works off what is in two separate cells.
It has several parts and maybe not all of them can be done. It would be
great if someone could help me out. Here goes.
If A2 & A3 each have a "N" in it then I want cell A5 to show today's date
plus 20 weeks. However, if only 1 of the cells A2 or A3 has a "N" in I still
want A5 to show today's date plus 20 weeks.
Can this also be done? If both A2 & A3 have "Y" in them then I want A5 to
freeze with the date last show when 1 of the two cells had a "N" it ...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...The number of new home buyers slipped for the fourth consecutive month as the red-hot housing market began to cool amid rising mortgage rates and the biggest home price increase in 17 years, say repor
The number of new home buyers slipped for the fourth consecutive month
as the red-hot housing market began to cool amid rising mortgage rates
and the biggest home price increase in 17 years, say reports issued
Thursday.Both Statistics Canada and the Canada Mortgage and Housing
Corp. released details on the national housing market that show
rocketing prices, especially in booming Western Canada. Statistics
Canada said new housing price index rose 1.2 per cent in April to
138.2, the biggest month-to-month jump in 17 years.
Visit www.canadianhomemortgages.com for more info and save your money.
...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...Summing a group of numbers
Awhile back I could hold down the right mouse button and select a group of
numbers. The sum of these numbers would show on the bottom of the page. I
have lost the ability to do this. Is there a way to get it back?
See your other post.
"Barbie Da" <BarbieDa@discussions.microsoft.com> wrote in message
> Awhile back I could hold down the right mouse button and select a group of
> numbers. The sum of these numbers would show on the bottom of the page.
> have lost the ability to do this. Is ther...Outlook XP on windows 2000 #2
I am using the following version of Outlook "Microsoft
Outlook 2002 (10.4510.4219) SP2" on Windows 2000. The
problem I am having the name resolution from the address
book is very slow or freezes and I have tried all possible
ways to fix with no avail, is this a know issue. Can
anyone provide me with a resolution. Appreciate any help...
...2-Sided Printing: HP Photosmart D7300 Series. Right edge of image clipped off, won't allow manual feed
We are wasting tons of paper trying to print a "booklet" 2 sided.
Printer: HP Photosmart D7300
Booklet 5.5 x 8.5
Printer set at landscape
Ran 2-side configuration wizard. Did all paper feeds (numbers and
When attempting to print, right side is clipped off
No prompts appear to turn paper over.
When printer is set to BORDERLESS, the full right side of image
appears, but left side of page is trimmed off.
"fit image to paper" option vanishes.
Is there a way to do this?
I'm trying to set-up an invoice using thr Sales invoice template on excel, is there any way that, every time you open the invoice worksheet that it auotmatically count's up 1
Below is a post I found by Frank Kabel. It might be just
what you need:
one way (using the Windows registry for storing the last
number). Put the following code in the workbook module
(not in a standard module) of your template:
- It changes cell A1 of the first sheet
- you may change the key identifiert according to your
needs (e.g. DEFAULTSTART, MYLOCATION, etc.)
Private Sub Workbook_Open()
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...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...PAYROLL QUESTION #2
WHERE CAN I FIND A TEMPLATE FOR PAYROLL WITH CORRECT TAX DEDUCTIONS OF THE
You can use Google to search for Excel Payroll Templates. I would guess
that the most useful templates will not necessarily have this year's
data, but will allow you to enter the tax rates yourself.
If you need specific help, it would probably be useful to indicate which
country, state/province/taxing district you're looking for.
Please also turn off your CAPS LOCK. It makes your posts much more
difficult to read, and is considered the equivalent of shouting in
In ar...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...Delete Rows #2
I would like to be able to delete a row with a name in it based on a
cell with that name from another cell in a different worksheet. Any
This example loop through row 1 - 100 on the activesheet and if the value in A is the same as in
Sheets("Yoursheet").Range("A1").Value in delete the row
Maybe you can use a Autofilter if you have many rows (faster)
See this page for more info
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim End...Sheet Reference
Is there a formula for listing the current Sheet. I know
about the filename cell("filename",A1) which returns the
entire path, but I just want the Sheet name to appear.
You still use the CELL("filename") function, but you need to
trim the result a bit. Like this:
Microsoft MVP - Excel
"John" <email@example.com> wrote in message
> Is there a formula for listing...