I have a problem where I am linking data from two worksheets say: worksheet 1: A1 = 10, A2 = 20 worksheet 2: A1 = =Sheet1!A1, A2 = =Sheet1!A2
I have filled this formula through a certain number of cells on sheet 2 so that if i add more data to sheet1 it will appear in the appropriate place on sheet2. Is it possible to show the formula results in the formula bar, i.e. sheet2: A1=10, A2=20, such that i can used 'Find' or 'Autofilter' on the cell values rather than the formula?
In range B4:M4 some cells contain values and some are blank. I am trying to write a formula that will omit the blank values and just give me the nonblank values. The code below returns the FIRST nonblank value, but I can't seem to figure out how to drag it across a row to get the others. I want to ensure that the numbers stay in the same order (so no largest/smallest formulas), but the blank cell values need to be removed.
This would then need to be repeated for (B5:M5, B6:M6, ETC).
I have a column that contains Gender values ( M or F). How do I create a formula that first counts the number of M or F then tells me the ratio of M to F?
I have two columns containing numbers and have done a vlookup to see if the values exist in each column. Now I need a formula to return the numbers that were NOT found using the vlookup function.
I need to display a set of cells based on the value of two drop down cells i have. As I am not very good at english and worse at explinations, I'll try via screen shots...
I have two dropdowns (C4 and C6) that will indicate what table to use (Second sheet / screenshot). I want that "table" to display in the yellow box on the first page. To complicate matters, some options do not have a CLA option - those starting with X. As there are 24 different outcomes and each is 3x9 if/then statements just dont seem to cut it.
I'd like a formula to be inserted in cell A4 that returns all products listed in row 1 that have FALSE in row 2. Is this possible? I tried a HLOOKUP and a MATCH yet it wasn't returning all occurances
Am using Excell for some times and understood the basic operations like VLOOKUP and other formulas, and it was really usefull. Now i have another requirement where i need to get all the values matching from another tab.
Below is the example: I will be entering all the values in Tab 2 manually. As well as i will be entering the Categories (Column A) in Tab1. I want the Values (Column B) in Tab1 to be populated.
TAB 1CATEGORY VALUES
AA1 1 2
AA2 5
BB1 3
BB2 4 6
TAB 2 CATEGORY VALUES
AA1 1
AA1 2
BB1 3
BB2 4
AA2 5
BB2 6
I have 2 problems in that
1) It is not dynamic, i have to change Tab 2 frequently. 2) values are listed below the categories
I would like to have a dialog box "popup" if the value of two cells do NOT equal.
cell C11 has net sales values for the day, including cash, checks, and credit cards. After sub total of these values, I subtract any charges(voids) and get a total(c11). I enter a sales(c12), tax(c13), no tax(C14), misc. amounts(C15 & C16) ,reductions(c21) and put the total in Cell C22 with the formula =Sum(c12:c16)-c21). Because I do not want the formula overwritten, I protect the sheet including the amounts in cell c11 and c22. If they do "Not" match I would like a dialog box to appear so the error can be corrected.
Triggering a message box. one of the worksheets in my workbook is called Update Comments - this is a sheet that contains data in the following format (headers)
B7 = Week Number C7 = W/C D7 = Update Due E7 = Updated By G7 = Update Comments
I have a formula in column D (beginning D8 and copied down for the year) as follows:
is there a way that i can insert cell values in a userform i have data which needs to be displayed on 33 lines line 1 cell value from d10 then space cell value from d185 then space cell value from d186 this needs to be repeated line 2 cell value from e10 then space cell value from e185 then space cell value from e186 line 3 cell value from f10 then space cell value from f185 then space cell value from f186 line 4 cell value from g10 then space cell value from g185 then space cell value from g186 this continues trough to line 33
I am trying to create a formula to display a string of text that refers to multiple cells. for example, =If(A1=0,"insert text here" &B1 "insert more text here" &B2 "insert even more text here" &B3, "insert text here" &C1 "Insert text here"). It works fine for one cell value, like ="Total: " &A1
When I type a formula into a cell, it disapates into the answer. Eg: I type =100/2 into the cell. When I press enter and go back to the cell, the answer is displayed in the cell and in the formula bar. That is, it looks as if I typed in (hard coded) the cell as 50.
I built a formula but I need it to display the cell reference (column letter & row number), not TRUE or FALSE, of the cell(s) that matches the criterias.
I have provided the formula to demonstrate what I'm trying to do.
I have a quick question regarding this simple code thats been drviing me nuts. There x distinct pieces of Data in the range speciifed, but when i run this code, the reply posted in cell H1 is 1, instead of x.
Eventually the code will be built to check if the result is odd or even and then add an entry into the first blank cell if the result is odd.
I am trying to find a way to display text that is the product of a concatenate function in a defined cell, but not to have the function itself in that cell. Basically, I want to have the below function in cell A1. I want to add a command to it to take the result and display it in cell A2.
The reason for this is that I need the cell to be selectable (it is generating a file name that needs to be selected and copied), but I don't want the code behind it to be seen. I can't find a way to make the cell selectable AND hide the formula from being seen when the cell is selected. This is because our people keep copying the formula rather than the resulting text.
I was hoping for something like a DISPLAYIN(target) function, but it doesn't seem to exist.
I need to build a formula to have one cell display multiple options depending on a value in another box.
If Value is >x and <x display Y
Details:
Cell D5 Holds a dollar amount. E5 is where the calculation will happen. Logically i need it to do the following exactly:
If D5 is between 5000 and 9999, display 75. If D5 is between 10000 and 14999 display 150. If D5 is between 15000 and 19999 display 200. If D5 is >20000 display 250.
The result is a variable/number (235), "d" or a blank cell ("").
This formula is in a column and works fine where there are numbers in corresponding cells. The problem arises with corresponding cells which appear blank (show no values) but contain references to other cells: they result in a "d" when nothing should be displayed.
So while a corresponding blank cell is correct if it shows no values, it gives me this problem - I don't want "d" or anything.
I'd like to dislpay the workings of a formula (that refences other cells) in a seperate cell. e.g. Formula =A1-1000/B1. would show as =2000-1000/20 (If Cell A1 = 2000 & B1 =20)
