I have two sheets, the first called 'AF' and the other called 'POR'. For one of the columns in the 'AF' sheet I used the following formula - VLOOKUP(B2,POR!$A$2:$A$111254,1,FALSE) to retrieve a string consisting of 7 numerical digits (Ex. 4678451). Now if I want this result of 7 digits in the same 'AF' sheet to be converted to the word "YES" - how do I achieve this? (All the 7 digits numbers are different or unique). Is there some sort of REPLACE command I use here or some other means?
I have included a test sheet with notes to explain what I am trying to accomplish. With the current code all works fine if I enter the data manually in column G, but when I do copy and paste for the whole column G it does not work. Is there a way to have the totals in column K convert to value row by row only if I simply paste data in Column G?
I am creating a review sheet/skill matrix using excel and need to be able to convert a particular selection to a number in the adjacent cell to work out the users efficiency (long story that i wont bore you with)
So basically i have a question/statement in cell A and a drop down answer menu in cell B containing up to 5 possible answers (very poor, poor, ok, good, very good). In cell C i would like to insert a formula that allocates a number dependant on the selection in cell B. So if the user selects very poor in cell B cell C states 1, if cell B has poor selected then cell C states 2 etc etc
I am trying to create a spreadsheet to keep track of an online racing league that I'm in. I have created columns for the racers, and rows for the races. What I would like to do is type in 1st, and it represent the number 13 for when I do an autosum at the bottom of that column. It would be easier for me to type in the position place, instead of the points value. The position place will always carry the name numeric value. Here is what I have so far:
I have a file with a lot of numbers, but one column with single digits. These digits have a cooresponding model name. I want to convert them all to their model name. There are only 4 model names. I tried a nested IF statement, but felt this wasn't working properly or was not the proper formula choice.
A Find/Replace would work, but it picks up the single digits elsewhere in the sheet. I realize i could copy/paste my column to a new sheet and from there convert/paste, but I am trying to get better at Excel.
I have a spreadsheet where I want to record the VO2max value (a number indicating the maximum volume of oxygen a person can utilise) of a person.
The VO2max is just a number, normally between 30-60.
I then need excel to look up the VO2max value (i.e. “35”) and give that number a predetermined inputted value name in a different cell (i.e. “low” or “fair”, “good”, “very good” etc etc).
However, as VO2max is also affected by age and *** I also have a column for the participants age and *** and I want excel to use that to make the value name.
E.g. in a 24 year old male a VO2max of 32 is “low” But, in a 42 year old female a VO2max of 32 would need to be classed “”moderate”
Any ideas how this can be done? can it be done?
I could post the table containing the age range, values and names if I knew how to post an excel sheet on here.
When I drag my VLOOKUP formula down a column in Excel 2010, the return value copies the formula result from the original VLOOKUP formula result. For example, if the first VLOOKUP returns a value of 0.5, I expect to see 0.5 or 1 in the cell below that one. However, I get 0.5 which is not the expected result for the cell below.
When, I click the fx on the cells below, the expected return values appear in the formula result. After I click OK, the expected formula results updates and now appears in the cell.
I'm not sure what is causing this issue. My computer was updated recently from an old machine to a new one. I have never experienced this issue before.
I created a simple auto numbering function whereby Cell A7 contained =Row()-6, and Cell A8 contained =(A7+1). I then shift, and pasted the contents of cell A8 until cell A600. My aim is to simply copy the increments of 1 - 600 into another column. However when i copy and paste i'm also copying the initial underlying formula ie: =( A?+1), Is there a way to copy the results, not the formula?
I have an excel program that is supposed to count word instances in a word document. I can't seem to find the right declaration for a word document. For example to declare a workbook in excel its
Dim wb As Work Book
I've tried
Dim doc As Word.Document 'or Dim doc As Word.Application
as shown in some of the forum posts, but an error user-type not defined keeps displaying.
I have a working formula that I want to convert to vba code... I only want to store the formula result in each cell in my range, not the formula itself. It is an ARRAY formula (entered with CTRL + SHIFT + ENTER) and it would be ideal if it could follow R1C1 format to keep it relative for each cell.
Here is the formula as it's entered in my spreadsheet:
I am not super familiar with using INDEX/Match combos which is why I am struggling with this one...
The end result should be:
For each cell in myRange cell.value = 'Formula result here Next cell
-OR-
If possible to fill the range in one step:
myRange.value = 'Formula Result Here, utilizing R1C1 relative reference... (this may be a stretch given the range is 55krows x 18 columns)
The problem at hand is I need to fill a range of approx. 999,000 cells with values and the values are a VLOOKUP with 4 matching criteria. So returning the value from Col U on my LookupTbl when the criteria in Col's A, D, Q, and R are an exact match to my values in A, D, Q, and Z:AQRow1.
I have an excel work book where in one page (sheet1) contains formula types to be used based on a code. and in the other page (Sheet 2) when I enter the code I need it to get the formula from 1st page and then do the calculation in the second based on the code. Ex : Sheet 1
Code Formula 0 Cell A * CellB 1 Cell B - Cell A*5% 2 (Cell A + Cell B)*10%
I hope you got the picture. The problem is I'm not sure how to take only the formula from 1st sheet and then use it in the 2nd sheet to do the calculation. I wont mind using a form for this even.
I know this is an easy one but I'm unsure why I can't easily modify a formula. The key event in this formula is "+190". Every time I change the value the formula no longer works. I've copied to another cell and the formula no longer works. My question is when you have a formula like this one if you have to modify how do you do it and keep the formula working.
I have a column with names, where i need the 1st letters of the Names to be displayed in CAPS. If its a Single letter also, the same should be displayed in CAPS
I have whole bunch of formula's added to cells by code. So far so good. How could I then set in macro code that the result is stored in cell as value rather than formula? Now when I go to cell you can see =PIBVSearch(.... yadda yadda yadda in the field above.
I would prefer to change it so that content of cell changes to it's value so there's no formula visible(and above all not stored to harddisk(LOTS of disk space lost. Whole project started to trim down 269Mb excel file into smaller piece! I don't want it to go back there too easily...) or in danger of getting updated which might take a very long time easily).
Code works so that as long as there's rows which haven't got update yet but do have product run code(or something like that. Not quite sure what that number is but I figure it's product run) after code reached that far(which it copied from another file). Then for each row it calls functions for each cell which are responsible for filling invidual cells.
So either way to replace content of whole row with values or invidual cells, I can insert both into loop easily though whole row after cells are calculated would be a) faster to add(just one place to add, not 30...) and likely faster to run(project is about getting the file to run faster afterall...).
I am having trouble converting the following Countifs formula developed in 2007 to work in 2003 Excel.
Basically, I want to count the frequency of "N" in 'Data_Q2 2008'!$S$2:$S$9806, where 'Data_Q2 2008'!$B$2:$B$9806 = 'Metric Upload'!C2.
'Data_Q2 2008'!$B$2:$B$9806 is a list of physicians. 'Metric Upload'!C2 is a pick list for selecting the physican that is in the larger data set using Data Validation.
I'm having a slight problem as I cannot find a way to translate the following code from my worksheet formula to VBA: =IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))=1,A1,MID(A1,1,FIND("-",A1,FIND("-",A1)+1)-1)). The code takes a value from one cell and takes the part that I need. There are two main types of inputs, they are in the form:
aaa-aaaaaaa-aaaaaa aaaa-aaaaaa
The lengths are variable, but for the sake of showing you an example I have simplified it to the previous. The formula takes the part of the string that is before and after the first hyphen and puts it in the cell next to it.
Looking for a formula to convert time (00:00:00 AM) to a general 4 digit number (0000). Example 08:30:00 AM ---> 0830. Tried using format cells but no luck.
I was able to convert a standard number to time using the following formula: =TIME(INT(H2/100),MOD(H2,100),0)
I have a paper-based scoring sheet that we use to determine a rating category. I cannot figure out how to convert this scoring sheet into a lookup region and use some type of LOOKUP formula to determine the rating. I have looked at the various Grading examples on the Internet that show you how to look up a grade, based on a score, but they only look up a grade, based on a single input (the score).
Here I have two values going in--the person's age and their score, and I need to look up the rating, which is the column heading.
Can anyone point me in the right direction?
I've attached a sample spreadsheet which shows the original scoring sheet.
I have attendance tables that has column Y showing the shift to be worked and column Z showing any absentees. I used the following formula to count the number of people off per period.
A coworker is setting up an MS Project...project, and adding a custom field that would convert the Start date field into our Fiscal Quarter Fiscal Week schema, to display as FQFW, e.g. Q1W1. I've written the formula in excel, but I've never even touched Project until this week. Apparently it doesn't take formulas quite the same way as Excel does. I tried using the ''Switch" function in Project, and it worked, but it only accepts 14 arguments, and there are obviously 52 weeks we're dealing with. Would there be a way to do this using VBA? (I know next to nothing here as well.)
I have a formula in a cell which is refered to other cell within the same sheet and also to other sheet from other file. say
C1 = A1+B1+data file c1 Data file is another file in other location.
I was trying to break a external reference link (from Data File) from Data _ Edit Link, but it is converting all the formula to value. How is it possible to break only the external link without damaging the formula within the sheet say c1 = A1+B1+26 (value from data file c1)
