Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default count text strings in an entire workbook

I'm having a problem which led to another problem. I'll tell you about both
because either solution would get me to where I wasnt to be.

1. I created an excel workbook which is serving as a template. In its base
case version there are four tabs with the titles: bond 1 present, bond 2
present, etc. An additional tab called "pointers" is up front with input
info for each case 1-4, or more cases as necessary (these toggles are
referred to in the individual "present" tabs).

Each of these tabs compute results relying on those inputs. I also have
cell S2 in each worksheet which has the typed in number 1, 2 , 3 or 4
corresponding to the number in the worksheet tab. What I'd like to do is
allow the user to create a 5th tab (by duping any of the first 4) which will
automatically compute results for
case 5, base on the 5th row in the inputs tab. And I dont want the user to
have type the new number in cell s2.
I tried a formula
=MID(CELL("filename"),FIND("Pres",CELL("filename") )-3,2)*1, which extracts
the number from the tab label (which the user would have to type in). But
this only calculates correctly for the active tab for some reason.
Unfortunately, all of the computations in the other four tabs would be based
off this number so it screws everything up.

Does anyone have another solution to making this dataentry free (other than
entering the name of the new tab)?
I do not want to use VBA here is this is a VBA free version of another
template.

2. One solution I thought would work is extracting the word "present" from
each tab and running a countif for that text string in the entire workbook.
I think this will work, but how do you use countif for the entire workbook
when you won't know the last tab name until the user adds it?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 132
Default count text strings in an entire workbook

You could use a named range that has a formula of "=GET.DOCUMENT(87)" This
will return the index of the current sheet (if you are on the fifth sheet, it
will return 5). If you know that you have some non-counted sheets, then
subtract the number of non-counted sheets from the function.

This still requires that the use manually change the tab name but I can't
currently think of a way to change the sheet name non-programatically.

Regards,
Bill


"Dave Breitenbach" wrote:

I'm having a problem which led to another problem. I'll tell you about both
because either solution would get me to where I wasnt to be.

1. I created an excel workbook which is serving as a template. In its base
case version there are four tabs with the titles: bond 1 present, bond 2
present, etc. An additional tab called "pointers" is up front with input
info for each case 1-4, or more cases as necessary (these toggles are
referred to in the individual "present" tabs).

Each of these tabs compute results relying on those inputs. I also have
cell S2 in each worksheet which has the typed in number 1, 2 , 3 or 4
corresponding to the number in the worksheet tab. What I'd like to do is
allow the user to create a 5th tab (by duping any of the first 4) which will
automatically compute results for
case 5, base on the 5th row in the inputs tab. And I dont want the user to
have type the new number in cell s2.
I tried a formula
=MID(CELL("filename"),FIND("Pres",CELL("filename") )-3,2)*1, which extracts
the number from the tab label (which the user would have to type in). But
this only calculates correctly for the active tab for some reason.
Unfortunately, all of the computations in the other four tabs would be based
off this number so it screws everything up.

Does anyone have another solution to making this dataentry free (other than
entering the name of the new tab)?
I do not want to use VBA here is this is a VBA free version of another
template.

2. One solution I thought would work is extracting the word "present" from
each tab and running a countif for that text string in the entire workbook.
I think this will work, but how do you use countif for the entire workbook
when you won't know the last tab name until the user adds it?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
truncating text strings of different lengths Funkey Excel Discussion (Misc queries) 7 March 29th 06 07:22 PM
How do I count wildcard text meeting certain criteria in EXCEL? cybermaksim Excel Worksheet Functions 1 February 17th 06 04:03 AM
Count by Colour (Text) with other criteria Paul Sheppard Excel Discussion (Misc queries) 1 January 18th 06 04:41 PM
Count Position of Filtered TEXT cells in a column Sam via OfficeKB.com Excel Worksheet Functions 8 May 18th 05 04:23 AM
Count Position of Filtered TEXT cells in a column Sam via OfficeKB.com Excel Worksheet Functions 0 May 15th 05 08:14 PM


All times are GMT +1. The time now is 11:36 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"