I am trying to use the validation feature to calculate a check digit and the formula is too long to put in validation. I rewrote the formula to be shorter using math features instead of text, but apparently you can't use arrays in validation. Because of certain distribution constraints, using VBA is not possible so a UDF is out. Can either of these be shortened in anyway? I am not sure if it is even technically possible to solve this problem with these constraints.
Here is the "human readable" explanation of formula:
(Accept only 10 digit numeric value.)
Example Number: 123456789-3
Digits 1,3,5,7,9 are multiplied by 2 (weighted 5)
So 1,2,3,4,5,6,7,8,9 becomes 2,2,6,4,10,6,14,8,18Weighted 5's multiplied values then have their individual digits added (ex:12=1+2=3)
So 2,2,6,4,10,6,14,8,18 becomes 2,2,6,4,1,6,5,8,9
Weighted 5's sum is added to the sum of the Nonweighted 4 digits.
So 2,2,6,4,1,6,5,8,9,3 becomes 43
Add 24 to sum.
43+24 = 67
Checksum = (10-(67 mod 10)) mod 10
(Sum round up to 10 minus sum)
I'm trying to do a nested formula to automatically assign a region number based on what the County is. We have 88 counties we deal with, split into 7 regions. What is the best way to go about setting up a formula to do this, so I don't have to do it manually? I believe the last time I tried doing something like this, I received an error saying that Excel didn't support a formula as big as what I was writing.
Excel 2003. When the Vlookup List becomes impractably long to select data from the cell dropdown. Is there an alternative prefered method to entering data from the long list?
Essentially, I would like a user to be able to select a PUB from the Data Validation drop down in row 2 of the PUB RATES sheet so that the corresponding information in the DATA sheet autofills.
Currently, this works only for the first column under each PUB when selected and this fills across all 4 columns (rather than the respective information for each column filling).
Also, the Data Validation dropdown includes blanks which I would like to exclude.
I have used a Range Name for the Data Validation of each PUB so that these can be drawn from a separate sheet as I don't want all the DATA content on the same sheet as the PUB RATES content.
I have a workbook with two tabs, the first tab "Query" is an Access query that pulls in call data from our phone systems. The second tab "Report" is a userform/report. This will be used by numerous people and has to be as userfriendly as possibly. Because our clients want the data sent this way I don't have a lot of room in changing the report itself.
Let me paint the picture, I have it so the user is able to select the month from a validation list I created in B1, when the month is selected is autopopulates each work day of that month in cells A13:A35. There are several (28 total) reports from different phone lines we have. So I have another validation list in cell B3 that has the name of each report we have. So in theory the user should select the month and report and the information will fill in and they can print that specific report off. The issue is this. I had used the formula =SUMPRODUCT(--(Query!A5:A25004=Report!A13),--(Query!B5:B25004=Aban_08),Query!E5:E25004). I'm trying to find total calls for specific lines for specific dates. The "Report" tab has information from January to current with all 80 different call lines (all named differently such as CDN_5700, CDN_5701). One report such as "Aban_08" actually pulls information from several call lines which is bringing me to my issue, I need one tab for the report, and be able to have the user select the different report, but each report pulls from multiple call line names. Again, I need (for instance) B13 (cell A13 is the date June 2) to pull all calls from the report tab for the date of June 2 with (for example) the names "CDN_5700","CDN_5701","CDN_5702".
iam looking for formula to display the condtion result .
i have spec in col B and tolerance in col C and col D ( +ve & -ve tolerance respectively) from col E to I ,observed reading data & col j for results.
i want to check the value of col E to I for B C D value n if entire 5 values are within spec then display "0" in subsequesnt col J else display "1" OBSERVEDRESULTS.L.NOSPEC`+TOLERANCE`- TOLERANCE123451250.20.2262525.225.425.22100.20.2350.20.2450.20.25510.20.2
2. sheet contains the availability of teachers. so all columns start with a date and the rows contain the name.
A row therefore looks like this.
7/1 Harley
Lidia Livia Thais Salete Carla
My issue: The 1.sheet has a column Teacher, now in this column I want to have a drop down list. (E2, E3, E4, E5 ... etc..) This list is depended on the date which is in column B.
Essentially I want the list to look up date in its row go to the second sheet and give me all the possible names in it. That is all.
I would like the individual cells of C1 and D1 to allow me to input values up to 100. The tricky part is that if D1 equals 100 I would like C1 to be less than 100 and vice versa, if C1 equals 100 then D1 should equal less than 100.
Ex. C1 = 100 & D1 = 10
This should never happen: C1 = 100 & D1 = 100. I would also like an error box to show when either of the cell values exceed 100.
want to get a formula where I can get the details of City if searched Brand wise and details of brand city wise. I also want option to search say five city names at once and get the brands available in all those cities. I have updated a excel with some dummy data.
I have a cell that uses list type data validation. I need to be able to update the list of allowable values via a macro and would greatly prefer not using a range in excel.
I've been able to create the string that I want as the formula but cannot change the formula. I'm not very familiar with this aspect of excel macros (data validation)
How do I hardcode the Offset function? Right now I am placing the offset formula in Validation from the Data menu toolbar.
Also, I have two columns "Expense" and "Income" used for offset. Expense has 11 selections for the listbox, and Income has only 3. When I choose income from the dropdown in column B, I get way too many blank selections in the dropdown just because It doesnt contain as many items as Expense. How can I re-code adn hardcode the formula so I have only few blanks for my selection.
I have attached a copy "data validation test" to help make things more clear.
I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
if it is possible to have data validations on a cell within Excel but allow a user to input a formula? The data validation is a decimal between 0 and 1 and the formula would be a vlookup.
I am trying to get my array formula to recognize the text selected in my data validation cell. The formula worked perfectly until I decided I wanted to try and use a data validation list to eliminate spelling errors. Even though the text I am trying to recognize in the cell is exactly the same, because it is in a data validation format it no longer works. Is there a way I can modify my formula so it works? Example below...
Can I limit the text length in a cell that contains a formula? You may say "Limit the text length in the input cell". That can't be done because the formula in the resultant cell takes text from two other cells in addition to the input cell. I need to limit the overall text length to, let say, 50 characters.
I am quite inexperienced with Excel and I am struggling to understand how should I set up a formula, within the data validation function to achieve the following:
I need to restrict a certain set of cells, for people to be able to input only the following values: "1", "0.5", "A", "H", "0.5A" (or 0.5a), "0.5H" (or 0.5h)
And I would need the data validation tool not to include blank cells.
I have 2 columns First Name & Surname. What I want to do is create a data validation list on the surname which results in the 2nd data validation list only showing the first names which link to one of the surnames.
i.e. If I selected Smith in the 1st validation list then I would only like to see 'Paul' as an option in the 2nd list
We have an Excel file that is located on the network and is accessed by numerous individuals. Should someone need access when another user has the file open, we get a message that the file is currently in use and it also lists the user id of that person. The issue is that a particular user will open the file and keep it on their desktop for hours at a time. Is there a way to limit the amount of time that user can have the file open? Is there something within Excel that I can turn on that should the file not be modified for a particular amount of time that Excel saves the file and closes it down?
is there a combo boxes limitation in a certain worksheets or workbook.? if there is then how many? coz i got a problem , i have to make around 2000 combo boxes, if i save it , i got no problem , but when i closed it and then reopen , the problem occurs..
I recently bought a new Dell XPS 10 tablet with a keyboard. When I copied my spreadsheets from my old notebook, none of the countifs or countif functions are working, all popping out a zero. It work completely fine on my old notebook on which I have Excel 2010 installed. On the tablet I have the pre-installed Home & Student 2013 RT. Could this version have limitations to my functions? What can I do to get my functions working again?
I got this wonderful, beautiful spreadsheet/template i'm using now for my small business, and well, i've got it all tweaked and tuned so it looks beautiful on my screen (100% view in excel). I can look at it, and they way it looks, is the way it prints whether i opt for a PDF or (EEEEEK!) paper. I've sent this report and template i've put together with Mrexcel's support, and it's all primp and pretty and the person i send the spreadsheet to for review? he's looking at it at the same 100%, and lines are cut off when multiple rows are merged that have multiple sentences typed (technically reference to) in. he's tried to print both pdf and paper from his computer and had no issue. it comes out perfect. It only looks visually cut off. we're trying to avoid a second transmission back to me to reformat say something needs to be omitted. QA if you will.
my question: Is this some sort of issue relative to our screen resolution? is there a way i can optimally get what he is seeing when he views it electronically to be what i'm seeing electronically?
I have a macro that copys a worksheet a user-inputted number of times but throws an error after adding Sheet #62. The line it hangs on is:
Sheets(myTemplate).Copy After:=Sheets(i - 1)
Does anyone know if this is a problem with my code, a memory issue, or the workbook I'm using to copy? In the past when I've copied a lot of sheets in a workbook (to the same workbook), Excel sometimes won't let me add anymore but if I copy all the sheets to a new workbook it will sometimes let me add more sheets. I figured it was a problem with how Excel counts sheets.
Sub CopySheets() myTemplate = ActiveSheet.Name
'First, jump through the validation hoops Dim AddSheetQuestion As Variant
'Define the application input box question showAddSheetQuestion: AddSheetQuestion = Application.InputBox("Please enter the number of sheets you want to add," & vbCrLf & _ "or click the Cancel button to cancel the addition:", _ "How many sheets do you want to add?")
'Cancel or the X was clicked If AddSheetQuestion = False Then..................................
I am writing a macro that scrapes information from other workbooks. I wanted to add a hyperlink on each line to the workbook that row's information came from. I tried both the VBA method of adding a hyperlink as well as the formula method. When I click on the link that was created, windows explorer opens up to a folder rather than opening the workbook. I have a feeling I am hitting some folder level limitation. The link is to a network share and here is an example of what it looks like:
\MYCOMPANY.CORPFILESCBOSEALDRIVEDeptROPSPT Billing2007 AdjustmentsMCBDApproved#01305.20075.25.07 - C - NAME.xls
I am using excel 2002 and I have found that when the workbook is shared and getting 2 or more users to open the workbook around the same time brings up a read only type prompt box for the 2nd ( and 3rd) user when the book hasn't fully opened for the 1st user/is still fully opening. Is this a limitation within excel or is there a way around this for example via vb code.
I know the more data a workbook has can cause it open a lot slower but is this the same for shared mode.
