|
 |
| | | Author: | BarbaraH  | | Posted: | Aug 24, 2019 05:50 | | Subject: | Excel | | Viewed: | 154 times | | Topic: | Inventories | | Status: | Open | |
|
| | Is there a way of downloading an inventory of a specific set as an Excel file? |
|
| | | | | | |
| | | | | Author: | BarbaraH | | Posted: | Aug 24, 2019 09:20 | | Subject: | Re: Excel | | Viewed: | 46 times | | Topic: | Inventories | |
|
| 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
|
|
| | | | | | | | | |
| | | | | | | Author: | paulvdb | | Posted: | Aug 24, 2019 09:23 | | Subject: | Re: Excel | | Viewed: | 38 times | | Topic: | Inventories | |
|
| 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.
|
|
| | | | | | | | | | | | | |
| | | | | | | | | 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: | BarbaraH | | Posted: | Aug 24, 2019 10:30 | | Subject: | Re: Excel | | Viewed: | 43 times | | Topic: | Inventories | |
|
| In Inventories, SylvainLS writes:
| | 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.
|
Okay, this is obviously beyond my skill set. Anyone like to show me, preferably
via screen shots, exactly how to do this. Please.
|
|
| | | | | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | 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: | BarbaraH | | Posted: | Aug 25, 2019 10:34 | | Subject: | Re: Excel | | Viewed: | 30 times | | Topic: | Inventories | |
|
| In Inventories, SylvainLS writes:
| | 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
|
It worked fine until point 7
Then I just got error messages.
|
|
|
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | Author: | BarbaraH | | Posted: | Aug 25, 2019 10:37 | | Subject: | Re: Excel | | Viewed: | 32 times | | Topic: | Inventories | |
|
| In Inventories, BarbaraH writes:
| | In Inventories, SylvainLS writes:
| | 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
|
It worked fine until point 7
Then I just got error messages.
|
=VLOOKUP(E3;"$Page2.$A$3.$B$217";2)that's my formula
|
|
|
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | | | Author: | paulvdb | | Posted: | Aug 25, 2019 12:28 | | Subject: | Re: Excel | | Viewed: | 28 times | | Topic: | Inventories | |
|
| In Inventories, BarbaraH writes:
| | In Inventories, BarbaraH writes:
| | In Inventories, SylvainLS writes:
| | 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
|
It worked fine until point 7
Then I just got error messages.
|
=VLOOKUP(E3;"$Page2.$A$3.$B$217";2)that's my formula
|
Not sure if this works different in other language versions of Excel, but based
on how I work with formulas in my Dutch version of Excel I think the correct
version of that formula should be =VLOOKUP(E3;Page2!$A$3:$B$217;2;false)
The false at the end of the formula makes sure that it will only look for the
exact value in the list of colors. Otherwise it will use the nearest value if
your cell E3 has a value that's not in the list of colors.
|
|
|
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | | | 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: | Cob | | Posted: | Aug 24, 2019 11:58 | | Subject: | Re: Excel | | Viewed: | 28 times | | Topic: | Inventories | |
|
| In Inventories, BarbaraH writes:
| | Is there a way of downloading an inventory of a specific set as an Excel file?
|
Use BrickStock and save the file. Open the BrickStock in excel.
You will have to open as read only but can save as anything else.
|
|
|
|
|
United Kingdom, England
Netherlands, Groningen
France, Nouvelle-Aquitaine
USA, Missouri