| 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 
|
|
Next Page: 5 More | 10 More | 25 More | 50 More | 100 More
|
|
France, Nouvelle-Aquitaine
