Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
truncating text strings of different lengths | Excel Discussion (Misc queries) | |||
How do I count wildcard text meeting certain criteria in EXCEL? | Excel Worksheet Functions | |||
Count by Colour (Text) with other criteria | Excel Discussion (Misc queries) | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions |