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...match name fields to determine differences #2
I have two worksheets that have common columns for last name and first
name. I want to determine which rows are in both worksheets based on
the last name and first name. The concatenate function with vlookup
is an option, is there an easier way?
I will have to assume that first names are in column A and last names in
And that there are 300 names on sheet1 with headings.
The first sheet is called "sheet1"
It doesn't matter how many names are on sheet 2.
In the second sheet cell C2 enter
You may have to change it around to match your information.
I am writing an MFC UI active x control!
The control work fine when it was in a shape of dialog wit buttons, but then
I change the control UI to be in the form of tab control so I use the tab
control but when I put this control in a container application it cause the
application to stack when I press the OK button of the tab control I change
the tab control inside my active-x to property sheet but I get the same
Can I use property sheet control or tab control inside my MFC active x
control or I cant use those controls???
...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...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?...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...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 ...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:
...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...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...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...Missing References
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...Appointments #2
Sorry to repost but this is driving me crazy.
All I want to be able to do is schedule an appointment for myself and
a colleague with an Account record and get it to appear in our outlook
calendars. Everything I try to achieve this fails:
1. If I try to schedule an appointment in the SFO client against an
account record (Actions Add Activity etc) and add my colleague as an
attendee then the appointment goes in to my outlook calendar but not
my colleagues. Instead I get a box pop up called check names. It
basically says that Outlook does not recognise the user (my colleague)
who I selected as ...Search for names
I have a list, "sponsor", with several names in it. I have rows of data
where names appear in some cells (for instance, d1, e1, f1, g1, h1 have
names). The names do not repeat in a row. Some names in the data will not
be in my "sponsor" list. If one name from my "sponsor" list is in a row, no
other names from that list will be in the row.
Can I search each row for names in the list and, if found, print the name to
a target cell?
...Mail Stuck in Outbox
I just upgraded to Outlook 2002 SP-2 from Outlook 2000 while there was a
message stuck in the outbox and now I can't check mail or delete the message
from the Outbox. When I try to Send/Receive mail, I get the really
enlightening message: "Operation Failed". When I try to delete the trapped
message, I get: "The messaging interface has returned an unknown error. If
the problem persists restart Outlook". Restarting Outlook changes nothing,
of course. Outlook Express works fine.
If only I could just delete that message in the Outbox! Does anybody have
any idea how to fix...Copying worksheets with hyperlinks to named cells
I have a spreadsheet that I am working to collect data for several different
products. I developed a skeleton worksheet that will be copied within the
same workbook and re-named with each individual product. In the skeleton
sheet, I have hyperlinks near the front of the sheet that reference to areas
later in the sheet (via named cells).
I copied this skeleton sheet and gave the copy another name ('product 1' for
example). When I click the hyperlinks in the 'product 1' worksheet, it links
back to the 'skeleton' worksheet.
Is there an easy way to have the lin...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...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...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...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...2 Different print areas?????????
Hello. I would like to print a sheet, and I want to leave out 1 row. I
just want the printer to skip it and keep going. When I assign print
areas, the computer splits it up onto 2 pages. Any way to get around
mcr1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15496
View this thread: http://www.excelforum.com/showthread.php?threadid=277504
"mcr1"...Purchase Order Enhancements #2
I have a user ID who reports to 2 seperste line managers, but the PO
Enhancements screen does not support this scenario.
The line managers do not want to see everybody's orders so this option is
not a possibility.
Two separate user ids for the user, depending on which department they are
Richard L. Whaley
Author / Consultant / MVP
Documentation for Software Users
For help learning and better using Dynamics GP,... check out our books at
"Keith McConville" wrote:
> I have a user ID who repo...