VLOOKUP Cell Referencing #VALUE Error

Oct 1, 2009

I'm working on a spreadsheet for a gaming community. This sheet is used by more than 3000+ players and growing. It has to be updated roughly twice a week for new inventory items, which can range from 1 to 3 new items weekly.

To make it easier on new and existing members, I want to create a way to make updates with the spreadsheet easier and to allow players an easier way of updating the spreadsheet rather than re-entering all the data over and over again twice a week.

So, I started to use VLOOK in some formulas to look at the data and pull the corresponding information. However, this same information is used throughout the sheet and referencing this information has brought about #VALUE errors that I cannot seem to figure out how to get around.

I've included a copy of the spreadsheet to download and review in hopes of finding a solution.

Here's what this speadsheet is doing:
MyInventory is where the players will enter all their items, with some being drop-down selections and manual entry.
The LookupedInventory worksheet is where the VLOOKUP (columns C,G,J,Q,etc.) formula is used to look up entries in the MyInventory worksheet and pull their values over to the LookupedInventory worksheet.
Now these values from the LookupedInventory will be carried over into two other worksheets, InventoryTableAttack and InventoryTableDefense. These two worksheets are where my #VALUE errors are appearing. If the cell referenced is empty, the #VALUE error appears. However, if the cell references a number, the rest of the formulas tied to to this cell reference work. However, since there are errors, the rest of the worksheet cannot function until I fix all the #VALUE errors.
So, what I'm seeking is to see where my issue is and what I need to do to fix it.

If I need to take a different approach to this, please let me know what that is and how to go about doing it.

I've even thought about a macros that copies and paste all the current data from an older spreadsheet to the new one, but I'm not that good at macros. If I can stay away from macros, that would be good, but if not, then I'll have to dig in my heels and start leaning.

View 9 Replies


ADVERTISEMENT

Worksheets & Cell Referencing #REF! Error

Mar 18, 2009

I have 3 worksheets:

Lets call them RED, BLUE AND GREEN

Essentially GREEN pulls Values from BLUE and BLUE pulls Values from RED. Red being the base worksheet from which everything is calculated.

In my GREEN workbook I have the following =MIN('BLUE'!H14,'BLUE'!L14)

And this will work fine.....Until I press a clear Worksheet button that has been implemented on the RED worksheet (button was not implemented by me and I do not wish to edit anything with respect to that button)

When RED is reset the #REF! Error will appear in GREEN. This is fine because it cant find any values From BLUE. However when I do input new values into RED, which in turn updates BLUE which is where GREEN in theory should then be able to pull the results from.... But it just stays with a REF! error.

Is there anyway i can permanently set a CELL to have the following code =MIN('BLUE'!H14,'BLUE'!L14), no matter what happens to other cells?

View 10 Replies View Related

Vlookup Function Referencing A Formatted Cell?

May 1, 2014

I am trying to use the vlookup function but the range of reference has that little green triangle in the corner and if I f2 each cell that triangle goes away and the vlookup function works great. Do you know what is going on here? And also, a quick way to convert all the cells with the green triangle to be without the triangle in a whole range? I don't want to have to go to each cell individually and f2. I've attached an example worksheet for reference to what I am describing. vlookup format.xlsx

View 2 Replies View Related

VLOOKUP Referencing The Diagonal Cell Instead Of The Horizontal

Sep 4, 2013

I am trying to get my formula to reference directly to the right of the look up value but it keeps returning the diagonal value that is located to the right and up one cell.

excel 1.jpg
excel 2.jpg

View 14 Replies View Related

Referencing Cell Based On User Input That Corresponds To VLookup

Jun 26, 2014

So my issue this time is that I have a bunch of columns that correspond to a month. If the user wants to view data from that month column then they have to link to that cell specifically (the worksheet being used is different than the one in which the month columns are)

My goal was to create a single cell in which the user typed in a month abbreviation such as jan, feb, mar.. so on and so forth. From there they correspond to a vlookup table which tells you which month is in what column (because that part never changes) so jan is in column c and feb is in column d... continuing until december is in column n. So the vlookup corresponds to the table and that works fine, type jan into the cell and the vlookup returns c. So i think this will work.

My problem is that in the cell I am typing this in I want it to be a part of a whole cell reference such as c5 or d7 where the 5 or 7 is a constant (I have that part down) however the reference is to another sheet. So in sheet 2 I want to reference sheet 1 i.e.

Formula: [Code].....

<-- is the desired return.

Right now (in sheet 2) I have

Formula: [Select Code] .....

Which returns the desired d5 but as text and not referencing sheet one. I tried

Formula: [Code]....

But this makes the vlookup malfunction (i think it starts looking in sheet 1 for the month cell but it is not and can not be there.

SO how do i force a vlookup to turn into a cell reference, and how do i make that cell reference come from a different worksheet without causing the vlookup to malfunction.

Attached File : examples.xlsx‎

View 2 Replies View Related

Vlookup Referencing Cell With "left" Function

May 25, 2007

See the attached example for an illustration of my problem. Essentially, I am trying to reference the first two numbers of an account number when using VLOOKUP. The function then references a table with account classifications based upon the first two numbers of each 10 digit account. This seems so basic, but is driving me up the wall!

View 3 Replies View Related

Vlookup Cell Format Error #n/a

Jul 20, 2007

I have a range of data that i have copied in from another source. I have a list that I am trying to perform a vlookup from but its only returning #n/a. my forumula looks like this: =VLOOKUP(A1, 'range'!A:C, 3, 0). On my range worksheet, if i double click on one of the cells i.e. A1, then the data in the cell right aligns in the cell and the data in column 3feeds through to my vlookup. I have about 3000 cells that require a lookup, do I need to double click each of them in succession in order to get my lookup to work?

View 6 Replies View Related

Referencing A Field With An Error In It

Mar 15, 2007

My problem is I have a field when imported occasionally has a #Value! error in it. Most of the records are fine. As I will be importing it into another DB I need to have the #Value! removed and replaced with a Zero. Here is what I tried. I have a field that is displaying a #value! error. In another field that is referencing that field I wanted to do the following if statement. Basically if that error value is displayed show a zero, else show the value of the square. (Note I also tried it with an IIF but got a # Name? error)

If((Q2) = "#Value!",0,(Q2))

Or should I be looking for an error? When I ran it, It basically gave me the value of the Q2 field.

If((Q2) = Error(#Value!),0,(Q2))

Or is there some other way to get around the error?

View 2 Replies View Related

Referencing Variable Workbook Tab In VLookup?

Nov 29, 2012

I have a challenge with Vlookups!

I have one workbook called Ratecards, which contains various tabs which are named based on a client code. e.g. ABC02

I then have another workbook which has employees in column B and the client code in C3.

The current Vlookup is =VLOOKUP(B8,'[ratecards.xlsx]ABC02'!$A$5:$N$168,4,false) which works fine for one client.

I want to substitute the ABC02 in the vlookup for cell ref C3, as the contents of C3 is variable and I want it to tell the sheet which ratecard to refer to.

View 3 Replies View Related

Vlookup - Referencing Sheet Index

Dec 4, 2009

I am trying to find out whether it is possible to reference to a sheet index number, instead of sheet name, in a VLOOKUP formula.

For instance, normally I would write:

VLOOKUP(A1,'ExampleSheet'!A1:B10,2,False)

(I hope got the translation to English language Excel correct)

Let's say that the sheet index of Examplesheet is 2 (at least it is 2 the way VBA sees it).

Is there a way I could reference sheet number 2 in the VLOOKUP formula, instead of its name? My sheet names vary, but the formula should look in the same area, regardless of name.

View 9 Replies View Related

Runtime Error Referencing A Picture?

Jan 17, 2012

correct this line of code?

Workbooks("Copy of ReportMacros.xls").Sheets(Sheet1).Shapes("Picture 1").Copy

View 2 Replies View Related

#REF! Error Referencing Worksheet After Importing Sheet

Feb 9, 2008

I have a template i'm making that is full of formula references to sheets not yet in existance in the workbook. what i want to happen is this: My code imports data files, renames the tabs to 1, 2, 3, 4... The formulas i already have are set in the CONCATENATED tab.... an example of such a formula is: ='1'!$P2.

so this should work, and yet it doesn't. the sheet is imported as it should be, the tab is renamed to 1, and yet the CONCATENATED sheet still shows #REF for the cell value. if i click in the cell, click in the text bar and enter out, the formula updates. there apparently is no option that i KNOW OF (although i'm sure there is something out there that does...) that will update this without me entering into the text bar and entering out.

Question: how do i manually update all references once the sheets to those references have been imported?

View 2 Replies View Related

Lookup Referencing Closed File Returns Value Error

Aug 13, 2008

I have a file created in Excel 2003 that uses a vlookup to reference another file, also created in Excel 2003. The function returns VALUE when the referenced file is closed. I get the VALUE error whether or not I update links upon opening the file. If I open the source file, the function calculates properly.

Here is the formula when the referenced file is open:

=VLOOKUP(A38,'[June Scorecard confirmation.xls]VPSC Summary'!$C$10:$F$22,4,FALSE)

The full & correct path appears in the formula when the referenced file is closed.

Columns C, D & E in the referenced range contain text, column F contains a Sum. This file is linked to another file, also using lookups. The linked area in this file works without problem, but it is a precedent to the calculation in column F.

Both these files reside on a network in subdirectories of the same logical drive. I am using a laptop that briefly had Excel 2007 installed on it, but was wiped clean before it was issued to me.

View 3 Replies View Related

Vlookup Formula In VBA - Object Error (input A Formula Into A Cell)

Apr 23, 2009

I'm having some trouble trying to get excel to input a formula into a cell. I'm still a novice at VBA right now, so I don't think my problem will be too much of a brain buster.

I want a formula in Cell A6 (and I already know it correctly works) in this format: =E6&VLOOKUP(I6,'FA-Fund Data'!B$1:C$2000,2,FALSE)&J6

View 3 Replies View Related

Vlookup Error Msg "unable To Get The VLookup Property Of The WorksheetFunction Class"

Jan 8, 2009

I am receiving a run-time error with following code. The error message is "unable to get the VLookup property of the WorksheetFunction class". I only receive the message when the lookup value is not found in the table.

I thought adding the "False" command at the end would return an "N/A" but it didn't. Is there anything I can add to avoid this error?

View 3 Replies View Related

VLOOKUP #NA Error

Jan 29, 2008

I created a workbook with three sheets, I do a vlookup formula that looks like this:=VLOOKUP(D3,Sheet3!A:D,2,FALSE)

so basically, find the value of D3 and look for the exact match in sheet 3 (column range a-d) then report back the value found in the second column.

I get an #NA error with this. Funny thing is that if I go to sheet 3, find the correct value and "re-type" it in, it will now pull the information I want.

I've tried some basic formatting changes that dont fix the issue and the only thing that seems to work is retyping the values into sheet 3.

I've got about 1500 rows I'd have to retype so the idea doesnt excite me.

View 9 Replies View Related

Excel 2010 :: Cell Summing Through Referencing Neighbor Cell Contents?

Feb 12, 2014

we have a Excel 2010 worksheet containing multiple tables.

Table1
ColumnA ColumnB
1 Blue 12
2 Orange 14
15
11
3 Red 10

Table2
ColumnA ColumnB
1 Blue 11
2 Black 13
15
11
3 White 10
19
17

On a separate worksheet we want to calculate with the first worksheet's values.

For example: Calc1: Sum ColumnB IN Table1 WHERE ColumnA = Orange

How do we do such a calculation/formula?

View 4 Replies View Related

N/A Error Using VLOOKUP Even Though There Is Match?

Jul 24, 2014

Why I am getting N/A errors in sheet 1 of workbook?

For example, in sheet1, Cell B2 should equal 3. And it should stretch across the entire data range, so even something like B14 should return 3.

View 8 Replies View Related

Worksheetfunction.vlookup Gives #VALUE! Error In VBA

Nov 29, 2009

I've tried using the following (simplified) code to look up a date in a named range and return the result from the same row in the next column to the right. I can do this easily in the worksheet, but I can't write a VBA function to do it. Code:

View 2 Replies View Related

Error In VLookup Formula

Aug 30, 2009

I want to learn VLOOKUP formula in this following problem.

VLOOKUP($A3,Sheet2!$A$2:$Q$13,$D2,0)

I am attaching the file for the same.

View 2 Replies View Related

Vlookup Error Message

Dec 15, 2006

I am trying to run the macro and I get this error:

Compile Error! Sub or Function not defined

for the following
RFQnum = VLookup("RFQ Number", CPARSdata, 2, False) 'RFQ# should be same for each supplier

CPARSdata is a named ranged with 25 columns and 338 rows

View 9 Replies View Related

Vlookup- NO Match Error

Dec 15, 2006

I am using Vlookup to search for a text string in column A and storing the value of column B for more than 40 variables.

I do NOT want a macro error on Vlookup each time it can not find a match. I want to store an "error message" in that variable and move on.

countif and a rountine handler sounds like a lot of coding for each variable; can I use ISNA?

SAMPLE
Sheets("CPARS download").Select 'CPARS DOWNLOAD
RFQnum = Strings.Mid(WorksheetFunction.VLookup("RFQ Number", _
Range("CPARSdata"), 2, 0), 1, 13) 'RFQ# should be same for each supplier
' RFQnum = Mid(RFQnum, 1, 13) 'truncate the supplier code at the end
BidDue = WorksheetFunction.VLookup("Bid Due Date", Range("CPARSdata"), 2, 0)...........

View 9 Replies View Related

VLOOKUP Retuns #N/A Error

Jul 6, 2007

I am trying to write a formula for a vlookup by product codes for a very large set of data which is then summed. My problem is that not all of the product codes are used, resulting in a large amount of #N/A errors that prevent me from being able to sum the columns.

Is there a formula that I can use to return a 0 in place of an #N/A for a vlookup?

View 9 Replies View Related

Vlookup Text N/A Error

Sep 8, 2009

I am struggling to figure out why my vlookup does not work, i am trying to vlookup invoice cost based on the model numbers which is in text.

I have converted the text to general but this still does brings the N/A errors, is there something else i am forgetting?.

View 9 Replies View Related

Vlookup- To Replace The Error With A 0

Oct 31, 2009

My formula is
=IF(E2"C",0,Vlookup(D2,NUM_GAME_PACK,QUA,FALSE))
I need to replace the error with a 0.

View 9 Replies View Related

Runtime Error '438' With Vlookup

Oct 13, 2006

I have written some code to perform a Vlookup for some data from another sheet but when i run the code it comes up with runtime error '438' "Object doesn't support this property or method".

Sub RAS_StockUpdate()
Dim Count As Integer
Dim SKU As Long
Dim FileName As String
FileName = ActiveWorkbook. Name
Workbooks.Open FileName:= _
"\Hwyfile1publicRange TransitionRAS DatabaseRAS_Data_Export.xls"
Windows(FileName).Activate
For Count = 1 To 100
Range("B16").Select
ActiveCell.Offset(Count - 1, 0).Select
Select Case IsNumeric(ActiveCell)
Case True...................................

View 3 Replies View Related

VLOOKUP Returns #N/A! Error

Oct 28, 2006

I have a sheet that uses vlookup when the lookup returns #na error how can i conditional format these cells to so text is same as background

View 9 Replies View Related

Stop VLOOKUP #N/A! Error

May 12, 2007

When I have the Vlookup formula and the field where I have the data to lookup is empty I get a sign with a number symbol and N/A, how can I tell excel not to show me this when the field where I type the information that I want to look is empty?. I want all the formulas fields to show nothing.

View 6 Replies View Related

Error In Vlookup Function With Single Value

Jul 28, 2014

Please find the attachment in which i have mentioned all the details about the error in VLOOKUP function. I couldn't understand why I am getting that error for that single Vlookup value while others are ok.

Vlookup error.xlsm

View 8 Replies View Related

Error Message When Using (VBA) Vlookup Function

Mar 2, 2014

I'm running this line (from longer code of course), where i/g are integers and h is a range:

[Code] ......

And I'm getting run time error '1004'.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved