|
Why am I asking it here? Because I am making something for my players to help build characters sheets using it. I am posting it here because I know a lot of you are Excel savvy. I've searched the web and have found no answer. The problem is I don't know how to really ask the question. So here I go..
Vlookup issues
I have a drag down box. You select an item. I want to following columns to fill in pulling data from another worksheet. The following columns have repetitive information (costs), so it will see the second column "Cost" it will take the first matching number then fill in from there taking the first match. How do I make it so every following column takes its information from the unique identifier (Armor) in column 1?
Is Vlookup up the right formula? Should I be using something else? I know about hero lab but that is way too complex for beginners. Please help :).
|
|
Which version of Excel are you using? I am not sure I understand exactly what you are trying to do.
My first advice is to take your data columns (where you are sending the vlookup to search), and put them on your main page and then hide the columns if you like. Put it way to the right and the players will likely never even know it is there unless they look for it. This will give you shorter formulas and make trouble shooting easier.
|
Short answer: stop. Don't build it yourself. Perfectly good Excel based character management sheets exist. Here's a free one. Here's a better (IMHO) purchasable one, The Only Sheet.
I am not entirely sure what task you are trying to perform here, but personally I would just write a macro / piece of VBA for this. Cell manipulation is a snap to do - you can read, write and do pretty much anything you care to do. Automating excel is one of its most powerful features. If you can explain exactly what it is you need, or better yet give an actual spreadsheet example... I might be able to help.
I am also doing this for fun, as I am trying to improve my Excel skills. I just put it up on good docs. here is the link... go to [url=https://docs.google.com/leaf?id=0B9WikFyF-9SFNDMxNGM4Y2QtNjNmMS00ODg4LWIxYzEtYzE1ZjE1YTQ3Zjk1&hl=en[/url].
I get a...
Sorry, the page (or document) you have requested is not available.
Please check the address and try again.
Something to get you started, some simple vba - I have quickly written some code around the area you are looking at ( and tested it ! )
Public Sub grabcell()
Dim wb As Workbook
Dim ws As Worksheet
Dim o1 As Object
' 1 based arrays
Set wb = Workbooks(1)
Set ws = wb.Sheets(1)
Set o1 = ws.Cells(1, 1)
MsgBox "Value is " & CStr(o1), vbInformation, "Info"
Set o1 = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub
Public Sub copycell()
Dim wb As Workbook
Dim ws As Worksheet
Dim o1 As Object
' 1 based arrays
Set wb = Workbooks(1)
Set ws = wb.Sheets(1)
Set o1 = ws.Cells(1, 1)
ws.Cells(1, 3) = o1
MsgBox "Value copied !", vbInformation, "Info"
Set o1 = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub
Public Sub checkcells()
Dim wb As Workbook
Dim ws As Worksheet
Dim o1 As Object
Dim i as Integer
' 1 based arrays
Set wb = Workbooks(1)
Set ws = wb.Sheets(1)
For i = 1 To 20
If ws.Cells(i, 1) <> "Bazinga" Then
ws.Cells(i, 2) = ws.Cells(i, 1)
Else
ws.Cells(i, 2) = "Boppity Bop"
End If
Next
Set o1 = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub
Hopefully what they do should be self evident, and provide examples of interrogating cell values, copying etc. grabcell reads a value from a cell and pops a messagebox up. copycell will copy a cell value from one cell to another. and checkcells will copy the first 20 rows into column 2, unless the value is Bazinga, in which case it will put the value Boppity Bop into the cell.
You can mix this up by copying across worksheets - ( you would need a second ws reference, IE, dim ws2 as Worksheet, set ws2=wb.Sheets(2) )
Technically speaking you can use a shorter form for VBA and direct referencing of sheets and workbooks - its weakly typed and has collections ready to access, but the code above is a better way to do it.
To get into this you need to get to the Visual Basic Editor from the Macros menu. As for how to run them, depends on the app, you could just add some command buttons from the Control Toolbox toolbar and place a call to them in the click event.
Feel free to ask about that hasty blitz of information.