Windows 10 excel 2007 forumula

[ragnarok1968]

From cell A2 through A660, I'm trying to catalog some football cards.

my problem: to prevent myself from typing duplicate names, can I do a formula so that say Former Detroit lions player Jerry Ball, doesn't get duplicate names under player name?

So is there a formula so as to prevent me from making multiple entries of players?

I'm manually entering each players name. So while I'm entering a mistaken duplicate name, it can ask me

"add one card to this player already cataloged? yes/No but it seems like a do while.. loop

or something.

my thought is: highlight cells A2 through 660, since there are 660 cards for an entire set. THEN, input the code to interrogate all the names already input to determine if I am trying to enter a player in the spreadsheet anyway.

any help would be MASSIVELY appreciated! thank you

-Chris
"the grateful moron.."

 

[ragnarok1968]

@BIGBEARJEDI can you see if you can get the word out on this? I'm also trying to create an active sort filter so that after every entry, it automatcially alphabetizes the players according to team.

with this post above, I want to prevent Duplicate name entries which would be "text" . maybe have it add a card to the inventory under that players name. this might be involved but I know it is possible. I used to know Excel really well from my collage days.

-Chris

 

[BIGBEARJEDI]

Hi Chris,
Sure, I'll ask around. I think it's possible on both items. The sort filter should be pretty simple; have you tried Googling it or going to an Excel education site?; there are several of them out there on the web. You could use a macro to do the active sort filter; it's been years for me, but I taught Excel for 20 yrs. but not so much any more as I'm not forced to teach it to make a living, so I've moved on to other projects like the W10-Linux dual-boot thing. Take a look at the advanced section in the Home and Learn Excel tutorial on macros and scripting. That should give you some examples for the Sort Filter. See here: Free Excel 2007 to 2016 Course - Contents Page

There are also sites that provide you with free macro templates to do things like auto-sorting filters, and data entry. You could do pivot tables for the data entry duplicate checking--I was never that great at those; they are quite advanced. Personally, I would scrap the whole Excel thing, and switch to Microsoft ACCESS instead. Excel is what we call a flat-file database, and even simple things like your duplicate-name checking and automatic sorts are quite difficult to figure out. Access on the other hand is a 4D database now, and is based on much better database technology such as the old DBASE database and Oracle (top of the line!) databases. Home and Learn used to have free Access tutorials on their free website but seem to have removed them. They are a bookseller, and therefore they put up what's most popular at the time. You could go to their site and order their Access books which I think they still sell and order one of those. You could also go look on Amazon for the "Dummies" series, and I think they have one on setting up a database with Access and using it. Something to think about. Access is used by TONS of corporations (heavily used in the Fortune500) for managing things like Inventories and Asset Management. I helped build an Access database for Taco Bell to keep track of all their computers back in the 90s, and that included their POS computers in 2,600 restaurants. It's the right tool for the job on big jobs, as well as little jobs.

[EDIT: I almost forgot to mention that my repair business I use daily in written in Access & I have over 400 customers in that; I used it for billing, searching, data-entry, form auto-fill, lots of stuff. It's not for everyone; but it sure makes my life easier.]

Hope that gives you a starting point.
BBJ

 

[Neemobeer]

Under the data tab should be 'Data Validation' you can use this.
I'm using Column A as my example
Click on the top cell and go to Data > Data Validation
Make sure the Ignore blank and Apply these changes check boxes are checked this will apply the validation to all cells in my example A1-A20 the ,A1 will automatically reflect the current cell too.

Then set your error settings

 

[ragnarok1968]

Thanks BBJ and Neemobeer. I found this code and when I enter the new card, it alphabetizes

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("D:D")) Is Nothing Then
Range("D2").Sort Key1:=Range("D2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub

The "D"'s in the code I had to add myself or change because it goes by team. So I found a code snippet to save the file or workbook

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("D:D")) Is Nothing Then
Range("D2").Sort Key1:=Range("D2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
ActiveWorkbook.Save
End If
End Sub

So now, what I have going on is, I enter a new card, it not only alphabetizes the card AFTER entry, it saved after each entry

-Chris

 

[ragnarok1968]

@BIGBEARJEDI , This will be a checklist for my son. He's at school while I'm doing this now. When completed, I'll print this spreadsheet and place it in the case so when he's older. he'll know how many of each player he'll have.

this includes the specialty cards. This is basically a gold trim card around the edge of the card and you are guaranteed 4 per case. So I got him a Joe Montana, Jerry Rice, Jerry Ball and a couple others. I'll account for those too.

but I'll print a physical copy to place in the case for inventory purposes. I have 10,000+ cards to enter. I HAD considered creating an access DB but I'm not good at that development anymore. but I could feasibly create an Access DB later and open the spreadsheet as APPEND to do my magic.

Thanks, BigBearJedi!

-Chris

 

[ragnarok1968]

@BIGBEARJEDI Do you think you can create an ACCESS DB? I'm using my ACCESS 2007. So if you Cand save it in that format.

I'm not into the "latest and greatest" I just bought this off Amazon for $46 bucks. It serves its purpose.

I'll put a notice in the coding with your name on it for credit. I can send you the most current Excel spreadsheet. We could/should be able to use the DB to input the newest input and ultimately into the spreadsheet.

thanks brother.

-Chris

 

[bochane]

Access can import Excel tables. The functions / selections you are looking for are not so difficult to create in Access

 

[ragnarok1968]

@bochane I'm not at all versed with Access. But I know what you're saying as I realize that there's an underlying VisualBasic for Applications (VBA) within Microsoft office. Are you versed with Access? I'm using MS Office 2007 Professional with small business contacts. I'm particular to the 2007 interface.. that's just me.

But Have tried to create a DB in access but it was a broken mess.

 

[ragnarok1968]

ok, I was able to use the wizard functions to create a Database based off of my Excel spreadsheet. I created a form so I can add, delete and maneuver among the records (First, Last, Next and Previous) now I have some maneuverability. for some strange reason, Access managed to insert complete records that are blank from my Excel spreadsheet. I guess I need to add a case of

Do until EOF ( or end of Records?)

its been forever since I did Visual Basic coding.

 

[bochane]

It is VBA, Visual Basics for Applications (like Access and Excel)
I normally find code examples on internet, Google is your friend.
Here are some links I found helpful
Visual Basic
Access Tutorial

 

[ragnarok1968]

It is VBA, Visual Basics for Applications (like Access and Excel)
I normally find code examples on internet, Google is your friend.
Here are some links I found helpful
Visual Basic
Access Tutorial

Thank you. I appreciate that.. (tips hat)

 

[ragnarok1968]

@bochane , I'm just trying to create a simple database for creating a repository of data on my sons case (10,080 cards minus some damaged) of his 1991 Pacific plus cards

 

[ragnarok1968]

@bochane , Here's a snapshot of the Excel spreadsheet: So see it's pretty vanilla

 

[bochane]

It just is too much to explain Access here, but I think you need to create a database with two tables Teams and Players. With this you can easily design queries with the functionality you need. The data is usually represented in forms, the data of forms / reports come from the query. Every Access application starts from a switch board. There are tools to create all of these.
You may be able to import your excel sheet in Access and use than one of the tools of Access to optimize it into a database with the two desired tables.

Another good starting point is to buying a manual i.e. Instant Access Databases of Greg Buczek. It comes with a CD with lots of (VBA code) examples. I think you can find cheap copies in 2nd hand bookshops for the version of Access you have (that is what I did).

Hope it helps

 

[ragnarok1968]

@bochane thank you. will check it out.