Free Microsoft Excel 2013 Quick Reference

Why doesn't this VLOOKUP work?

Ok so in the attached sheet, column T and U have VLOOKUPS in them but neither work.

They look at the corresponding column B value. The value is in there and looks at another cell. e.g B4 get's it's value from B3.

If i type in to B4 what's already in there, e.g 1100, the look up works.

Why is this? What is wrong with using a value generated by a formula?


Post your answer or comment

comments powered by Disqus
Guys, why doesnt the below code working, i am simply asking the macro to delete entries which are less than 5 days old, but it doesnt do it, i dont get any errors either

Code:
 
  With Range("A1:J1")
        .AutoFilter Field:=6, Criteria1:="

I have the following formula in cell I2 (copied down as far as needed and
copied across to AA2):

=INDEX($A2:$A$253,MATCH(I$1, $D$2:$D$253,0))

which when copied to J2 becomes (for the sake of discussion):
=INDEX($A2:$A$253,MATCH(J$1, $D$2:$D$253,0))

In cells I1 through AA1 are numbers (representing Districts) A small sample
of the Districts is:
1;2;4;5 and a small sample of the lookup table is:
Store;Description;Zone;District
184;Chula Vista South;11;1
559;Mission Gorge;11;1
157;Orange - Chapman;11;2
520;Lake Forest;11;2
523;Newport-N/Wpc;11;2
519;Encino;11;4
548;Porter Ranch, CA;11;4
167;Studio City;11;5
188;Rolling Hills Estates;11;5
196;San Pedro CA;11;5

What I wanted to do was to get all of the stores that belonged to a district
(rather than doing a copious amount of copying and pasting)

I started with a very similar formula, which did not work. I ended up with
the 1st store found, repeated when copied down. Formula is the same as the
first formula showed, but with an absolute reference for the Index row
instead of relative as it is now:

=INDEX($A$2:$A$253,MATCH(I$1, $D$2:$D$253,0))

I thought that if I made the row relative, it would work for the first
column, but when I copied the formula across and down, I would end up with
the same problem in the subsequent columns (I would get the correct first
store, but all of the stores under that would be the same as the first.) My
plan was, once I had the first column working, I would experiment with
different parts of the formula to try to get subsequent columns working;
maybe using something like the Offset of the match from the previous row.

Much to my surprise, this formula basically did what I wanted. Since the
number of stores per District varied, my plan was to copy the formula across
and then down to a point where I figured I would start getting #N/A errors
because, for a given column, there were no more stores in that District.

However, what I found was that when all of the stores for a District were
returned, it would start displaying stores for the next District. I could
quickly identify where I needed to start deleting formulas by looking at the
first value in the next column and seeing it was the same as the store in the
column I was looking at.

So my question is, why does this formula work (for subsequent columns?)
Also, why don’t I get #N/A values when there are no more stores for a given
district? And, are there alternative formulas that would also work?

BTW, I am eventually going to create named ranges from the different
districts, so will be changing the # in row 1 to District# (i.e. District1)
since you apparently can’t use numbers for Named Ranges. Also, I discovered
(but should have realized before I tried) that D# won’t work (i.e. D1, D2,
etc.) as those look like cell references. So, so far, my new formula looks
like:

=INDEX($A2:$A$253,MATCH(VALUE(RIGHT(K$1,LEN(K$1) - 8)), $D$2:$D$253,0))

--
Kevin Vaughn

why is this not working? it should add up the values of every 6th cell (H6
through to FB6), but if any cell is over the value of D6 then D6 's value is
taken as the value for that particular cell in that particular instance. it
seemed to be working, but if i enter a value of over D6 in H6 etc, the
answer is worng.

=SUMPRODUCT((MOD(COLUMN(H6:FB6),6)=2)*(H6:FB6<=D6)*H6:FB6)+D6*SUMPRODUCT((MOD(COLUMN(H6:FB6),6)=2)*( H6:FB6>D6))

cheers!

steve

If anyone could help me with this it would be very much appreciated, Iíve been trying all sorts of combinations with no luck. You will need to look at the picture I've attached to make sense of the below.

In (C2+) it should display one of the following results - "Listed", "-" meaning not listed, or "Listing Ended". This is worked out from the corresponding dates to the right of "Selling Status" and in (G2) which contains the current date. So when there is nothing entered in (D2) "Selling Status"(C2) should display "-", if a date is entered into (D2) it should then go to "Listed". This part Iíve managed to accomplish, however, when I try add the next part to the equation, it doesnít seem to like it.

If "Listing End Date"(E2) is less than the current date(G2), "Selling Status"(C2) should then go to "Listing Ended".

This is the current formula I have (below) which does actually display "Listing Ended" However it also contains the "Listed" which it should over right.

Current Formula - =IF((D2=""),"-","Listed"&IF(E2<G2,"Listing Ended",))

Hi,

i'm having a little problem with vba(again), and could use some help.
The following formula gives "error 1004"
It's just an "IF" formula, so why does it not work?
Please advice.

Regards,
Peter

Range("H14").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-3]=""Sheet1"",'Sheet1'!R[-11]C[-3],IF(RC[-3]=""Sheet2"",Sheet2!R[-1
1]C[-3],IF(RC[-3]=""Sheet3"",Sheet3!R[-11]C[-3],IF(RC[-3]=""Sheet4"",Sheet4!
R[-11]C[-3],IF(RC[-3]=""Sheet5"",Sheet5!R[-11]C[-3],IF(RC[-3]=""Sheet6"",She
et6!R[-11]C[-3],IF(RC[-3]=""Sheet7"",Sheet7!R[-11]C[-3],IF(RC[-3]=""Sheet8""
,Sheet8!R[-11]C[-3],IF(RC[-3]=""Sheet9"",Sheet9!R[-11]C[-3],IF(RC[-3]=""Shee
t10"",Sheet10!R[-11]C[-3],IF(RC[-3]=""Sheet11"",Sheet11!R[-11]C[-3],IF(RC[-3
]=""Sheet12"",Sheet12!R[-11]C[-3],""""))))))))))))"
Range("H15").Select

Can anyone modify this to work please I think it does not because of the
"" problem? Did try and do this at first using worksheet function but
have up now just trying to paste the formula into the worksheet!!!

rng = "B" & ofst + 4
rng2 = "B" & (ofst + 5) & ":HZ" & (ofst + 5)
Range(rng).Formula = "=SumIf(B4:HZ4, " * QUAD * ",rng2)"

Many Thanks
Neil

*** Sent via Developersdex http://www.developersdex.com ***

Dear All,

Can you have a look at this and please tell why it doesnt work

Sub CopySheetAsHTML()

Dim fName As String
Dim wks As Worksheet

Set wks = ActiveSheet
fName = wks.Range("J4").Value

With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
"I:" & fName & ".htm", wks.Name, "DataRange", _
xlHtmlStatic, wks.Range("J4").Value)
.Publish (True)
End With
End Sub

Many Thanks in advance

Steve

I am working on cleaning up code from the recorder and I would like to know why this does not work.

Original from the recorder:
Code:
    Columns("O:O").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
What I tried to clean it up to:
Code:
    Columns("O:O").Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
What am I missing here? I am in the process of learning and want to know why this does not work.
Can this be cleaned up from the original to make it more efficient?

OK,nothing fancy here,but for some reason this wont work,I have a column
A of appx 67 movies I want to buy,next column B I have a # 1 if I own
it,and a # 0,if I dont,now below column b I have the simple formula
=SUM(B91-(B1:B90)) B91 being the total # of movies I want which is
67, and of course adding B1:b90 equals # movies I already
got..........once I type in the formula and hit enter it doesnt give me
a total it gives a #VALUE! line,so I changed B91 to the number 67,
=SUM(67-(B1:B90)) , and got the same #VALUE! total,I know Im going to
feel stupid once I here what Im doing wrong but for some reason I cant
get it right,what am I doing wrong

Thanks
Steve

--
Bigredno8
------------------------------------------------------------------------
Bigredno8's Profile: http://www.excelforum.com/member.php...o&userid=17162
View this thread: http://www.excelforum.com/showthread...hreadid=491215

Okay: 2 sheets
sheet1: called Nationality
Sheet2: called Graph

Sheet 1 consists of a table. Column A4:20 is various nationalities.
Columns B3-M3 are months of the year, with column B starting with JULY
Under Month are various percentages corresponding to nationalities for that
Month.
Cell I1: named MONTH. format of cell is general. Cell K1 is a date. Any
date can be entered, such as Dec 05.
at the bottom of this page is a graph reflecting nationailities, and the
graph
changes DEPENDING on the date entered in K1. Example: enter Dec 05 and the
nationality stats for the month of Dec show on the graph. Enter June 05 and
graph shows nationality stats for June.

Sheet 2 (called Graph) has a table
A4: A20 are nationalities
B3: cell is named PERIOD and C3: cell is named YTD for the graph
Formula in B4 is
=VLOOKUP(1,Nationality!B4:M4, IF(MONTH(date)>6,MONTH(date)-6,MONTH(date)+6))
under column YTD formula is simply
= nationality!O4
I could just copy and use this existing Graph and worksheets. My problem is
that my year commences in JUNE and not July and if I merely change Column B3
(on nationalities worksheet)to June it no longer registers correctly.
Does this explain in enough detail. i am desperate to use this worksheet
and graph, but cannot understand how it is reading the date and how to get it
to commence in June. i have checked everywhere to see if there is a formula
I am missing that would explain something else.

Excel 97, multiple worksheets. 1st worksheet is a list of contractors used
at work listed by their category, i.e. 'Fire Alarm Maintenance', 'PTAC
Units', 'Dectron', etc. plus their phone numbers.
I use vlookup in sheets following sheet 1 to look in sheet 1 and load the
contractor name and phone numbers.
My first couple of sheets work fine with something like this:
'=VLOOKUP("Carpets national",PhoneList!$A$4:$G$33,4,FALSE)'.
In this example the National carpet contractor's phone number will display
in the sheet that this function is used in.
In a later sheet I use:
=vlookup("Dectron",PhoneList!$A$4:$G$33,4,false).
All I get in the receiving cell is the visual of the function. No data, no
error.
when I click on the 'fn' on the toolbar I see each argument shown with the
proper result shown.
Any ideas on why the proper result is not being posted in the receiving cell?

Hello,
This code looks for each blank cell and copies the exact formula located right above it. But if the activecell is located within the range, then it doesn't work or sometimes, it partially copies the above formula then continues on leaving some other cells blank. Why must I have the starting line of beginning at cell A2 for this to work?

Range("A2").Select
    For Each cell In Range("A7:A85")
        If cell = "" Then
            cell.Value = cell(ActiveCell.Offset(-1, 0)).Formula
        End If
    Next
By the way, if the first line was to be Range("A1").Select, it will fail. Don't know why.

Thanks in advance,
Ricky

Hi I have written this macro
Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$G$29" Then
       'Ensure target is a number
        If IsNumeric(Target) Then
            If Range("G34").Value >= Range("G17").Value Then
            Range("I34").Value = "Cost covers rennovating second House!"
              If Range("G34").Value <= Range("G17").Value Then
            Range("I34").Value = "Need another Mortgage!"
                Application.EnableEvents = True
            'Allow run time errors again
            On Error GoTo 0
        End If
          End If
    End If
End If
End Sub
But it only works one way, it only works if G34 is greater than or equal to G17. Why doesnt it work the other way. Thanks
Alex

Excel 97, multiple worksheets. 1st worksheet is a list of contractors used
at work listed by their category, i.e. 'Fire Alarm Maintenance', 'PTAC
Units', 'Dectron', etc. plus their phone numbers.
I use vlookup in sheets following sheet 1 to look in sheet 1 and load the
contractor name and phone numbers.
My first couple of sheets work fine with something like this:
'=VLOOKUP("Carpets national",PhoneList!$A$4:$G$33,4,FALSE)'.
In this example the National carpet contractor's phone number will display
in the sheet that this function is used in.
In a later sheet I use:
=vlookup("Dectron",PhoneList!$A$4:$G$33,4,false).
All I get in the receiving cell is the visual of the function. No data, no
error.
when I click on the 'fn' on the toolbar I see each argument shown with the
proper result shown.
Any ideas on why the proper result is not being posted in the receiving cell?

Why does this forumla not work? =IF(J2=N14:N26,"A",IF(J2=N27:N40,"B",IF
(J2=N41:N52,"C")))

OK,nothing fancy here,but for some reason this wont work,I have a column A of appx 67 movies I want to buy,next column B I have a # 1 if I own it,and a # 0,if I dont,now below column b I have the simple formula =SUM(B91-(B1:B90)) B91 being the total # of movies I want which is 67, and of course adding B1:b90 equals # movies I already got..........once I type in the formula and hit enter it doesnt give me a total it gives a #VALUE! line,so I changed B91 to the number 67, =SUM(67-(B1:B90)) , and got the same #VALUE! total,I know Im going to feel stupid once I here what Im doing wrong but for some reason I cant get it right,what am I doing wrong

Thanks
Steve

Okay: 2 sheets
sheet1: called Nationality
Sheet2: called Graph

Sheet 1 consists of a table. Column A4:20 is various nationalities.
Columns B3-M3 are months of the year, with column B starting with JULY
Under Month are various percentages corresponding to nationalities for that
Month.
Cell I1: named MONTH. format of cell is general. Cell K1 is a date. Any
date can be entered, such as Dec 05.
at the bottom of this page is a graph reflecting nationailities, and the
graph
changes DEPENDING on the date entered in K1. Example: enter Dec 05 and the
nationality stats for the month of Dec show on the graph. Enter June 05 and
graph shows nationality stats for June.

Sheet 2 (called Graph) has a table
A4: A20 are nationalities
B3: cell is named PERIOD and C3: cell is named YTD for the graph
Formula in B4 is
=VLOOKUP(1,Nationality!B4:M4, IF(MONTH(date)>6,MONTH(date)-6,MONTH(date)+6))
under column YTD formula is simply
= nationality!O4
I could just copy and use this existing Graph and worksheets. My problem is
that my year commences in JUNE and not July and if I merely change Column B3
(on nationalities worksheet)to June it no longer registers correctly.
Does this explain in enough detail. i am desperate to use this worksheet
and graph, but cannot understand how it is reading the date and how to get it
to commence in June. i have checked everywhere to see if there is a formula
I am missing that would explain something else.

Why can't I get this to work? NewFN always equals the file I selected but
wbExt always equals "nothing" and the routine crashes. I can't figure out
what I'm doing wrong.
Thanks in advance,
Todd

Private Sub CommandButton1_Click()
NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),
*.xls", Title:="Select a file")
If NewFN = False Then
MsgBox "No File Was Selected"
Else
Workbooks.Open FileName:=NewFN

Dim wbExt As Workbook
Set wbExt = NewFN
wbExt.Worksheets("Sheet1").Range("C4").Copy

Application.CutCopyMode = False
wbExt.Close savechanges:=False
Set wbExt = Nothing

End If
End Sub

I am trying to use a simple VLOOKUP and for some reason i am getting the #N/A no clue why, please any help would be greatly appreciated.

******** ******************** ************************************************************************>Microsoft Excel - Book7___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC1C2C3C4C5C6C7C8C9C10C11C12C13C14C15C16C17C18C19C20C21C22C23C24C25C26C27C28C29C30C31C32C33C34C35C36C37C38C39C40C41C42C43C44C45C46C47C48C49C50C51C52C53C54C55C56C57C58C59C60C61C62C63C64C65C66C67C68C69C70C71C72=
ABCD1ANCHORAGE AK#N/A 2ANCHORAGE AK#N/A 3MONTGOMERY AK#N/A 4FLORENCE AL#N/A 5GADSDEN AL#N/A 6DOTHAN AL#N/A 7OXFORD AL#N/A 8AUBURN AL#N/A 9BIRMINGHAM AL#N/A 10BIRMINGHAM AL#N/A 11DECATUR AL#N/A 12FAIRFIELD AL#N/A 13FLORENCE AL#N/A 14GADSDEN AL#N/A 15HUNTSILLE AL#N/A 16MOBILE AL#N/A 17MONTGOMERY AL#N/A 18TUSCALOOSA AL#N/A 19BIRMINGHAM AL#N/A 20SPANISH FORT AL#N/A 21BIRMINGHAM AL#N/A 22BIRMINGHAM AL#N/A 23GULF SHORES AL#N/A 24DOTHAN AL#N/A 25AUBURN AL#N/A 26JONESBORO AL#N/A 27ROGERS AR#N/A 28FT. SMITH AR#N/A 29MESA AR#N/A 30TUCSON AZ#N/A 31MESA AZ#N/A 32TUCSON AZ#N/A 33TUCSON AZ#N/A 34GLENDALE AZ#N/A 35CHANDLER AZ#N/A 36PHOENIX AZ#N/A 37TUCSON AZ#N/A 38MESA AZ#N/A 39YUMA AZ#N/A 40GILBERT AZ#N/A 41SCOTTSDALE AZ#N/A 42CHICO AZ#N/A 43NEWARK CA#N/A 44TORRANCE CA#N/A 45VICTORVILLE

This code runs on the worksheet and when it's running like this it works fine. Except that it's not changing the cell values like I want it to before it sorts.
PHP Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Or Target.Column  2 Then Exit Sub

    Dim before, after As Integer
    Application.EnableEvents = False
    With Target
        after = .Value
        Application.Undo
        before = .Value
        .Value = after
    End With
    Application.EnableEvents = True
    
 '   Range("A10").Select
 '   Selection = after
 '   Range("A11").Select
 '   Selection = before
 '   Range("A12").Select
 '   Selection = Target.Address
    
 '   For Each cell In Range("B2", "B101")
        ''---If change was to decrease value
 '       If cell.Address  Target.Address Then
 '           If cell.Value > 0 Then
 '               If before > after Then
 '                   If cell.Value >= after Then
 '                       If cell.Value  0 Then
 '               If before  before Then
 '                           cell.Value = cell.Value - 1
 '                       End If
 '                   End If
 '               End If
 '           End If
 '       End If
 '   Next

Range("A1:L101").sort Header:=xlYes, Order1:=xlAscending, Key1:=Range("B2:B101")
End Sub 
When I turn on the code that will change the cell values in the range however like this...

PHP Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Or Target.Column  2 Then Exit Sub

    Dim before, after As Integer
    Application.EnableEvents = False
    With Target
        after = .Value
        Application.Undo
        before = .Value
        .Value = after
    End With
    Application.EnableEvents = True
    
 '   Range("A10").Select
 '   Selection = after
 '   Range("A11").Select
 '   Selection = before
 '   Range("A12").Select
 '   Selection = Target.Address
    
    For Each cell In Range("B2", "B101")
        ''---If change was to decrease value
        If cell.Address  Target.Address Then
            If cell.Value > 0 Then
                If before > after Then
                    If cell.Value >= after Then
                        If cell.Value  0 Then
                If before 

The error that i get is runtime error 1004. Why would this happen on my computer it works fine and my friends computer i get that error

I have 4 worksheets in 1 spreadsheet. The 1st worksheet contains a summary
of subtotaled information on the other 3 sheets.

I subtotal sheets 2-4 after sorting the data by the date column.

In sheet 1 I use the vlookup function to capture the subtotaled fields based
on the date.

Here's the vlookup function:
=IF(ISERROR(MATCH($A14,Match_CDA_Date,1)),
0,
VLOOKUP($A14,CDA_Weekly_Totals,10))

This function works fine for weeks 1-8; after that I either get a zero or
erroneous data returned. This error occcurs in different cells for each
worksheet.
The error may start in row 10 for sheet 2, row 13 for sheet 3, and row 9 for
sheet 4.

Why does the vlookup work okay in some cells and fails in rows further down?

Ive turned on auto recover and set it to 5 mins but no matter what happens
there is never a backup document anywhere. I made the system crash with
unsaved changes but there is nothing there except the original document with
all changes lost. What does this actually do and how can I make it work.

When I open a work book or file, I receive the following;

File Conversion

Why does this appear?


No luck finding an answer? You could always try Google.