Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF
Hi,
1) Thanks to Tom Ogilvy who posted the earlier reply about the SUMIF function. This is what I was looking for, however it dosnt seem to handle text very well. What I want to do is to count the number of times a string appears in a column of data ( in a different workbook). However the way this string has been input means that there may be white space at the start or end of the string. Is there any way to get the SUMIF function to say IF A contains B, THEN ++ ?? ( as opposed to IF A = B). 2) Also is there anyway to work with spaces - I have a lot of worksheets to be referenced but they all contain spaces, and currnetly it wont let me work with these. 3) Is there any neat way of referencing the current sheet in a formula. See I have 2 Excel files, A , B. Both files have exactly the same number of worksheets ( each worksheet in A has a coressponding one in B, named identically). So what I want to do is to reference the coressponding worksheet in the other file from the current worksheet, without explicitly naming that worksheet ( so I can just copy it to all other worksheets - I will eventually have over 60 !! ) Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF
Dave wrote
Hi, 1) Thanks to Tom Ogilvy who posted the earlier reply about the SUMIF function. This is what I was looking for, however it dosnt seem to handle text very well. What I want to do is to count the number of times a string appears in a column of data ( in a different workbook). However the way this string has been input means that there may be white space at the start or end of the string. Is there any way to get the SUMIF function to say IF A contains B, THEN ++ ?? ( as opposed to IF A = B). Look at the TRIM function. Without knowing exactly what you require I am not sure where the best place for you to use it would be but it will do what you want. Maybe you need a macro called Clean that you use to tidy up your worksheets a little. It could remove all the leading spaces etc. Then you can work with your data knowing that it is correctly formatted. 2) Also is there anyway to work with spaces - I have a lot of worksheets to be referenced but they all contain spaces, and currnetly it wont let me work with these. Do you mean spaces in workbook names? ... in worksheet names? If not, where are these spaces? You could remove them with a find/replace macro. 3) Is there any neat way of referencing the current sheet in a formula. See I have 2 Excel files, A , B. Both files have exactly the same number of worksheets ( each worksheet in A has a coressponding one in B, named identically). So what I want to do is to reference the coressponding worksheet in the other file from the current worksheet, without explicitly naming that worksheet ( so I can just copy it to all other worksheets - I will eventually have over 60 !! ) If both workbooks are open the you can do this: Add a named range to the worksheet by using menus Insert -- Name -- Define Give the range the name xxx!SheetName where xxx is the current sheet name. In the refers to text box enter a cell reference where you can put the name of the sheet. If the sheet name has spaces in it then use single quotes around the sheet name like this 'xxx xxx'!SheetName When you want to use the sheet name in a your formula then use this assuming your other workbook is called X1: =INDIRECT("[X1.xls]" & SheetName & "!A1") If you make sure your range called SheetName can be in the same place on each worksheet then you can create one name and get Excel to do the others for you. To do that you enter =SheetName in a cell on the worksheet where you inserted that name. Now copy this cell with the formula in it and paste it to each other worksheet. It will create the name for you. You can delete the formula once you have pasted it to each sheet cos it has no useful purpose. Now you just have to put the sheet name into each of these cells. Both workbooks need to be opened to use the indirect function as it does not work on a closed book. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF and IF | Excel Worksheet Functions | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |