"istext" Behaviour/recognition
Apr 4, 2009was workin' on a text recognition question with someone here:
http://www.excelforum.com/excel-new-...ml#post2071064
and another user posted this:
ADVERTISEMENT
Print Behaviour Changes
Aug 14, 2008I have a workbook which prints the sheets I desire using my default printer when I click the Print button in the Standard Toolbar. However, my macros must not be written entirely correctly, for whenever I want to print using a different printer by clicking File/Print... my sheets immediately print without letting me choose any options - so with other words, using File/Print does exactly the same thing as hitting the Print button.
Manage Addin Behaviour Via Registry
Mar 14, 2007Is it possible to make a setting in the registry, so next time excel opens
the addin is not "marked" in the addins-list. I can't do it manually because, because the close_event in the addin will be executed and that will screw things up.
Currently I'm looking at the regkeys: addin-manager and options, I see a change in the registry if I close XL when I unchecked an addin from the addin-list. It looks promising, but Iám not familiar with it
Userform Controls Behaviour When Moving From One Control To Another
Feb 27, 2013I have built a quoting tool and Userforms to act as a user interface to this tool. The userforms basically just feed user input back to relevant sheets. I have a number of textboxes, listboxes and radio buttons on multipages. Now the issue is that each control has an Afterupdate or Change event associated with it. Consider this scenario:
I have 4 product categories (represented by radio buttons) : Cat A, Cat B, Cat C, Cat D
Each category can have up to 10 line items which are represented by combo boxes.
Now lets say the user has selected Cat A radio button, he selects a product from the combo box (the cursor or focus is still on the combo box) and clicks on Cat B radio button. Here is the issue: on the first click, Afterupdate or Change event of Combo box fires but radio button is still unselected. The user has to click Cat B radio button AGAIN to select Cat B radio button. So essentially it takes two clicks with a slight pause between them to change categories.
One way to avoid this is to press Enter or 'tab through' each field. But this is not very intuitive. I have tried using Exit, Afterupdate, Exit, Change events without success. I can't use ControlSource option because the cell to input info to is dynamically selected. I have used xlCalculationManual for some text boxes but can't use this since some inputs on the userforms depend on the right value being calculated on the sheet.
Replicate Default Behaviour When Passing Ranges To Functions
Oct 13, 2013I've got this relatively simple file I use to calculate some values for a game mod. Its currently using ad-hoc excel functions, but its maths related to aerodynamics and they get unreadable quickly. I'd like to move to VBA functions for ease of maintenance, and programming the maths part of the code has been no issue so far.
However, I don't understand how to replicate the functionality of the default maths functions regarding range inputs.
Simple example, one of the intermediate values is the Aspect Ratio of the wing:
Code:
=ROUND(2 * Semi_Span / Mean_Aerodynamic_Chord, 3)
This works fine with both numeric inputs, cell references, or, as shown here, defined range names.
However, my VBA version:
Code:
Function AspectRatio(b_2 As Variant, mac As Variant, Optional round As Integer = 3) As Variant
AspectRatio = Application.round(2 * b_2 / mac, round)
End Function
Only works with single-cell references or numeric inputs.
When called as:
Code:
=AspectRatio(Semi_Span, Mean_Aerodynamic_Chord)
It fails.
From my limited understanding - I've only started looking into VBA about 2 hours ago - I need to do a IsNumeric test on the inputs in question, and then use the row from Application.Caller.Address to select the right cell if the input is a range? Am I on the right track?
How to craft a Sub I can call in the 15+ math functions I made.
ISTEXT In Array SUM
Jul 31, 2009I have a workbook with worksheets named by peoples names. There are cells in my summary sheet that contain all the names of the worksheets (or peoples names).
Each worksheet has a table in it that I am trying to search one column to find a specific name and then report if there is text in another column in the same row. If I use the following formula:
=IF(ISTEXT(INDEX(INDIRECT("'" & E$4 & "'!f10"),MATCH(INDIRECT("A11"),INDIRECT("'" & E$4 & "'!h10"))))=TRUE,1,0)
It works and I'll return a "1" for cells in column F that have text and a "0" for columns that don't.
However, I am trying to sum up the cells in F that have text and when I enter:
{=SUM(IF(ISTEXT(INDEX(INDIRECT("'" & E$4 & "'!F10:F200"),MATCH(INDIRECT("A11"),INDIRECT("'" & E$4 & "'!H10:H200")))),1,0))}
it returns "1" when that should not be the answer. Am I doing something with the array in my INDIRECT formula? I used this similar application for another purpose in this workbook and refereced the array through INDIRECT the same way (but didn't use MATCH or INDEX) and it worked fine. This was the formula:
{=IF(SUM(IF(INDIRECT("'" & C$4 & "'!H1:H200")=INDIRECT("A5"),1,0))>0,SUM(IF(INDIRECT("'" & C$4 & "'!H1:H200")=INDIRECT("A5"),1,0)),"-")}
IF (ISTEXT Formula)
Jan 28, 2010I am summing 3 cells, A3, A4 & A5. Cells A4 and A5 may contain text. I want to write a formula using the IF(ISTEXT funciton which says something like this:
Sum(A3:A5), if cell A4 and A5 contain text then sum just A3. If only cell A5 contains text then sum just A3:A4.
Im not sure exaclty how to construct this.
Date Recognition
Nov 30, 2009I have a spreadsheet that uses data which is linked to dates on which events are being held.
So, event 1 happens on 5/12/09, event 2 on 6/12/09. What I would like is the spreadsheet to identify the next event occurring and copy the information into a different cell on the spreadsheet.
Number Recognition
Apr 25, 2008I have a column of data (numbers) that are expressed incorrectly i.e 1,000 actually means 1, 0,3 actually means 0.3 and 15,500 actually means 15.5. I can make the first and second examples OK by using the replace command, but how can I check the number format of the cell and then make it change to what I want. All these numbers are in random order and sequence. I'm using the replace command in a macro, is there something I could put in the macro to solve my problem?
View 9 Replies View Related=IF(ISTEXT On Blank With Formula?
Mar 15, 2009I have this formula in a cell: =IF(ISTEXT(F5),F4,""). The problem is F5 is visually blank and has this formula =B3. so it returns F4. Is there a way to rewrite the formula or to use another formula, so it will return blank?
View 2 Replies View RelatedSearching A Cell With IF And ISTEXT
Mar 20, 2014I'd like to search a cell A1 containing for example 'hello_L765'. If the 7th character is L and the 8th character is text(ie not a number) than display yes, otherwise display the 7th character.
View 3 Replies View RelatedCan ISTEXT Search A Range
Jul 5, 2007I'm currently designing a new spreadsheet and I can't change the formula I'm using below to search more than one cell or a range. Is either choices a option?
I'm using =IF(ISTEXT(E135),G135) Can I add cell E139 (and how many additional cells could be added) or can I set it up to search E135:E144.
Multiple IF And ISTEXT Within Same Formula
Jun 23, 2014I have a rota sheet whereby shifts are written in as start time, finish time, and then a calculation for the hours.
At the bottom I have another SUM formula to calculate total daily hours.
However, within the total personal hours I have a VLOOKUP formula to check if the start time cell has a holiday text reference.
Please see below:
Take the "Sergei" example: he starts at 9am, finishes at 8pm, and the formula automatically calculates an 11 hour working day.
However, in the "Tom" example, the "TH" refers to another sheet which houses a reference of the amount of hours to place in the total hours box if those letters appear, thus imputing holiday hours at the same time.
"=IF(ISTEXT(F24),VLOOKUP(F24,'Hol-Meet Hours'!$F$7:$G$27,2,0),(G24-F24)*24-IF((G24-F24)*24>6,0,0))"
What I am struggling with is to use an IF and ISTEXT formula in the "Daily Hours" formula to ignore any holiday inputs, thus only giving me trading hours.
The only way I could think of doing it was something along these lines:
"IF(ISTEXT(F16),SUM(F17:F31),SUM(F16:F31)...et al"
The issue here is that I need to repeat the formula for every staff member within the same cell's formula, which I don't know how to do (multiple functions within a formula repeated 20 times), and is a right drag.
ISTEXT Returning False
Nov 12, 2009I'm trying to apply the istext to a cell that has multiple values eg: [car, boat, truck] so I have setup the cell with the formula
=istext(c147 = "car",1,0)
it returns false constantly. Is that becuase of the other values separated by comma's in that same cell?
Is there a way to do this so that I don't have to go through 300+ cells that have multiple values to separate them?
Isnumber Or Istext Formula
Feb 21, 2010I've been trying to use excel's Isnumber or Istext function to evaluate a range of cells, but have been unsuccesful so far. Is it even possible? or is the formula just restricted to evaluating only single cells?
For example, if I do, Isnumber(A1:A10), is there any way to make that work?
VBA ISNUMBER, ISTEXT Functions
Jan 13, 2005I am trying to create code that deletes lines in a spreadsheet when the first column is either blank or has text in it. However, when writing the VBA code, ISNUMBER and ISTEXT do not work. Amazingly, ISBLANK does work.
View 4 Replies View RelatedNumber Recognition Formula
May 25, 2009I play Keno a lot and have what is probably a very simple formula request. If I’ve got for example this result pasted into Excel: 06 12 15 21 23 27 28 31 32 34 48 49 54 56 60 67 68 74 76 77. How would I go about determining if a pair or two numbers had come in?
Say I was looking for 31 and 76, is there some way I can get Excel to recognize the two spot hit then post in response either 1 (in bold) if yes or 0 (not bold) if no the two did not come in together? Note this would not be only one or the other, they both would have had to hit at the same time.
Automatic Cell Recognition
Sep 4, 2009In the Yellow Cells, I am looking for a formula or Macro that will automatically recognise and give the correct Premises number. As an example, in the first block of Yellow cells, it should be MOR001&2; The second block should be MOR003 and so on...
One could copy and paste the Premises number for each premises, but with 100 entries or more, it becomes a tedious and time consuming task. An autonomous function to recognise the correct premises number would be much easier
In the end, this is to be used when drawing up a PIVOT so that one may easily pick up a premises number or numbers and their related charges
File's Name And Recognition By The Program?
Jul 22, 2014if it is possible for vba to recognize the name of the file is into.
example:
You file's name is : Track
you change for : Music
Your code automatically recognizes that and change the code.
Forcing Date Recognition
Aug 1, 2006I do not write code in it, just use it for school and work on the most basic levels. I am trying to suck an Excel document into SPSS ( Statistical Package for Social Science) for my Engineering internship. I am logging temperature using Omega Engineering, Inc temperature probes. When converting the data recorded on the probe to excel, it does something funny. I will try my best to explain what it is doing. If you format the cell to general, it gives you this (38917). Then simply changing the format to date I get this (7/19). How do I change this to make it so the date is the only memory in the cell. I don't need the other number in there because when I suck it into SPSS it only sees the 38917 number and not the date. I need the date to do my statistical analysis on the data.
View 4 Replies View RelatedWorksheet Referencing And Position Recognition
Jun 4, 2009I have a bit of a complicated one here so I have attached my book, its probably easier to undertstand the query by looking at the book. Hopefully somebody can help me out here.
I have 2 worksheets:
A results worksheet - this contains all category of results, each category has been given a specific sort id.
A foreign worksheet - this contains all categories found on the results worksheet with sort id = 7.
Now the complicated bit. The whole exercise is too pick up the correct exchange rate for the foreign category (sort id 7). each foreign type has a specific number at the end of its description in brackets e.g (3).
At the bottom of the "results worksheet" is a key which says what type of exchange rate to use for this type e.g. (3) Price of CAD 22.9 converted using exchange rate of CAD 1.9645 = £1.
What I need to do is on the foreign worksheet, it to recognise the number in the item description, keep it in mind then switch over to the "results worksheet" find the keys at the bottom of the page (the key is not necessarily in the same place all the time!!), match the number in the items description with the corresponding key and then find the exchange rate.
As i said very difficult to explain, since I reletively zero experience with this kind of work in excel. Luckily I been muddling my way through a work project gradually thanks to a forum member here. so i hope we can keep this up!
edit:
two restrictions in the way this solution can be done:
1- Results worksheet can not be modified, but it can be referenced to using INDIRECT, and you can make any number of modifications outside of the results worksheet.
2. Any solution must be automatic.
/edit
I have attached my workbook and highlighted the bits I need to fill and the picks I need to pick up.
Would be ever so grateful if some1 could show me the light here.
Name Recognition To Match Data Across Sheets
Jul 29, 2013I've created a spread sheet of hockey stats to prepare for my fantasy league. I've created a formula weighing different stats differently to create one "Fantasy Value" score. I've done it over 3 years of data and want to find the average 3 year score for each player on the front page of the workbook. There are 300+ names I'm tracking so I'm wondering if there is a way that Excel can recognize a player's name and pull the data from one specific column in 3 different sheets.
I've attached the excel file for your reference : NHL Stats.xlsx‎
Creating Revenue Recognition Waterfall
Oct 3, 2008So here's the setup: A customer purchases a service which lasts 6-months. As such, revenue recognition for the company can only occur as the service is provided. So if a person orders a $600 service at the beginning of a month, revenue will be recognized at $100 for the next 6 months. I'm trying to create an Excel Waterfall chart which will show the monthly revenue recognition amounts for all orders depending on the date which they were ordered.
I've attached a sample of what I'm looking to do. It's become tricky for me because Revenue Recognition is pro-rated based on the date ordered (i.e. order on the 20th of a month so at the end of the month 1/3 of the month is recognized as revenue). I'm looking for something that will populate the percentage of the order amount, each month, that will be recognized. I've populated what row 3 should be, but there doesn't seem to be an easy way to apply a formula or something across all cells.
Random Cell Recognition For Formula?
Nov 7, 2012I have a spreadsheet where I need the end result to be auto filled after recognition of an adhoc "top up" amount. How do I get E7 (3170) to automatically recognise the insertion of C7 (3000) to give this result without having to manually update the formula in E7?
DATE
PURCHASE
ORDER
PRODUCTIVE
[Code].....
Date Recognition In Referenced Cell
Nov 11, 2009I have the following two formulas in a spreadsheet, which sum and count, respectively loan information within a certain date range. Both of these are working correctly.
My problem is in substituting the dates with cell references for dates located in a worksheet with a table of dates. The dates from this table work with simpler formulas, but I cannot get them to work here, regardless of how many () "", etc that I try....
Check Cell For Data. Tried ISREF, ISBLANK, ISNUMBER, ISTEXT.
Mar 24, 2009I have 4 cols, x rows. I need Column C to check Column B for a numerical value, and if true, return the value in B, and if not true, then return value from A. Column B are functions formatted as general. The reference made by Sheet2!B is numbers formatted as text as to retain leading zeros. Here is what I have so far
=IF(ISBLANK($B10),$A10,$B10)
Excel 2007 :: Validation With ISTEXT Not Working When Number In Cell?
Dec 3, 2012I'm trying to validate a cell in Excel 2007 that should contain only two letters.
Formula:
Code:
=IF(AND(LEN(A1)=2,ISTEXT(A1)),TRUE,FALSE)
When the value of A1 is JK, the result is TRUE
When the value of A1 is 12, the result is FALSE
When the value of A1 is 3K, the result is TRUE
When the value of A1 is K3, the result is TRUE
When the value of A1 is 123, the result is FALSE
When the value of A1 is JKL, the result is FALSE
When the value of A1 is 3, the result is FALSE
When the value of A1 is K, the result is FALSE
The LEN function works as expected, but the ISTEXT function does not, whenever the cell contains a number and a letter. I've tested the LEN and ISTEXT functions separately, and get the same results.
Is it me, or is it Excel?
Formula Showing Revenue Recognition Based On Days And Months
Mar 15, 2014I know I will earn $5,000 of monthly revenue from a client. $5,000 is represented in a monthly revenue cell. I have 12 columns showing the 12 months of the year. There is an additional cell showing the customers implementation date.
If a company's implementation date is on or after the 15th of the month (example: 3/28/2014) then the next month (April 2014) is skipped and the $5,000 is returned to columns May through December. All months prior to May return $0.00. If the implementation date is before the 15th day of the month (example: 3/13/2014) then the next month (April 2014) and all months after will return $5,000. All months including March and prior must return $0.00. If the implementation date is unknown then 12/31/2099 would be in the implementation date cell and $0.00 is returned for all 12 months.
Essentially, if the implementation date is prior to the 15th of the month the revenue will show as of the following month. If the implementation date is on or after the 15th of the month the revenue skips the following month and will show the month after.