returns

I have some data in A1 that looks like the following: PA1*COL 1*50*Snack 1 PA2*3*100*1*100 PA1*COL 2*100*Snack 2 PA2*52*100*0*0 PA1*COL 3*100*Snack 3 PA2*2*200*1*100 PA1*COL 4*50*Snack 4 PA2*7*50*0*0 PA1*COL 5*200*Snack 5 PA2*100*0*0*0 In A2 i need to be able to type a value, in this example i...

Hi all, I'm looking for some help to randomize a VLOOKUP. I have four values on column A, all Zero. Column B has values A,B,C,D The VLOOKUP returns A as this is the first matched value. The VLOOKUP correctly returns the result of the minimum value in column A: =VLOOKUP(MIN(A1:A4),A1:B4,2,0)...

As the title state, I need to figure out how to obtain each value in the array as I copy the formula down the column, or figure out how to make part of a formula not be affected by CSE. Example: <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> O O O S...

Here is the formula I used which contains combination of IF and INDEX =IF(STREAM!E5:E54>0,INDEX(STREAM!F5:F54,P1,1),INDEX(STREAM!E5:E54,P1,1)) In this, I have faced the problem that only one condition returns output, other returns 0. If STREAM!E5:E54 is true then it returns the value of...

Hello, I am pulling 2 different dates via VLOOKUP into cells in a spread sheet and then wanting the difference by day returned. The problem in the dates being pulled have time in the also so when I want the different to be zero it returns .29 or something like that...

I am trying to do an INDEX and MATCH where the INDEX column contains blanks. This formula works well until the column has blanks. Then I get #N/A {=INDEX(O6:O130,MATCH(TRUE,O6:O130<>0,0))} -- Column O returns #N/A =IFERROR(LARGE(P6:P1300,1),"") -- Column P also has blanks, returns #N/A...

Hi, if you lock a cell which has a formula in it of which then returns a value in the same cell does the formula get deleted when you delete the cells value.

Hello if I can find the ROWS and COLUMNS using MATCH how do I find out what is in the range <code>=MATCH($O$38,Holidays!$A$4:$A$92,0)</code> returns the row number. result=19 <code>=MATCH($Q$37,Holidays!$A$3:$OE$3,0)</code> returns the Column number result=184 How do I find out what is...

Hi, {=INDEX('190708'!H2:H3333;MATCH(1;('190708'!A2:A3333=Sheet1!A3)*('190708'!B2:B3333=Sheet1!B3);0);MATCH(Sheet1!H2;'190708'!2:2;0))} In this case, row 2 and column 8 in the chosen array, the value in that cell is 9 but excel returns #REF . What is wrong?

Hi, I have a spreadsheet which I have created large IF Statements in. It was all good until I tried to open it again and it took 10 minutes to open! I then realised the size has increased to 10k KB! Here is one of my multiple IF Statements (one of the smaller ones). In column B are 3 digit...

I am currently using the following formula to extract from the columns below on the left containing various 3 digit numbers: {=IFERROR(INDEX(B$1:B$21,SMALL(IF($B$1:$B$21=$H$3,ROW($B$1:$B$21)),ROW(1:1))),"")} So in the first example I am looking for the value of 1 for the first digit and...

Hi The following if statement works perfect =IF(AND(B698="abc",G698="charge"),+C698,IF(AND(B698="abc",G698="payment"),-C698,IF(AND(B698="abc",G698="credit"),-C698,""))) When I add another condition ( made it red so it can be easier to see here), It returns “false” when the new...

OK so I have a formula that returns a true or false value for a certaintext string.... IF(ISNUMBER(SEARCH("EPN",B40)),"EPN","") So if EPN is in B40 then EPN is shown in the cell where the formula is enteredotherwise it is blank. Works perfectly. But I now need to search a number...

Hello. I have had the below formula in a spreadsheet for quite some time now. The purpose of this formula is to determine the name of the Excel file that holds the formula. A table called "t_Parameters" holds the name of a subdirectory that is appended to the end of the the directory. So, if...

Hi I've got an IF statement that i) checks if the value of a cell in another sheet is blank - if it is, then it returns nothing ii) returns the value of that cell if it is not blank. =IF('Tab1'!$S5="","",'Tab1'!$S5) However, the formula keeps changing every time a macro, which...

This is my formula but if any cell in range c5-c41 is blank it returns a ref error...

The following code has worked before, but now it doesn't and I can't figure out why. I just determine the value in the activecell and if it's more than one I just copy the row and insert it below the selected row, but for some reason I get a runtime error 1004 and the insert method fails. I...

Should be an easy one for you guys, I'm trying to produce a time/date stamp without using VB code, needs to be a simple formula. I'm trying this "=IF(F6="","",(now))" but it returns "#NAME?" Any suggestions? Thank you

Both of these formulas are working (no errors) but not quite up to the standards that I want for the workbook. The first formula looks at a DOB on worksheet ‘personal_info’!E12 and if it’s blank, returns a blank. Otherwise, it will return a ‘year’ that is based on the DOB of the user. If...

Hello all, I got 2 userforms; Productionreturns and QA returns. Both contain the same labels; lblGelcodeR and lblProductnameR. Both contain the label info; if you click on it a new form opens: Information The form Information contains the label lblGelCodeR and lblProductnameR. They should...