Pages: [1] :: one page |
|
Author |
Thread Statistics | Show CCP posts - 0 post(s) |
culo duro
EVE Corporation 987654321-POP The Marmite Collective
45
|
Posted - 2013.03.18 09:57:00 -
[1] - Quote
o/
I've been looking into industry lately, and i was wondering if there's an option to update fields and then make it pass to the next column.
E.g Sheet2 is a Datasheet, where all the information is stored. Row 1 is going to drag information from Sheet2s G1. A1 is a name field. B1-H1 is going to be the part which Sheet2s G1 needs to update, to make a diagram.
What i'm asking is if it can be automated? or atleast with very little effort since i'm using about 1k different prices.
I hope it's clarified.
|
Steve Ronuken
Fuzzwork Enterprises
1222
|
Posted - 2013.03.18 10:15:00 -
[2] - Quote
If I understand what you want, I think you're looking for
vlookup()
It takes three inputs. Looks the input up in the list which is the second input, returning the column that is the third input. (a fourth input may be required if the list isn't in alphabetical order)
https://office.microsoft.com/en-gb/excel-help/vlookup-HP005209335.aspx
Steve Ronuken for CSM 8 Handy tools and SDE conversions Twitter: @fuzzysteve on Twitter |
culo duro
EVE Corporation 987654321-POP The Marmite Collective
45
|
Posted - 2013.03.18 10:39:00 -
[3] - Quote
I can't seem to get it to work though? even if i use the suggestion, care to elaborate? |
Steve Ronuken
Fuzzwork Enterprises
1222
|
Posted - 2013.03.18 12:08:00 -
[4] - Quote
You'd have a function looking something like:
=VLOOKUP(A1,'price data'!B:F,3)
on the 'price data' work sheet, you'd have data like: s181000000257.8457514818/03/201309:26:43 s1910000002018/03/201312:01:39 s2010000002254.904268518/03/201309:57:42 s211000000260018/03/201308:00:46 s22100000024537.47861418/03/201309:25:23 s34100000025.65993290318/03/201312:00:30 s351000000214.627031518/03/201311:40:08 s361000000248.1799617618/03/201311:44:42 s3710000002147.724131718/03/201311:35:40 s3810000002809.755439118/03/201311:35:22 s3910000002800.733005718/03/201311:19:27 s40100000022308.67772618/03/201311:56:18
and in the cell A1, you'd have 34
The formula would give you: 5.659932903
If you're willing to trust an excel sheet from someone you've never met: http://dl.eve-files.com/media/1303/example.zip is an example of vlookup Steve Ronuken for CSM 8 Handy tools and SDE conversions Twitter: @fuzzysteve on Twitter |
culo duro
EVE Corporation 987654321-POP The Marmite Collective
45
|
Posted - 2013.03.18 12:19:00 -
[5] - Quote
Steve Ronuken wrote:You'd have a function looking something like: =VLOOKUP(A1,'price data'!B:F,3) on the 'price data' work sheet, you'd have data like: s181000000257.8457514818/03/201309:26:43 s1910000002018/03/201312:01:39 s2010000002254.904268518/03/201309:57:42 s211000000260018/03/201308:00:46 s22100000024537.47861418/03/201309:25:23 s34100000025.65993290318/03/201312:00:30 s351000000214.627031518/03/201311:40:08 s361000000248.1799617618/03/201311:44:42 s3710000002147.724131718/03/201311:35:40 s3810000002809.755439118/03/201311:35:22 s3910000002800.733005718/03/201311:19:27 s40100000022308.67772618/03/201311:56:18 and in the cell A1, you'd have 34 The formula would give you: 5.659932903 If you're willing to trust an excel sheet from someone you've never met: http://dl.eve-files.com/media/1303/example.zip is an example of vlookup
Ah cheers, it's because i'm using the European version (2007) where it uses ; instead of ,
Is it possible to lock a field up though with it? so let's say i got 10 fields pulling their numbers from the same place with the use of data connection?
|
Steve Ronuken
Fuzzwork Enterprises
1222
|
Posted - 2013.03.18 12:27:00 -
[6] - Quote
I don't get what you mean, sorry.
Steve Ronuken for CSM 8 Handy tools and SDE conversions Twitter: @fuzzysteve on Twitter |
culo duro
EVE Corporation 987654321-POP The Marmite Collective
45
|
Posted - 2013.03.18 12:34:00 -
[7] - Quote
Steve Ronuken wrote:I don't get what you mean, sorry.
Currently i got 3 fields, A1 A2 A3, pulling information from Data!G1.
What i want to do it to create an average of that price. G1 is updating via a Data connection.
I've been looking around for a function that would lock A2 and A3 on day one. Then later that week i could lock A1 and A3. Either that or something like this: A1 Gets information, next update A2 gets updated by not A1. On the third update A3 gets updated while A1 and A2 got the same numbers they got updated with earlier. |
Steve Ronuken
Fuzzwork Enterprises
1222
|
Posted - 2013.03.18 12:42:00 -
[8] - Quote
Ahh. I think that's a little beyond what's possible, without going into scripting in the worksheet, to copy figures around.
One possibility, I guess, is to have multiple copies of the datasource, so you just update the one you want to, 'locking' the data in the others. It's not a /good/ option, but it's a little easier. Steve Ronuken for CSM 8 Handy tools and SDE conversions Twitter: @fuzzysteve on Twitter |
culo duro
EVE Corporation 987654321-POP The Marmite Collective
45
|
Posted - 2013.03.18 12:59:00 -
[9] - Quote
Steve Ronuken wrote:Ahh. I think that's a little beyond what's possible, without going into scripting in the worksheet, to copy figures around.
One possibility, I guess, is to have multiple copies of the datasource, so you just update the one you want to, 'locking' the data in the others. It's not a /good/ option, but it's a little easier.
Oh well big cheers for the help. I guess i'll see if a friend of mine can make an online database of it. |
Angelic Resolution
The Arcanum
3
|
Posted - 2013.03.18 13:59:00 -
[10] - Quote
In the cell you want, type '=' then hold down CTRL and press the sheet, in the sheet press the cell you need.
Done. |
|
culo duro
EVE Corporation 987654321-POP The Marmite Collective
46
|
Posted - 2013.03.18 16:42:00 -
[11] - Quote
Angelic Resolution wrote:In the cell you want, type '=' then hold down CTRL and press the sheet, in the sheet press the cell you need.
Done.
No i'm not going to redirect +1k Prices seperatly. |
Lucy Oreless
The Guardian Knights
128
|
Posted - 2013.03.19 07:40:00 -
[12] - Quote
culo duro wrote:Angelic Resolution wrote:In the cell you want, type '=' then hold down CTRL and press the sheet, in the sheet press the cell you need.
Done. No i'm not going to redirect +1k Prices seperatly.
Don't you have kids that crave allowance... or at least a wife? _____________________________________________________________________________________ -áI did not have sexual relations to THAT woman.... |
culo duro
EVE Corporation 987654321-POP The Marmite Collective
46
|
Posted - 2013.03.19 12:20:00 -
[13] - Quote
Lucy Oreless wrote:culo duro wrote:Angelic Resolution wrote:In the cell you want, type '=' then hold down CTRL and press the sheet, in the sheet press the cell you need.
Done. No i'm not going to redirect +1k Prices seperatly. Don't you have kids that crave allowance... or at least a wife?
i ended up abusing a buddy of mine he's going to create an app for us. |
Lucian Cosades
Noob Slaughter
1
|
Posted - 2013.03.19 14:37:00 -
[14] - Quote
I believe you have your answer, but I will offer the following:
What you want cannot be automated without the use of macros. The code you need is not complex, but if you have no experience with Visual Basic, the initial learning curve would not make it productive (unless you want to dive into and learn VB, which is valid and the best way to learn VB)
If you are just looking for an solution, and elegance be damned, what you need can be done manually with very little effort. Here is my suggestion:
- Populate Column A with your vlookup() formula.
- Somewhere in the column include a date field to record when the data was pulled. You can use the function TODAY() to return the current date.
- Whenever you update your source data, highlight Column A, copy, then paste it as a new column in front of A. However, instead of the default "paste", you want to "paste special" to paste "values".
Your values are now set in stone for that date, with the date recorded. Your "lookup" column has now shifted to Column B, and its embedded formula has updated automatically. I suggest highlighting the lookup column in yellow so that you can visually track it as it moves. Or, you could keep your lookup column as Column A, and copy/pastevalue to the right.
Hope this helps. |
Sidrat Flush
The Praxis Initiative Gentlemen's Agreement
130
|
Posted - 2013.03.19 22:25:00 -
[15] - Quote
You probably enjoy creating your own spreadsheets more than using other people's which is all good of course, but for ideas you could do worse than take a look at my work book collection, from my signature.
A public version hasn't been updated to the new Eve version though but all you have to do is update the database manually and you're good to go if you want the updated values, otherwise it's totally unlocked and you can see all the formulas when you unhide the data sheets.
Good luck have fun and above all enjoy - When you're not enjoying walk away come back later.
The new home of the Eve Industrial Organiser is here. Enjoy the first in a series, EIO:Refinery now http://www.eve-files.com/media/corp/Sidrat/ Read about it http://eveindustrialorganiser.blogspot.co.uk/?m=0 |
culo duro
EVE Corporation 987654321-POP The Marmite Collective
46
|
Posted - 2013.03.20 08:18:00 -
[16] - Quote
Sidrat Flush wrote:You probably enjoy creating your own spreadsheets more than using other people's which is all good of course, but for ideas you could do worse than take a look at my work book collection, from my signature.
A public version hasn't been updated to the new Eve version though but all you have to do is update the database manually and you're good to go if you want the updated values, otherwise it's totally unlocked and you can see all the formulas when you unhide the data sheets.
Good luck have fun and above all enjoy - When you're not enjoying walk away come back later.
The reason i dislike using other peoples spreadsheets if because they usually lock them up, and i want to know what's behind. Cheers.
I believe you have your answer, but I will offer the following:
Lucian Cosades wrote:What you want cannot be automated without the use of macros. The code you need is not complex, but if you have no experience with Visual Basic, the initial learning curve would not make it productive (unless you want to dive into and learn VB, which is valid and the best way to learn VB)
If you are just looking for an solution, and elegance be damned, what you need can be done manually with very little effort. Here is my suggestion:
Populate Column A with your vlookup() formula. Somewhere in the column include a date field to record when the data was pulled. You can use the function TODAY() to return the current date. Whenever you update your source data, highlight Column A, copy, then paste it as a new column in front of A. However, instead of the default "paste", you want to "paste special" to paste "values".
Your values are now set in stone for that date, with the date recorded. Your "lookup" column has now shifted to Column B, and its embedded formula has updated automatically. I suggest highlighting the lookup column in yellow so that you can visually track it as it moves. Or, you could keep your lookup column as Column A, and copy/pastevalue to the right.
Hope this helps.
Cheers i'll be trying it out. |
|
|
|
Pages: [1] :: one page |
First page | Previous page | Next page | Last page |