# Cell Returning #n/a

Mar 29, 2008
i have this formula in cell e28 =IF(B28<>"",LOOKUP(B28,STOCK!A1:A500,STOCK!B1:B500),"") and when its empty it returns a blank cell

but in cell e29 i have exactly the same formula =IF(B29<>"",LOOKUP(B29,STOCK!A1:A500,STOCK!B1:B500),"") but i returns #n/a

why is this and how do i stop it i want the cell to be empty unless there is a code entered in b29 in which case it will look up the relevant info for me

View 9 Replies
ADVERTISEMENT
Apr 23, 2014

Basically, I want to use look in one cell to get a value then in a table look in a coloum to find the same value then return a value from a different column in that row.

This is to organize groups of children participating in an event.

See the attached spreadsheet for details.

Groups example.xls

View 1 Replies
View Related
Feb 5, 2009

I am looking to return the larger value of 2 cells into a seperate cell.

View 2 Replies
View Related
Jul 30, 2009

I had this problem a few days ago and NVBC suggested Text to Columns, which worked initally but didn't seem to 'save' or stick... also, I'm not sure what is causing the problem to begin with. It was my understanding that Excel couldn't return a truely 'blank' value. If a cell was blank or empty, Excel still 'sees' it as Zero. WHY then, for the Love of Gummi Bears, will Excel not calculate my formula. It's a simple formula!

=A13+B13

I've tried

=SUM(A13+B13)

I've tried

=IF(ISERROR(A13+B13),"",A13+B13)

But if don't TYPE in a 0 or a number, then it just does nothing. 50 + 0 = should be 50 not blank or #VALUE!. 50 + Nothing = should be 50 not blank or #VALUE!

Attached is a small copy of what I'm trying to do. I've double checked that all number cells are formatted to numbers, not text. I tried the Data:Text to Columns trick. I've tried Tools:Options:Show Zeros. I've tried Manual and Automatic Calculation. They shouldn't have to type in 60 cells of Zero to get the simple calculation.

View 2 Replies
View Related
Jan 7, 2013

I'm linking one sheet in a workbook to another. The formula is simple,

='Link 1'!E1

If there is data in the cell, it replicates it which is what I'm after. However if there is no data in the cell it returns a "0", not what I want at all.

View 2 Replies
View Related
Nov 11, 2013

I am trying to return a cell with a simple yes or no answer and having a little difficulty.

I have entered the following formula which is returning #NAME

=IF(E4<=1.25,ORH4<=0.01,yes)

Calculation needs to be as follows:

Install Cost <1.25 and Profit atleast 0.01

The YES NO needs to be in the Invite? column

View 4 Replies
View Related
May 28, 2009

I have a current formula using SUM formulas obviously in the answer cell the answer is returning 0 due to no data inputed, how do i make the cell show empty.

eg. =SUM(A1*A3) returns answer 0 need cell to be blank.

View 2 Replies
View Related
Dec 10, 2007

I have a sheet containing collums with month an weeks. I try to find out which collum the first week start and where te last week the same mont ends.

Let say A5:E5 is filled with december in each cell. The row below (A6:E6) says week 48, week 49, week 50, week 51, week 52.

I want a formula telling me de Cell reference where desember begins en where it ends. In my Excample (A5 and E5). If Possible, I also could prefer that the cell reference was to rows below start/end. In my example (A7 and E7)

View 14 Replies
View Related
Jun 27, 2009

i'm trying to figure out a way that would return the address of the cell that contains the highest/lowest value of a range i.e. =cell("address",min(g3:g6)).

if the lowest value was in cell g4, i'd want the output from above formula to be $g$4.

View 14 Replies
View Related
Jun 24, 2013

view the graph below:

A

B

1

EXTENSION 101

2

INBOUND TALK TIME

55M19S

3

OUTBOUND TALK TIME

1H22M13S

[code].....

I am trying to create a formula that will act like a vlookup but will return a different row. In other words, On page two I need to write a formula that says: In column 1 if you see "Extension 101" return column 2 of the row right beneath it, so on and so forth. So page two should return a mirror of what page one says, the difference is I would like people to type in "Extension 101" in a cell and it would return all the date.

A

B

1

EXTENSION 101

2

INBOUND TALK TIME

55M19S

[code].....

Cell A1 would be typed in//Cell A2-A4 would be static//Cell B2-B4 would return these numbers based on info from A1.

Cell A1 would always be unique in column A (on both pages).

View 4 Replies
View Related
Mar 4, 2009

The conversion rate is updated daily from telephony reports, and I manually sort it into conversion order each day before sending it round.

What I want to do is find the highest converter for each team.

So:

Column A ('TeamLeader' range) has the Team leaders in, column B has the agent name ('Agent' range), and column F has the agents conversion rate ('conversion' range).

I think I'm on the right track with this formula:

=(MAX(IF(TeamLeader="Bonnie",Conversion))

But this only displays the figure in the Conversion rate, how to I get it to 'step back' 4 column's and show the agents name?

I tried this:

=VLOOKUP(MAX(IF(TeamLeader="Bonnie",Conversion)),A5:F60,-4,FALSE)

but that returns #NA - is this because I'm asking the VLOOKUP to look 'backwards' 4 columns?

View 9 Replies
View Related
Feb 5, 2010

I have find this code on web and I'm good with VBA at all. So I need help with this code below.

instead of text( marked with red ) i need to return value from cell, let's say from A1 and I want to have this macro running when excel is opened.

Sub Animate_String()

Dim stxt As String

Dim x As Integer, y As Integer

Dim Start, delay

stxt = "Lookup cell Aq"

For y = 1 To 100

View 9 Replies
View Related
Apr 1, 2008

my spreadsheet to return the word unit if cell a18=1 or the word units if its over 1 but i also want the cell to remain empty if there is nothing input into cell a18 is this possible

View 14 Replies
View Related
Feb 25, 2014

Essentially what I'm trying to accomplish is import a file from Quickbooks and determine whether it was a Credit Card, Check or Invoice based on the account number.

For example:

45-12345 should return the value CC in an adjacent cell because it contains the "-" character.

I0123456 should return the value INV in an adjacent cell because it contains the "I" character.

01234567 should return the value ACH in an adjacent cell because it doesn't contain either character.

I tried using VLookup, but I can't figure out how to write a formula for when it just contains a character, only if it matches it or is text/number.

View 4 Replies
View Related
Apr 19, 2013

All the answers I can find for this address either the auto-calculation setting or formatting the cell as a number. I am using this same formula in a bunch of cells to calculate totals: =SUM(E12:E17). It works find all over the sheet, except in one box. There is no difference in formatting or data type or anything.

Not working: notworkingexcel.png

Working: workexcel.png

The numbers being summed from the cells above the one in question are referenced from another sheet if that is relevant.

View 3 Replies
View Related
Feb 27, 2009

I am not sure if this is doable in VB or if there is an excel function to do this but I am using a rather large worksheet that is shared amongst my staff. I would like to have the sheet clear any filters that have been used by one member and for the sheet to save with the cursor in the home cell (A1). This way the sheet is ready for the next user. Can this be done with VB?

View 8 Replies
View Related
Feb 17, 2006

I am wondering if there is a way to return a cell reference by having excel

look at a table with certain parameters. For instance...

If there were a table with repeated dates such as:

Feb 12 1500 1530

Feb 12 1745 1750

Feb 13 1215 1245

Feb 13 1610 1700

Feb 13 1850 1900

Feb 14 1050 1140

Is there a way to return the cell number (ie A6) for the first instance of

Feb 14?

View 13 Replies
View Related
Nov 14, 2008

I have a sheet which uses a vlookup to find the data on a large sheet. normally to get the cell reference of the data i would use cell("address",......

However this appears not to work with a vlookup.

View 10 Replies
View Related
Oct 4, 2011

I am using the following code (I found here), to test for unlocked cells when I am creating a sheet. (I'm really new at this) I have 2 issues. First this (below) doesn't end itself, I have to escape or pick another cell. Second, I would like another button to return cells to the previous color after I have corrected any locked/unlocked cell errors.

Private Sub CommandButton2_Click()

Sheets("bf calcs").Select

Sheets("bf calcs").Unprotect ""

[Code]...

View 6 Replies
View Related
Jul 10, 2012

I am trying to return the value in the cell immediately to the left of a Maximum value found in 3 cells. I've added the MAX(Q4,S4,U4) formula into AF4. I've tried several variations without success.

View 5 Replies
View Related
Mar 25, 2013

Seems like this one should be pretty easy but I'm stuck... I just want to return a boolean based on whether the currently active cell contains the character "#". I've been trying to use the find method but I can't figure out how to convert a match or lack of a match to a boolean.

View 1 Replies
View Related
Feb 2, 2014

i am trying to return the numerical characters from a cell so in A1 i have 6-4 3-6 7-5

A2 i would like to return 6

A3 4

A4 3

A5 6

A6 7

A7 5

there are also possibilties that A6 & A7 could b 2 digit numbers such as 10-12 or could be 9-11 or 11-9 etc

View 4 Replies
View Related
Mar 3, 2010

I would like to have the answer to a formula, say in cell G6, to show up in say G5. I notice in my college coarses, doing our homework through excel, the solutions are always in a cell around the entry cell.

View 9 Replies
View Related
Jan 27, 2014

I have a cell in which I am entering either the letter "C" or "S".

I then want the following to happen......

If the cell contains the letter "S" do = IFERROR(VLOOKUP($B7,'Asset Growth Rate'!$A$2:$F$39,2,FALSE)*$C7,"-")

If the cell contains the letter "C" do = IFERROR(VLOOKUP($B28,'Asset Growth Rate'!$A$2:$F$39,3,FALSE)*($C28+$F28),"-")

The formulas themselves are already working in separate cells, I now just want to combine them depending on whether the cell contains S or C.....

View 1 Replies
View Related
Dec 31, 2006

The formula should take the info from validation lists in R13 & S13, and match them up in F3:O3 (T1Q2) & C6:C15 (T2Q2) and return the data in the intersecting cell into R9 (Where the formula is kept). In this instance, it should find 1 at I3 and the 4 at C15 and return NN to box R9.

View 10 Replies
View Related
Nov 13, 2008

If it can not locate the correct responce I want it the formula cell to be blank. How do I adjust my formula to do this?

This is the forumal I am currently using

=VLOOKUP(A26,Subs!A:F,5,0)

I have also tried this one as well

=IF(ISNA(VLOOKUP(A6,Subs!A:E,5,0))+(VLOOKUP(A6,Subs!A:E,5,0)=""),"",VLOOKUP(A6,Subs!A:E,5,0))

View 3 Replies
View Related
Nov 20, 2008

Take the displayed date from a cell, in this case formatted as yyyymmdd, and simply display the date as plain text, not converted to the 1900 date system. For example, I have a date cell that reads 20080610, but everything I have tried returns 39609. But I want it to return 20080610 in plain text so that I can use RIGHT and LEFT functions to extract the year, month and day.

View 2 Replies
View Related
Apr 6, 2014

Referenced cells returning zeros: can these be made blank or string data?

But why I still get the value 0 instead of blank cell when I press ctrl+shift+enter?

My formula = IF(U15="Bank", PROPER(E15),"")

and E15 = VLOOKUP(B15,'Customer Data'!$D$6:$K$100,3,FALSE)

View 5 Replies
View Related
Sep 12, 2008

I am trying to search a cell for multiple different strings and if the string is found, multiply a nearby cell by a factor of 1.5. if the strings are not found i want the original cell value to be used. I have tried using find functions but they do not seem to work. I have also tried using nested if functions with no luck..

View 9 Replies
View Related
Dec 19, 2011

I am trying to trigger a formula if a cell (B2) is populated (with anything) but if it is blank for the destination cell to remain blank.

The formula I am trying to trigger is =IF(G18=0,100,100-(100/(1+H18))).

View 2 Replies
View Related