Redisplay Messages: Compact | Brief | All | Full Show Messages: All | Without Replies| Author: | SylvainLS  | | Posted: | Aug 25, 2019 12:29 | | Subject: | Re: Excel | | Viewed: | 34 times | | Topic: | Inventories | |
|
| In Inventories, BarbaraH writes:
| | […]
| | It worked fine until point 7
Then I just got error messages.
|
=VLOOKUP(E3;"$Page2.$A$3.$B$217";2)that's my formula
|
Ah, okay, my fault: no quotes around the region.
And to get the correct values for the region, try this:
— start editing the cell by deleting the region,
— do not validate, just let the text cursor where the region will be,
— go to the page with the colour and select the area (the two columns with colours
IDs and names), the formula should now have the correct region,
— validate.
If that cell now has the correct value, then you can edit the formula again to
add the $ so you can use it in the other cells.
|
 |
| Author: | SylvainLS | | Posted: | Aug 24, 2019 12:27 | | Subject: | Re: Excel | | Viewed: | 41 times | | Topic: | Inventories | |
|
| In Inventories, BarbaraH writes:
| | […]
| | Colours are only numbers though. So you’ll need to import the colour table (same
form) to cross the tables.
|
Okay, this is obviously beyond my skill set. Anyone like to show me, preferably
via screen shots, exactly how to do this. Please.
|
For the first part:
1. Download the set inventory as Tab-delimited (should give you a “S-NNN-1.txt”
file).
2. Open Excel, open the file from Excel (or drag and drop it), it should give
you an import dialog where you can tweek things (delimiters, how numbers are
read, locale…) but you shouldn’t have to change anything because the tab-delimited
file is pretty simple/default.
You should now have the inventory in a table.
If you want to get colour names instead (or rather, besides) their code, well,
my Excel is a bit rusty (I use LibreOffice now) but this should do it:
1. Download the colour table as Tab-delimited (should give you a “colors.txt”
file).
2. Open it from Excel like above.
Note that it should be possible to cross/match from an external file but I’m
not sure how, so we’ll do it in one file, the one we have the inventory in.
3. Select and copy the first two columns (others are not needed).
4. Go back to the inventory file.
5. Create a new page (it’s cleaner) and paste the colours names and IDs.
6. Add a column in the inventory for the colour names.
7. Use the VLOOKUP function for the first item: “=VLOOKUP( E3 ; region ; 2 )”.
“E3” should be the cell with the colour code for the first item (line 3). “region”
is the whole region with the colours in the second page (e.g. “$Page2.A3.B217”).
“2” is because we want the value in the 2nd column, the colour name.
8. Add a $ before every value in the region (e.g. if it was “$Page2.A3.B217”,
make it “$Page2.$A$3.$B$217”). That will block the values (they won’t change
when the formula is moved or copy-pasted or dragged.
9. Apply the formula all the way down, for all the items by dragging the bottom-right
handle.
Now all the items have the colour code and names.
Hope that’s clear and helping 
|
|
| Author: | SylvainLS | | Posted: | Aug 24, 2019 10:18 | | Subject: | Re: Excel | | Viewed: | 41 times | | Topic: | Inventories | |
|
| In Inventories, paulvdb writes:
| | In Inventories, BarbaraH writes:
| | In Inventories, BarbaraH writes:
| | Is there a way of downloading an inventory of a specific set as an Excel file?
|
The two file options in the download tab are not Excel
|
You can open the tab-delimited text file in Excel and save it as an Excel file.
|
Actually, you could also import the XML version but the tab-delimited file contains
the items current names 
Colours are only numbers though. So you’ll need to import the colour table (same
form) to cross the tables.
|
|
| Author: | SylvainLS | | Posted: | Aug 21, 2019 16:56 | | Subject: | Re: Cardboard sleeves in inventories | | Viewed: | 53 times | | Topic: | Inventories | |
|
| In Inventories, yorbrick writes:
| | […]
It is probably a stupid question, but does BL have a test server where such changes
can be tried out without affecting how regular users use the site or allowing
regular users to snoop or interrupt the testing of ideas?
It seems crazy that any ideas that get tried out (that do affect regular users)
need to be made on the client facing server.
|
(Snide: BL doesn’t even have _one_ working website. )
There was a second website where users (mainly sellers) were encouraged to go
when BL2 was to come (the new skin, the new main page, the new WLs, etc.). It
had an older version of the database, with no impact on the actual database.
Some people went there, made bug reports and remarks, and then the update was
put in production as it was and the people who didn’t go to the second site were
angry because they hadn’t known and those who went were angry too because they
weren’t heard.
And then everybody was adverse to any change. Go figure.
Anyway, the “sleeves” changes are on the catalogue, the database, not the website,
its skin and features. So that would mean the catmins would have changed a copy
of the database, then told us “eh, go see what we did,” and then we would have
had a discussion, and maybe a few back and forth, and in the end, they would
have to do all the final changes again on the real database because they don’t
have a direct access to the database and can’t write scripts or do partial saves
and partial updates and the actual database would have changed in the meanwhile
too.
|
|
| Author: | SylvainLS | | Posted: | Aug 11, 2019 09:02 | | Subject: | Re: 4460 a/b: wrong height | | Viewed: | 34 times | | Topic: | Catalog | |
|
| In Catalog, JusTiCe8 writes:
| | Thank you both for the heads up.
Still, parts have "Size: 2 x 1 x 3 in studs"
This is the issue, when many units are used, they have to be explicitly written
to avoid such confusion, as history tells us, help page on BL are not enough.
I suggest to add a note on each single page of inventory for parts (done
by front "code" obviously, default design for parts for instance) about the units
used. Kind of studs from width/length, brick height for height.
|
Or that the size be formatted as “2 x 1 x 3 (studs x studs x bricks)” or, better,
“2 studs x 1 stud x 3 bricks.”
And these suggestions have been made almost every time a remark such as yours
was made but nothing changed 
|
|
| Author: | SylvainLS | | Posted: | Aug 10, 2019 17:55 | | Subject: | Re: Inventory Change Request for Set 6386-1 | | Viewed: | 26 times | | Topic: | Inventories Requests | |
|
| In Inventories Requests, ziddi writes:
| | Please make changes to the following inventory:
* Add 1 Part 3022 Black Plate 2 x 2
* Add 1 Part 3176 Black Plate, Modified 3 x 2 with Hole
Comments from Submitter:
Steps 10 and 13 of the helicopter, pages 3 and 4
|
Déjà dans l’inventaire, en Alternate.
Il y a une note dans l’inventaire:
«
Redesigned during production.
There are at least 2 different versions of instructions for this set. The differences
for the second version are indicated in this inventory in the Alternate Items
section with Match ID 99.
Quick overview of redesign: The attachment of the rotor assembly on the helicopter
was upgraded for increased stability of the rotor and tail section.
»
|
|
| Author: | SylvainLS | | Posted: | Aug 1, 2019 12:16 | | Subject: | Re: 928 wrong plate? | | Viewed: | 58 times | | Topic: | Catalog | |
|
| In Catalog, legoman77 writes:
| | […]
It is hard to believe that Lego makes
mistakes, most of us think that they are gods on Mt Olympus. They also contribute
to problems and often in the distant past put whatever they had into sets. The
old set 800 (I think that that is the number) called for a garage and they substituted
something totally different. They had ran out of the correct parts.
|
Considering Greek gods are far from paragons of perfection, subject to their
whims, inconsistent, unreasonable, and uncaring of us, mortals, I’d say “most
of us” are on to something here 
|
|
| Author: | SylvainLS | | Posted: | Jul 31, 2019 13:16 | | Subject: | Re: How to compare parts lists with my inventory | | Viewed: | 28 times | | Topic: | Inventories | |
|
| In Inventories, VOTB writes:
| | If you put all the items you want into a "want list" you can visit your own store
and see what parts you have that are on that want list.
At the top of the page under your "Shop," "Terms," and "Feedback" tabs you will
see "Wanted List." If you click on that you can cycle through your want lists
and see what you have.
|
And you can part out a set in a WL here: https://www.bricklink.com/v2/wanted/partout.page
|
|
| Author: | SylvainLS | | Posted: | Jul 26, 2019 11:13 | | Subject: | Re: Either a bonafide brain-fart, or a seizure... | | Viewed: | 37 times | | Topic: | Suggestions | |
|
| In Suggestions, Teup writes:
| | In Suggestions, SylvainLS writes:
| | In Suggestions, cycbuild writes:
| | In Suggestions, Teup writes:
| | In Suggestions, cycbuild writes:
| | Sylvain has spent a lot of time familiarizing himself with BrickLink, and is
a frequent contributor here. There's no need to be so relentlessly dismissive.
|
I agree. But in all fairness we can't ignore the question in the back of
our minds: When is this guy finally going to buy some more LEGO? 
|
|
My wallet has the reverse question
Though getting around 700€ of LEGO for around 15€ thanks to coupons and destocking
made us both quite happy a couple months ago
|
Wow!  Then my question changes... Why is this guy not selling any LEGO?
|
What, with all the problems you sellers continuously whine about on the forum?
Besides, MY precious, MINE!
|
|
| Author: | SylvainLS | | Posted: | Jul 26, 2019 10:00 | | Subject: | Re: Either a bonafide brain-fart, or a seizure... | | Viewed: | 45 times | | Topic: | Suggestions | |
|
| In Suggestions, cycbuild writes:
| | In Suggestions, Teup writes:
| | In Suggestions, cycbuild writes:
| | Sylvain has spent a lot of time familiarizing himself with BrickLink, and is
a frequent contributor here. There's no need to be so relentlessly dismissive.
|
I agree. But in all fairness we can't ignore the question in the back of
our minds: When is this guy finally going to buy some more LEGO?
|
|
My wallet has the reverse question
Though getting around 700€ of LEGO for around 15€ thanks to coupons and destocking
made us both quite happy a couple months ago
| | ::Gallic shrug::
|
Indeed
|
Next Page: 5 More | 10 More | 25 More | 50 More | 100 More
|
France, Nouvelle-Aquitaine
