Below is a formula that I use which now excceds the maximum length. =Consumables!S56/('Price list'!S56+'Price list'!S57+'Price list'!S58+'Price list'!S59+'Price list'!S60+'Price list'!S61+'Price list'!S62+'Price list'!S63+'Price list'!S64+'Price list'!S65 "all the way up to" +'Price list'!S118) This formula is also repeated is cells =Consumables!T57/('Price list'!S56+'Price list'!T57...... etc all the way =Consumables!AE56/('Price list'!AE56+'Price list'!AE57.............
Is there any way I can condense this formula? I have tried:
I use VLOOKUP with text (to return comments made by people that I have copied in another sheet). The problem is that sometimes, it doesnt copy the whole comment. Apparently there is a limit for the amount of text VLOOKUP can copy: after a LEN() test I have found that I cant copy texts longer than 255 characters.
Is there a simple way to make the VLOOKUP work even if the text is more than 255 characters long ?
I have looked into the maximum length of a variant/string in vba and it appears to be 250 characters. I am running a macro which first lists all excel files in a folder, returning them to a sheet, then using a loop statement opens each one in turn extracting the information to a second summary sheet before closing it. The file path to the folder is ridiculously long and the macro stumbles. I used the =LEN(A1) formula to check if the file names were too long for the string, but the maximum file name length was 226 characters. I've tried both String and Variant to collect the file names but both have the same effect.
I've set up a filing system which saves sheets/ workbooks based on the value of a cell - Range("B1") Everything works great apart from when ThisFile String length exceeds 31 characters which you may know is the max useable character length for a sheet name - I had no idea! 8-0
Is there a way i can check if string length exceeds 31 characters then, if it does, shorten it to 31 characters?
My worksheet contains data with the reaction times on a psychological test. Each respondent in the test has 280 rows in my excel sheet.
The 'perfect' length of the row, is from A to M. When an error is made in the test, the length of the row will increase. So the error length can be A to AA.
For me it is important to analyse the error. So I would like to give a perfect row length, the value 1, and an error row length a value 2.
So, in conclusion:
If: Cell length = A1 - M1? --> Copy A1 B1 C1 (A B Cof that row) to Sheet3, and give D1 in sheet 3 the value 1
Cell length >= A1 - M1? --> Copy A1 B1 C1 (A B C of that row) to Sheet3, and give D1 in sheet 3 the value 2
I was hoping that my formula would give me the count number based on the Maximum time (latest time) and the Name field...My result is a 0 instead of 62 (the correct answer).
Look in Column E and find the MAX value. Once you find the MAX value, (let's say E27) display the date that's in C27. I bet this is really easy but I've been screwing around with it for over a half hour and can't get the correct result.
I have two columns of data. The first column is the 'type' and the second column is the 'value'. I need to find the maximum 'value' for each individual 'type'
The 'types' are not necessarily next to each other and the data cannot be sorted to do so.
Example: type value A 15 B 6 A 21 C 7 B 13
I need to be able to say the MAX for 'A' is 21, the MAX for 'B' is 13 and the MAX for 'C' is 7.
I am creating a table for a depreciation schedule. I am having the user enter the year the asset was purchased and the expected useful life (in years) into the input box and I want the output table to show only the amount of years useful years the assets has. Every year after that should not be shown on the table.
I have a worksheet in Excel 2007. I have "wrap text" turned on in column G and so long as the character count doesn't exceed 255 the text will wrap and display properly. However, when the number of characters in a cell of column G exceed 255 the display in the cell turns to all pound signs (#). The content of the cell is displayed in the URL line but it is all # in the cell. How can I fix this?
I am trying to create a string of text that grabs info from other cells, which is easy so for example
=A1&A2&A3
would grab all the info from the 3 cells and merge them to create a string, but what I want to do is create a prefixed length.
Lets say A1, A2 and A3 would equal 10 characters in total but B1, B2 and B3 would equal 7 characters, these would be different lengths. so for a visual description I would like them to appear like this
At the moment
01MAIN-HELLO 01DESSERT-HELLO WORLD
Would like
01MAINXXX-HELLO 01DESSERT-HELLO WORLD
This would make both strings the same length and would make it easier for me to export to a custom file that needs a certain length prefix.
So I have got an identifier and a corresponding column of values. I need to calculate sum of those corresponding values for each identifier, so if identifier is 1993, I need to have a sum of all the corresponding values. These identifiers repeat, and I do not want to calculate sum for all of them, just the immediate identifiers (i.e. you have 1993, after that you may have 1994 etc etc and then at one point you will have 1993 again, but that "new" 1993 will have a different sum of course). Also, identifiers do not go in sequence sometimes (however, most of the case they do), for example 1993 may jump suddenly to 2004. The trick with all of this is variable "row width" of these identifiers...
I have a STRING array which is dimensioned with 100 elements possible. What i want to know is how to determine the "length" (not sure if this is the right word) of the array. For example: Suppose i have 10 strings in the array. Is there a command to determine that there are only 10 elements in the 100 possilble array?
What formula can I use to get BOB SMITH. The length of the name will vary. The number will change, but will always be 4 digits and will have the brackets. [XXXX]
I have been using this code below to replace any blank cells with the number 0 but recently I have encountered an error if the format of the cell is a string.
What the max length of an ActivX Control name would be. I mean the name that you would use in coding something, not the actual caption name of the button. IN this case I am using a radio button.
Based on that I would like to create random IDs to assign to the button. I know how to do this in the cell formula but not in code.
Trying to sum up a column that has an unknown length. This is a canned Excel form that is saved within a software system and is used to create customized documents. Usually, I would enter the table name.field name in a cell to pull the data from various parts of the system.
In this case, that data resides in AA234 of my customized Excel sheet. When the user retrieves this doc from the system, the data could run several rows, starting from AA30 to AA255. So, in my "Total" cell, I've entered =SUM(AA1:AA255), so it will just add up whatever is in the column, regardless of how long it runs upon document generation. When I try to pull the document, I get "=SUM(#REF!)" in that field instead.
I have a row of 1's and blanks, where there might be 10 consecutive 1's, followed by four consecutive blanks. I want to calculate the average length of a string of 1's in the row -- how this might be accomplished?
i have tried to create a userform thru which data is to be entered. so that the data will be printed on a worksheet. here there are few text boxes, in which the number of digits should be equal to 14. after filling the userform when i click the print button if it is less than or more than 14 a pop up msgbox should be displayed with OK button and the cursor should go back to that particular text box. i have written the following code, but it has a problem. even if the total no of digits are 14 the msgbox is displayed.
say the text box name is Roll no
If txtRollNo.MaxLength 14 Then MsgBox "Roll No should be of 14 digits", vbInformation + vbOKOnly txtRollNo.SetFocus Exit Sub End If
