Mr Excel & excelisfun Trick 24: VLOOKUP, MAX, Boolean, IF, Which is Best?

Hi,
I saw EXCELISFUN TRICK 369. I need to do something similar. I tried to follow his code and couldn't. Then I tried copying it and growing his records and still couldn't get it to work.

What I am trying to do is on the first list use an inventory list. Which could be about 2000 - 5000 records or maybe double that (not sure how big of a list I could use in Excel). But lets say it is the max number (if someone could tell me that number I would be most appreciative).

I then will load a second list, or would load a list into second column. I then want the difference (what is missing) from the second list to appear in the third list. If it can give me the row of where it is in the first list that would be great (not a problem if you can't). I just don't know why the code from that video is not working any help would be greatly aprreciated?

Thanks,

Peter Fraga
(fragapete@hotmail.com)

Hi All,

I was setting up a spreadsheet that was based on the following vid:
http://www.youtube.com/user/ExcelIsF...14/tqCEY5YMyqw

Dynamic sub tables based on a master sheet array formula

The formula in question is:
=IF(ROWS(A$7:A7)>$E$1,"",INDEX('2010Corn'!A$4:A$17,SMALL(IF(Table2[From]=$B$1,ROW(Table2[From])-ROW('2010Corn'!$H$4)+1),ROWS(A$7:A7))))

B1 is the customer I'm looking for, E1 is the count for the customer and the master page is 2010Corn. I have 20 sheets looking to this master page for data. It works great, except for an issue when adding a new line in the master table.

What is happening is when I get to the end of a row, I tab to enter a new line in the table. It takes up to a minute for the cell to change color and for me to regain control of the computer.

I have run a performance trace and while the computer is locked, one of the CPU core's is pegged for the whole time with a processor que of up to 10 items at a time.

My question is...does anyone have any hints how to optimize this formula?

Thanks
Tony

Just This Video For Fun

http://www.youtube.com/watch?v=0VvjrldlqI0

Hi,

Love VLOOKUP. Has met a lot of needs I have had over the years.

Question:
What do you do when the lookup value is to the RIGHT of the lookup range? In the past, I have referenced the row via =B2, say in column Z. It works, but it can get messy.

Is there another trick out there to overcome this problem?


Thanks,
S

I am trying to figure out why one code which works in a workbook would not work in another workbook. The only difference is range locations.

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Range("a4").Value = "save" Then
    Range("a4").Value = ""
    Range("H7:AC7").Select
    Cancel = False
Else
    Cancel = True
    MsgBox "Save not allowed. Please use the save button at the bottom of the page.", vbOKOnly
End If
    Call AllProtect
End Sub

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Range("A1").Value = "Save" Then
     Range("A1").Value = "" 
    Range("filename").Select
    Cancel = False
     
Else
    Cancel = True
    MsgBox "Save not allowed. Please use save button at end of report.", vbOKOnly

End If
    Call AllProtect

End Sub

the first code works fine but the second one hangs up when it trys to clear the cell with the password in it.

The only difference is that the word "Save" in the second code is capitalized but that is only because I have a code running that has everything in proper case.

Thanks in advance for your help.

I want to save the name of the last person who saved the file by passing the name they inputted in the Private Sub Workbook_Open() to Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean).
See my code below:

Private Sub Workbook_Open()
Dim Message, Title, Default, MyValue
Message = "Please type in your name" ' Set prompt.
Title = "User Information" ' Set title.
'Default = "" 'Default
' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Builds an on going list of who saved file when.
'Code must go into the "ThisWorkbook" module!
'All data is placed in column "A" adjust width and formats.
Worksheets("Tracking").Select
With Worksheets("Tracking")
.Range("A1") = "This file last saved ""By"" who ""On"", list!"
Range("A65536").End(xlUp).Offset(1, 0) = _
"By: " & Application.UserName & ", On: " & Now & " "
'I don't want to use the Application.UserName above but the MyValue variable (user inputted name) from my Private Sub Workbook_Open()
End With
End Sub

Thanks in advance to anyone who can help me (I am not very good at this stuff yet).
Sarah

I am having trouble creating a similar spreadsheet to Excelisfun video #712. The only difference between mine and his, is that I have 3 blanks between the data sets. Can someone help?

http://www.youtube.com/user/ExcelIsF...22/Po35KC2ODNw

hi i have writtern the following in a comman button

sub trythis()
dim x as range
set x = range("b1:b20")
for each x in x
if x.text = "June" then
x.copy
end if
next x
end sub

I have in the range names of the month repeated randomly. June appears 6 times in the list

By executing the above code the last cell containing "June" gets into copy mode (the cell is filled with marchings ants effect). Although the loop goes through many cells containing "June" only the last cell containing "June" shows this effect, how extend this effect to all the cells containing the text "June".

Good morning,

trying to find out what to do with my spreadsheet and the formulas in it.

If you look into the file 369. I can get to the stage where it outlines the mismatch figure. In the example it is 6 Mismatches.

But I cannot seem to get the table belwo right. Everytime I try and copy past the formula, the first set of data is the one from the first line and then I get a #NUM error, due to the fact that there seems to be a number reoccurence somewhere.

I took the youtube tutorial, but even copying that formula did not work.

My table consist of 2 sets of 600 values each.

Can someone tell me why I am getting this error and help me resolve the issue that the result is an existing set of data?

THANK YOU

BayerStars23
[IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot.png[/IMG][IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot-1.png[/IMG]

When you are making a formula say

=A5 + A6+A7

when you are doing this in cell D423, it sometimes gets annoying to have to either type in A6 and A7 manually or to navigate to A6 and A7.

I know there is a key you can hold down after doing A5 that keeps you on A5 even after putting in the + sign, so that you don't have to navigate all the way back there.

I can't figure it out!

Does anyone know what this is?

Please help!!

Thanks,
Michael