Začetnik |
Tema: Excel? |
665 odgovora
|
|
|
#1 objavljeno 1 Lip 2011, 22:21:57
|
Citat
|
Hey guys this is my first season here and have been collecting data with paper and pen. I was wondering if anyone had a guideline on how to setup a spreadsheet? Any help would be much appreciated.
|
|
|
|
#2 objavljeno 1 Lip 2011, 22:24:16
|
Citat
|
You can search the net for macros and how to set them up.
|
|
|
|
#3 objavljeno 1 Lip 2011, 22:24:21
|
Citat
|
why dont you use gpro organizer instead?
|
|
|
|
#4 objavljeno 1 Lip 2011, 22:35:20
|
Citat
|
|
|
|
#5 objavljeno 1 Lip 2011, 22:39:53
|
Citat
|
Mike, best recommendation if you can afford it, would be being a supporter. Option number 2 would be using Go tool, and in complement to Go using the Excel spreadsheet. Honestly using Excel is not the hard, and if you use Go, would help you a lot understanding how to best use Excel. Basically you write the formulas that you need.
|
|
|
|
#6 objavljeno 1 Lip 2011, 23:04:16
|
Citat
|
Even if you use Go, it would be prudent to also save your data on spreadsheet. People using Go for the first time sometimes loose all their data. At least you would have a back up. Also it would be prudent to backup your Go database.
|
|
|
|
#7 objavljeno 2 Lip 2011, 19:29:12
|
Citat
|
Ok thanks guys, is there anyway to save my data straight from GO to a spreadsheet or is it still manual?
|
|
|
On most of the GO screens it will let you right-click the column heading & export as csv file, which you can open with excel & just save as .xls
|
|
|
|
#9 objavljeno 25 Lis 2011, 19:40:13
|
Citat
|
Irrelevant to above but not worth starting a new topic for:
Can anyone help me out here?
I've got a drop down list of products (eg: Apples), which works fine but I want a couple of cells of data referring to each product (eg: Cost to buy, selling price etc...) to pop up underneath when I select the certain product.
I have no idea how to implement this and have googled how to do it but I don't really know what I am searching for!
Cheers :)
|
|
|
|
#10 objavljeno 25 Lis 2011, 19:41:43
|
Citat
|
VLOOKUP is your best bet probably.
the value from the drop down will be your key field, and the additional fields will be picked up from a lookup table.
The help is quite good for this (I have had to refer back to it a few times for stuff) but give me a shout if you need any more help :)
|
|
|
Try using a Vlookup formula in the cell you want the price to appear. So lets say you have your list in cell A1, then in another cell you can say lookup whatever is in A1 in a data array & give me the price.
Search for Vlookup on the help & go through the example, it will make more sense :)
*EDIT*
too slow. oh well I'm glad great minds think alike (???) lol
|
|
|
Got some silly equation which doesn't make sense or work - not sure whether to use VLOOKUP, HLOOKUP or INDEX? None seem to have worked
To put it differently (if this changes anything):
Fruit: Pear ----> This is where the drop down list is which multiple fruits
Price: Price of pear eg: £0.50 (taken from another cell) ---> Pops up when pear is chosen
This would then change to Bananas if you chose bananas in the drop down and the price would then change to how ever much the price of bananas was (again in another cell)
Thanks for your help - this is probably relatively obvious but I seem to have just made a mess of it!
|
|
|
If you have a table with the fruits in one column and prices in a second column you should use VLOOKUP.
Lets say that the cell with the fruit you want is in Cell A1, your fruits are in cells C3 to C7 and the prices in cells D3 to D7.
Your formula should be...
=VLOOKUP(A1,C3:D7,2,FALSE)
the FALSE bit means it will look for an exact match
|
|
|
|
#14 objavljeno 25 Lis 2011, 20:10:38
|
Citat
|
Sound - will trial and error to see how it goes...
Thanks again... :)
|
|
|
Quote ( Patrick Mason @ October 25th 2011,20:05:29 )
Got some silly equation which doesn't make sense or work - not sure whether to use VLOOKUP, HLOOKUP or INDEX? None seem to have worked
V and Hlookup work the exact same way.
| |A |B |C |1 |Fruit |Price |Apple |2 |Apples |£1.00 |=Vlookup(C1,A1:B5,2,False) *in this case it'd return £1.00* |3 |Pears |£0.81 | |4 |Peach |£0.92 | |5 |Grapes |£1.03 |
=Vlookup(CELL,ARRAY,COLUMN,EXACT) Or in this case (assuming your dropdown is Cell "C1")
=Vlookup(C1,A1:B5,2,False)
This will give you the left-most column and search for an exact match (which is "false" when coding) and it'll return with the matching Row in Column 2 (or Column B)
Hope that helps.
|
|
|
|
|
#16 objavljeno 25 Lis 2011, 20:21:47
|
Citat
|
Pictures make it a lot clearer :)
Thanks everyone for the help!
|
|
|
To be slightly pedantic Jono, VLOOKUP actually uses "Not exact match" as a variable, not "exact match". So if you use false you're actually looking for an exact match, not the other way around.
So looking for "Apple" when set to "false" in your example would not find "Apples".
|
|
|
|
#18 objavljeno 25 Lis 2011, 22:12:58
|
Citat
|
Quote ( Sion Francis @ October 25th 2011,22:08:07 )
To be slightly pedantic Jono, VLOOKUP actually uses "Not exact match" as a variable, not "exact match". So if you use false you're actually looking for an exact match, not the other way around.
So looking for "Apple" when set to "false" in your example would not find "Apples".
And this is why you're in Edifice ;(
|
|
|
|
#19 objavljeno 25 Lis 2011, 22:15:18
|
Citat
|
pfff...vlookup is as fancy a thing as I can do with excel...I can't begin to fathom half the wizardry you get up to with it :)
|
|
|
|
#20 objavljeno 25 Lis 2011, 22:18:06
|
Citat
|
Another quick one - if I don't have anything in a cell which is used in an equation, is there any way I can make it blank when it says: #N/A etc...?
|
|
|
|
#21 objavljeno 25 Lis 2011, 22:18:42
|
Citat
|
All self taught. I reckon if someone was eager enough with it. they could easily go beyond what i can do.
|
|
|
|
#22 objavljeno 25 Lis 2011, 22:20:37 (zadnji uredio25 Lis 2011, 22:22:30 od Daryl Gee)
|
Citat
|
Quote ( Patrick Mason @ October 25th 2011,22:18:06 )
Another quick one - if I don't have anything in a cell which is used in an equation, is there any way I can make it blank when it says: #N/A etc...? If I understand right and it's the equation that is giving you the #NA's, you can use ISNA:
=IF(ISNA(equation),"",equation)
|
|
|
|
|
#23 objavljeno 1 Pro 2011, 16:21:20
|
Citat
|
Does anybody have some sort of Microsoft Excel spreadsheet that works as a 'setup tool' ?
I know this is normally the sort of tool that becomes available as part of a team, but as I am currently without a team, wondered if anybody fancied sending me one? :)
|
|
|
|
#24 objavljeno 1 Pro 2011, 16:23:03
|
Citat
|
|
|
|
#25 objavljeno 1 Pro 2011, 16:23:31
|
Citat
|
|
|
|
#26 objavljeno 1 Pro 2011, 16:31:14
|
Citat
|
Wait until Mr Shadow dons his Santa hat, then ask him :)
Loving the brazen approach though
|
|
|
|
#27 objavljeno 1 Pro 2011, 16:32:05
|
Citat
|
|
|
|
#28 objavljeno 1 Pro 2011, 16:34:42
|
Citat
|
Quote ( Dan Rice @ December 1st 2011,16:21:20 )
I know this is normally the sort of tool that becomes available as part of a team, but as I am currently without a team, wondered if anybody fancied sending me one? :)
Well, he'd be a 'tool' if he did...
|
|
|
|
#29 objavljeno 1 Pro 2011, 16:38:18
|
Citat
|
Quote ( Mike Baston @ December 1st 2011,16:31:14 )
Loving the brazen approach though
You don't ask, you don't get :)
|
|
|
|
#30 objavljeno 1 Pro 2011, 16:38:33
|
Citat
|
|