Indirect and Sheet Name

I have a formula =COUNTA(INDIRECT(A3)) which returns the count of items in a 
range which is defined in cell A3, the formula in A3 is below.  Is there a 
way of not having to include the sheet name (Sheet 1) in cell A3 and to 
include in the COUNTA formula.

="'Sheet 1'"&"!C"&A1&":"&"C"&B1

Thanks, Rob 


0
Rob
3/23/2010 9:38:48 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
905 Views

Similar Articles

[PageSpeed] 14

Two possibilities
a) replace the A3 formula by ="C"&A1&":"&"C"&B1
and replace the COUNTA by =COUNTA(INDIRECT("'Sheet1'!"&A3))

or, better still
b) do away with the the A3 formula all together and use
=COUNT(INDIRECT("'Sheet1'"&"!C"&A1&":"&"C"&B1))

best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Rob" <anonymous@discussions.microsoft.com> wrote in message 
news:eARa7EtyKHA.2552@TK2MSFTNGP04.phx.gbl...
> I have a formula =COUNTA(INDIRECT(A3)) which returns the count of items in 
> a range which is defined in cell A3, the formula in A3 is below.  Is there 
> a way of not having to include the sheet name (Sheet 1) in cell A3 and to 
> include in the COUNTA formula.
>
> ="'Sheet 1'"&"!C"&A1&":"&"C"&B1
>
> Thanks, Rob
> 
0
Bernard
3/23/2010 10:05:19 PM
If you leave out the sheet name then the formula will apply only to the 
current sheet and it would look like this: ="C"&A1&":"&"C"&B1.

Or, you could ignore cell A3 and use a formula like this:
=COUNTA(INDIRECT("C" & A1 & ":C" & B1))

Tom

"Rob" wrote:

> I have a formula =COUNTA(INDIRECT(A3)) which returns the count of items in a 
> range which is defined in cell A3, the formula in A3 is below.  Is there a 
> way of not having to include the sheet name (Sheet 1) in cell A3 and to 
> include in the COUNTA formula.
> 
> ="'Sheet 1'"&"!C"&A1&":"&"C"&B1
> 
> Thanks, Rob 
> 
> 
> .
> 
0
Utf
3/23/2010 10:35:01 PM
Hi Rob

Another alternative, would be to use Index rather than the volatile 
Indirect function

=COUNTA(INDEX(C:C,A1):INDEX(C:C,B1))

--
Regards
Roger Govier

Rob wrote:
> I have a formula =COUNTA(INDIRECT(A3)) which returns the count of items in a 
> range which is defined in cell A3, the formula in A3 is below.  Is there a 
> way of not having to include the sheet name (Sheet 1) in cell A3 and to 
> include in the COUNTA formula.
> 
> ="'Sheet 1'"&"!C"&A1&":"&"C"&B1
> 
> Thanks, Rob 
> 
> 
0
Roger
3/24/2010 8:40:08 AM
Thanks everyone, lots of optiosn to try out.

Regards, Rob

"Bernard Liengme" <bliengme@TRUENORTH.stfx.ca> wrote in message 
news:ufz7wTtyKHA.928@TK2MSFTNGP05.phx.gbl...
> Two possibilities
> a) replace the A3 formula by ="C"&A1&":"&"C"&B1
> and replace the COUNTA by =COUNTA(INDIRECT("'Sheet1'!"&A3))
>
> or, better still
> b) do away with the the A3 formula all together and use
> =COUNT(INDIRECT("'Sheet1'"&"!C"&A1&":"&"C"&B1))
>
> best wishes
> -- 
> Bernard Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
>
>
> "Rob" <anonymous@discussions.microsoft.com> wrote in message 
> news:eARa7EtyKHA.2552@TK2MSFTNGP04.phx.gbl...
>> I have a formula =COUNTA(INDIRECT(A3)) which returns the count of items 
>> in a range which is defined in cell A3, the formula in A3 is below.  Is 
>> there a way of not having to include the sheet name (Sheet 1) in cell A3 
>> and to include in the COUNTA formula.
>>
>> ="'Sheet 1'"&"!C"&A1&":"&"C"&B1
>>
>> Thanks, Rob
>> 


0
Rob
3/24/2010 8:56:09 PM
Reply:

Similar Artilces:

variable column names in a report
i have an application where the user can enter self selected column names which then in turn are added to a table... application works fine. however, when it comes to reporting, how do i go about adding those self entered columns in a report? any help would be appreciated Do you actually need this in a report or can the results be pushed to Excel for printing? -- Duane Hookom MS Access MVP Help me support UCP http://www.access.hookom.net/UCP/Default.htm "k2sarah" <k2sarah@discussions.microsoft.com> wrote in message news:C56B6FD1-2F5B-45D5-8760-A61E3CA41495@microsoft.c...

Domain name msoffice.com.xx (Is it legal?)
I've searched through the web & found "msoffice.com.ua" being a registered entity in Russia. I intend to register a domain name also using "msoffice.com.<<our country>>" for Microsoft office consulation purpose. Microsoft itself is using "office.microsoft.com". Eventhough we click to "msoffice.com", it will still point to "office.microsoft.com". Does anybody know if the public can REALLY legally use "msoffice.com.<<our country>>" as a domain name? ...

How use Property sheet from a DLL library?
Hi, All, I want to create a common extension DLL library that only includes one property Sheet with more than one property pages. this DLL also includes a invoke API that can be called by Win32 application. my problem is that I can't Add property pages to property sheet and error message shows Resource was unvailable inside invoke API. below is some sample code: In MyProp.dll includes these: 1) void InvokeApi(HWND Hwnd, HINSTANCE hLib, ...); 2) class CPage1: public CPropertyPage{ ...} 3) class CPage2: public CPropertyPage{ ...} 4) class CMySheet: public CPropertySheet{ ...} here is I...

how do I define a chart series with an indirect reference
I've made a XY scattergram that I want to copy for use to other workbooks with other data series. The exact part of the data series to be displayed in the chart varies between workbooks. In the data series for the chart, I'd like to include an indirect reference that will define the first and the last dat point to be displayed. How can I achieve this? A related question: how can I copy charts beween workbooks in a way that the reference to the original workbook is not being copied at the same time. I.e. I want the displayed data to be taken from the workbook I'm copying the ...

want to do my own funeral srevice sheets ????? help!!!
Look at the built in templates, there are programs you could modify. File, new, Publications for Print, scroll down to programs. 2007, on the task pane, Apply a template, scroll down to programs -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "funerals" <funerals@discussions.microsoft.com> wrote in message news:D730B75C-8343-483C-BF29-F5C8A9201745@microsoft.com... > ...

Sorting Cube dimension names in a Pivot Table
How do I get the dimension names in the "pivot table field list" sorted by the translated names? The pivot table field list contains: - one measure-node - one dimension node for each cube-dimension. The cube-dimension-nodes are represented by their translated names. It looks like, this names are not sorted by this translated names, but by the order of the data source. The related radio-buttons in the pivot-table-options are greyed ("A to Z" and "sort by data source"). I would need the "A" to "Z" order. Pleas notice! I...

database query/names issue, causing excel to be sluggish
I have an issue with an excel worksheet I've created. WorkbookA has "Names" which are used to define drop-down menus in WorksheetA. These "Names" are defined to reference a WorksheetB (also in WorkbookA) which itself is populated by a database query, pulling from another Excel WorkbookB. The logic is that WorkbookB should not be touched by the User, only an Admin, and Workbook A is used by User. The Names in WorkbookA are to prevent incorrect entries. Now that I've set it up, WorkbookA is moving really sluggish, but if I delete all the Names it speeds up again....

Can't open two Documents with same name !!
Hi: I install Office 97 in WinXP, works good except when I open a excel spreadsheet a windows always opens saying Can't open two Documents with same name. I click ok and everything works OK. How do I stop this warning I get all the time when I first open a Spreadsheet. Thanks Debra Dalgleish has a list of frequently asked questions at: http://www.contextures.com/xlfaqIndex.html This one sounds like yours: http://www.contextures.com/xlfaqApp.html#AlreadyOpen Owen wrote: > > Hi: I install Office 97 in WinXP, works good except when I > open a excel spreadsheet a windows alway...

Adding sheets #2
HI, I have a file I got from a friend which has 2 worksheets in it. I want to add a sheet or make a copy of one of the sheets, but all those options are disabled. Is there a way I can undo whatever has been done on this file to add a sheet? Thanks Ramesh Ramesh, Check that the workbook isn't protected. You don't want it protected. Tools - Protection - Unprotect Workbook. If you see Protect Workbook, it's not protected. -- Earl Kiosterud www.smokeylake.com "Ramesh" <ramborums@@yahoodotcom> wrote in message news:ubVcPxmjFHA.3348@tk2msftngp13.phx.gbl......

Error message with copying sheet to another workbook
I always encounter an error message when i copy a sheet from one workbook to another workbook. The error message is as below: ================ A formula or sheet you want to move or copy contains the name '____123graph_A', which already exists on the destination worksheet. Do you want to use this version of the name? - to use the name as defined in the destination sheet, click Yes - to rename the range referred to the formula or worksheet, click No, and enter a new name in the Name Conflict dialog box. ================ I have tried to click No and enter a new Name, but afterward...

How do I import data from 1 workbook excel sheet into another?
I have an excel spreadsheet where I want to import the data from one worksheet into another worksheet I am also using. The data from this worksheet will be constantly changing so I would need the new data to also be exported onto the worksheet. Hi LJ- Try: =DAYS360(StartDate,EndDate)/30 and be sure to format the cell as Number or General with the appropriate number of decimal places. HTH |:>) "LJHolland" wrote: > I have an excel spreadsheet where I want to import the data from one > worksheet into another worksheet I am also using. The data from this > works...

Chart sheet causing error
Hi all, I'm having a problem at the last line of this code: For Each ws In Workbooks(sFile).Sheets If ... Then cboSheet.AddItem ws.Name End If Next The problem occurs when I step into a chart sheet, which results in a type mismatch error. Why does that happen how do you handle that? Gustaf You probably have ws defined as a variable of type worksheet. Either change it to Object, or outsort the charts n the code. HTH Bob "Gustaf" <gustafl@algonet.se> wrote in message news:ulMjdDCmKHA.1536@TK2MSFTNGP06.phx.gbl... > Hi all, &g...

Using REPT funtion with dates between sheets and workbooks
I am trying to repeat dates in several different places, so that I onl have to type it once. However, when I use the REPT function I get number instead of a date format. I've tried to format the cells back t date function, but it doesn't change -- ROCKWARRIO ----------------------------------------------------------------------- ROCKWARRIOR's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2709 View this thread: http://www.excelforum.com/showthread.php?threadid=46828 The REPT function repeats 'whatever' the number of times you set it to, it wil...

changing sheet size
We have a worksheet that is maxed out to 65536 rows & also lots & lots of columns. No need for that we could use 1000 rows & 20 columns. How can we do this? TIA -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "mmap hq" <mmaphq@netzero.net> wrote in message news:uHwBsMl$JHA.1336@TK2MSFTNGP05.phx.gbl... > We have a worksheet that is maxed out to 65536 rows & also lots & lots of > columns. No need for that we could use 1000 rows & 20 columns. How can we > do this? TIA > If you look...

How to count last name letters
Hi folks, I would like to know how I can count how many records have the same letter. For example, how many records start with the letter A, how many for letter B, etc. Seems simple enough, but I can't figure out how to work this. Would appreciate some help. Thanks. In news:uKD40b2rHHA.3484@TK2MSFTNGP05.phx.gbl, jason <jasonsantos-NOSPAM-@allstatesmedical.com> wrote: > Hi folks, > I would like to know how I can count how many records have the same > letter. For example, how many records start with the letter A, how > many for letter B, etc. > > Seems simple ...

How and where rename tab's name?
...

Cannot Omit or Change Name When Printing in Memo Style- Office 2003
Cannot Omit or Change Name When Printing in Memo Style by outlook 2003. Any help is appreciated. Leila Hello Leila, mmh, which name? Your name on the top? You could change it in the Account Options under Username. >-----Original Message----- >Cannot Omit or Change Name When Printing in Memo Style by >outlook 2003. Any help is appreciated. >Leila > >. > No, printing is pretty limited... You could hit forward or reply and print, then close the message. Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Vis...

Want to auto name worksheets
I work with a large spreadsheet that has several worksheets in it. Is it possible to create a macro that can copy the contents of a cell and then paste that as the new name for the worksheet? Reply to doc_cowher@yahoo.com Thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Doc, this will do what you want, Sub Name_Sheet() ActiveSheet.Name = [A1] End Sub -- Paul B Always backup your data before trying something new Using Excel 2000 & 97 Please post any re...

Financial Calculation across sheets
Need some help with a formula. Sheet1; A1=$2200 A2=$150.00 A3= $1800 A4=SUM(A1:A3) answer is $4150 Sheet2; A1=1029514 A2=2663427 A3=(A1/A2) answer is rounded two two decimal places (.39) Sheet3; A1=SUM(Sheet1!A4/(1-(Sheet2!A3+.1))) answer comes out $8082.38 Equation should calculate $8137.25 =4150/(1-(.39+.1) How do I get this to calculate. Any help would be appreciated. Thanks Hi Dave: The problem is the .39 You have adjusted the display to present .39, but the value Excel is using is 0.386537345 In Sheet2,A3 use =ROUND(A1/A2,2) -- Gary's Student "Dave Keister"...

Excel sheet is missing in application
Hi I am not able to open my excel sheet by doubleclicking on it. The excel sheet is missing from the excel application (leaves a gaping hole in the center) with only the menu bar visible. But am able to do the same by running a instance first (from RUN) and then opening it from there. Any way out? Dey Are you sure it's not opening? Maybe it's just off the screen. Try clicking on Window on the toolbar. Do you see your workbook name at the bottom of the list? If yes, try Window|Arrange|Tiled and then resize by hand (and then save it). If it's not really there, do you get...

Report using Fields with the same name from different tables
Hello all - I created a db with no knowledge of naming convention. Now I am attempting to write some reports pulling data from tables with the same field names. Apparently Access doesn't like this....I attempted to change the names in the BE but when I relink the FE it is prompting for parameters. What is the best way to proceed? Thanks - Lee -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201005/1 TheLee via AccessMonster.com wrote: >I created a db with no knowledge of naming convention. Now I am attempting to &...

2006 Isn't Adding Payee Name Variations
I like using the Payee Name Variation in MS Money. It was working exactly liked I wanted it to in MS Money 2005. Now that I have Money 2006, it isn't working at all and I THINK I have the same options checked in Online Settings. In 2005, I would get a downloaded payee like ALLSTATE INS CO INS PREM. I would change it to Allstate Insurance (before accepting)and the downloaded name would automatically pop into the Payee Name Variation box when I acceped the transaction. In 2006, if I change the downloaded payee before accepting, nothing is added to the Payee Name Variation box. I am having ...

INDIRECT doesn't retain data from external worksheets
I'm using the INDIRECT function to bring in data from other spreadsheets. Using this allows me to just populate a cell and automatically point to a new spreadsheet (I have dozens of spreadsheets that hold similar data, and dozens of formulas that pull that data over, so I don't want to manually edit all the formulas). However, it appears that when I use this, the file that it points to needs to be open. I have a fully qualified path ("C:\Documents and Settings\......"), so I don't understand why the document needs to be open. If I don't use INDIRECT, ...

Share sheet automatic on the internet.
Hello, I've got this spreadsheet where in I want one of the worksheets to be visible on the internet. This particular sheet is a sheet that shows the previous sheets making use of the camera tool in Excel. Is there a way to make Excel save and publish this sheet every time it is updated? Can Excel do this by FTP? There is no need to make it interactive. I have a server where I can make a special user and a password for this job. Could this be done with a macro? Thanks!! I think there is an option AutoRepublish every time this workbook is saved. you need chekc this option. On Oct 22, ...

Question about auto fills or linking sheets
Hello all. I am very new to excel and want to know if this is possible. I want to enter a name into box I6 and have it automatically fill in box B6, C6,D6 AND K6 on sheet 1, when I hit enter or toggle one of the arrow keys to another box.. I have about 200 or so different items that can go into row I. Sheet 2 has all the serial numbers and stock numbers etc. Row B,C,D and K are names of the items in the inventory, basically a combination of numbers and names with no math involved. Just serial numbers and stock numbers, that sort of thing. What I need is to be able to enter the common na...