Referring to Names #2

Hi Frank

I'm looking for something that's easy to copy across.

Say column A contains an alpha and columns B to X contain variou
numerical values with range created names of Bdata to Xdata.

I'm wondering if there is any way of writing the formul
Sumif(Alpha,A,Bdata) by referring Bdata to a cell reference instead o
using the term "Bdata" itself, so that I can change the cell and brin
up the relevant result.  

Hope this makes sense

Timmy Ma

--
Timmy Mac
-----------------------------------------------------------------------
Timmy Mac1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1518
View this thread: http://www.excelforum.com/showthread.php?threadid=26820

0
10/11/2004 8:21:05 PM
excel 39879 articles. 2 followers. Follow

1 Replies
687 Views

Similar Articles

[PageSpeed] 30

Hi
try
=SUMIF(A1:A100,"A",OFFSET(A1:A100,MATCH("Bdata",A1:X1,0)))

--
Regards
Frank Kabel
Frankfurt, Germany

"Timmy Mac1" <Timmy.Mac1.1dzbby@excelforum-nospam.com> schrieb im
Newsbeitrag news:Timmy.Mac1.1dzbby@excelforum-nospam.com...
>
> Hi Frank
>
> I'm looking for something that's easy to copy across.
>
> Say column A contains an alpha and columns B to X contain various
> numerical values with range created names of Bdata to Xdata.
>
> I'm wondering if there is any way of writing the formula
> Sumif(Alpha,A,Bdata) by referring Bdata to a cell reference instead
of
> using the term "Bdata" itself, so that I can change the cell and
bring
> up the relevant result.
>
> Hope this makes sense
>
> Timmy Mac
>
>
> --
> Timmy Mac1
> ---------------------------------------------------------------------
---
> Timmy Mac1's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=15188
> View this thread:
http://www.excelforum.com/showthread.php?threadid=268206
>

0
frank.kabel (11126)
10/11/2004 8:44:38 PM
Reply:

Similar Artilces:

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. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi 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 column B. 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. =IF(SUMPRODUCT((Shee...

Active-x #2
Hi, 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 problem! why??? Can I use property sheet control or tab control inside my MFC active x control or I cant use those controls??? Thanks ...

Need to reference a block of cells from another sheet.
Dear All, 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? Regards Paul. 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 http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/f088a5737dd26570 thank you Is there some reason you can't post your question here instead of asking us to look elsewhere? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Blinds Nottingham" <bradrail.blinds.awnings@googlemail.com> wrote in message news:d8768e43-e035-45eb-856a-0ccdc843441d@g1g2000yqi.googlegroup...

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? Thanks Tiffany 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 = 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? > > 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?? Hi Jeff this is a Excel restriction. Excel can only open one file with the same name (though they are in different directories) -- Regards Frank Kabel Frankfurt, Germany "Jeff C." <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:15ee401c41e38$58f639b0$a001280a@phx.gbl... > 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. Andrea Jones http://www.allaboutoffice.co.uk http://www.stratatraining.co.uk http://www.allaboutclait.com "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: 0, Or: ...

macro button refers to prev. workbook?
Hi, 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 previous workbook... Sub test() ' Dim name As String Dim adress As String Sheets("sheet1").Select Range("A1").Select 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 Dave Tools>Options>View. 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> wrote: >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.. ssuryarao@gmail.com wrote... >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 available references. 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
Hi, 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 condition? such as : CImageList & imageList; if (thumbnail == 1) imageList = m_ImageListThumb; else imageList = m_ImageListFullSize; Hope this makes sense! Ben 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 referencing it. "Ben Williamson" <oakdaleclose@googlemail.com> wrote in message news:43ee4603...

PAYROLL QUESTION #2
WHERE CAN I FIND A TEMPLATE FOR PAYROLL WITH CORRECT TAX DEDUCTIONS OF THE CURRENT YEAR? 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 newsgroup etiquette. 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 ideas? Thanks, Bernie Hi Bernie 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 http://www.rondebruin.nl/delete.htm Sub Example2() 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
Hi, 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. John, You still use the CELL("filename") function, but you need to trim the result a bit. Like this: =MID(CELL("filename"),FIND("]",CELL("filename"))+1,99) -- Ture Magnusson Microsoft MVP - Excel Karlstad, Sweden "John" <anonymous@discussions.microsoft.com> wrote in message news:0c6001c3a856$291c9a20$a501280a@phx.gbl... > 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 that? Miles -- mcr1 ------------------------------------------------------------------------ mcr1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15496 View this thread: http://www.excelforum.com/showthread.php?threadid=277504 Hi check out: http://www.rondebruin.nl/print.htm#non-contiguous -- Regards Frank Kabel Frankfurt, Germany "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. Any ideas. -- K.McConville Two separate user ids for the user, depending on which department they are working. -- Richard L. Whaley Author / Consultant / MVP Documentation for Software Users For help learning and better using Dynamics GP,... check out our books at http://www.AccoladePublications.com "Keith McConville" wrote: > I have a user ID who repo...