Displaying a Warning
estella endromed
Posted messages
48
Status
Member
-
Frenchie83 Posted messages 2254 Status Member -
Frenchie83 Posted messages 2254 Status Member -
Hi,
I am currently working with a database of equipment in Excel. My problem is that once the equipment is positioned in the cabinet (that's done), if I want to enter a new piece of equipment in my database and I assign it a position that's already occupied, a warning appears stating that the position is taken or there is an overlap of positions.
In the file I attached, there is only one cabinet to simplify the example, but in reality, there are several cabinets sometimes not in the same room.
Here is the link:
https://www.cjoint.com/c/CEjlRdx8oai
Thank you in advance for your help.
I am currently working with a database of equipment in Excel. My problem is that once the equipment is positioned in the cabinet (that's done), if I want to enter a new piece of equipment in my database and I assign it a position that's already occupied, a warning appears stating that the position is taken or there is an overlap of positions.
In the file I attached, there is only one cabinet to simplify the example, but in reality, there are several cabinets sometimes not in the same room.
Here is the link:
https://www.cjoint.com/c/CEjlRdx8oai
Thank you in advance for your help.
18 answers
Hello via55,
It's a very good idea, is there a way to display the message or a warning triangle on one of the cells of the DISPLAY tab (I don't know if it's possible to import an image of an error sign)?
However, my question is that I don't know the future size of the equipment, the first idea that comes to mind is to put 42 columns, that's a lot... Do you have another suggestion?
Best regards
It's a very good idea, is there a way to display the message or a warning triangle on one of the cells of the DISPLAY tab (I don't know if it's possible to import an image of an error sign)?
However, my question is that I don't know the future size of the equipment, the first idea that comes to mind is to put 42 columns, that's a lot... Do you have another suggestion?
Best regards
Re,
Possibility to display ERROR with a VLOOKUP and a conditional formatting
https://www.cjoint.com/?3EjtiGWbVv3
don't bother with an image that will need to be managed by a macro or something else!
Why 42 columns?
Can a single piece of equipment have a size of 42?
If so, you need 42 columns; I don't see how to do otherwise, but it's not a problem (just modify the search range in the formula for column J)
Best regards
Possibility to display ERROR with a VLOOKUP and a conditional formatting
https://www.cjoint.com/?3EjtiGWbVv3
don't bother with an image that will need to be managed by a macro or something else!
Why 42 columns?
Can a single piece of equipment have a size of 42?
If so, you need 42 columns; I don't see how to do otherwise, but it's not a problem (just modify the search range in the formula for column J)
Best regards
arf I currently only have LibreOffice and when I save in XL format sometimes it works and sometimes it doesn't!
So in View in H3 I put the formula:
IF(ISNA(VLOOKUP(E3;'Database'!$E$4:$F$12;2;0));" ";VLOOKUP(E3;'Database'!$E$4:$F$12;2;0))
and drag down
ERROR appears next to the concerned number
then with conditional formatting you can color it red for the cell where there is ERROR
So in View in H3 I put the formula:
IF(ISNA(VLOOKUP(E3;'Database'!$E$4:$F$12;2;0));" ";VLOOKUP(E3;'Database'!$E$4:$F$12;2;0))
and drag down
ERROR appears next to the concerned number
then with conditional formatting you can color it red for the cell where there is ERROR
The file I sent you contains the first formula which leaves #N/A that I was removing with a white font on a white background using conditional formatting.
The second, longer formula that I included in my last message bypasses the issue by putting a space (" ") instead of N/A, so there's no need to hide it with conditional formatting.
It's up to you to choose which one you prefer.
The second, longer formula that I included in my last message bypasses the issue by putting a space (" ") instead of N/A, so there's no need to hide it with conditional formatting.
It's up to you to choose which one you prefer.
Hello
I have been interested in your problem from the start, but solving it solely with formulas seemed complicated, so I did it with a macro.
Here is the link: https://www.cjoint.com/c/CElhUqmjzqJ
I think this answers your questions; however, the program only responds if you enter a value in the position column, so that is the cell to fill in last. Similarly, for an already existing position, if you wanted to change the number of equipment, you need to re-enter the position even if it hasn't changed places.
Best regards.
I have been interested in your problem from the start, but solving it solely with formulas seemed complicated, so I did it with a macro.
Here is the link: https://www.cjoint.com/c/CElhUqmjzqJ
I think this answers your questions; however, the program only responds if you enter a value in the position column, so that is the cell to fill in last. Similarly, for an already existing position, if you wanted to change the number of equipment, you need to re-enter the position even if it hasn't changed places.
Best regards.
Hello Frenchie83,
Thank you for your lovely proposal! That said, what bothers me is that I cannot apply sorting as I need to maintain the chronology of the equipment, meaning I need to keep the order in which they are inventoried.
Moreover, this is a database that should contain more than 1000 pieces of equipment :(
Is there a solution to all this?
Best regards
Thank you for your lovely proposal! That said, what bothers me is that I cannot apply sorting as I need to maintain the chronology of the equipment, meaning I need to keep the order in which they are inventoried.
Moreover, this is a database that should contain more than 1000 pieces of equipment :(
Is there a solution to all this?
Best regards
Hello
An idea https://www.cjoint.com/?3EllIqZbsHP
If you created an address it would be easier
example: create a column F with this formula
=IF(S42="";"";R42&S42&T42)
The quotation mark is used for the locations with 2 lines in this case there is no address
Cabinet 1 equipment 2 position 13 gives the address 1213
and if you are in cabinet 2 it gives 2213 so there is no risk of error
For controlling occupied locations create a column G with this formula
=IF(COUNTIF($F$4:$F$12;$F4)>1;"Duplicate";"") and conditional formatting cell = duplicate fill red bold font and yellow
An idea https://www.cjoint.com/?3EllIqZbsHP
If you created an address it would be easier
example: create a column F with this formula
=IF(S42="";"";R42&S42&T42)
The quotation mark is used for the locations with 2 lines in this case there is no address
Cabinet 1 equipment 2 position 13 gives the address 1213
and if you are in cabinet 2 it gives 2213 so there is no risk of error
For controlling occupied locations create a column G with this formula
=IF(COUNTIF($F$4:$F$12;$F4)>1;"Duplicate";"") and conditional formatting cell = duplicate fill red bold font and yellow
It's a possibility, but I see constraints in applying this:
# the size of the cabinets is not fixed; it can be 42, 37, 57, or others...
# moreover, there are several sizes of equipment: 1, 2, .....15..
# the number of cabinets can be counted in dozens and can even reach 50, so I find it difficult to prepare a list of 50 cabinets with their addresses (of course, if I understood your file correctly).
Best regards
# the size of the cabinets is not fixed; it can be 42, 37, 57, or others...
# moreover, there are several sizes of equipment: 1, 2, .....15..
# the number of cabinets can be counted in dozens and can even reach 50, so I find it difficult to prepare a list of 50 cabinets with their addresses (of course, if I understood your file correctly).
Best regards
It's the foundation of the system, and there are no constraints on the number of cabinets; we can add as many as we want, and for the number of shelves, we insert lines and add shelves, which isn't a problem either.
50 CABINETS WITH 50 SHELVES ONLY AMOUNTS TO 2500 LINES
that's not huge for a database.
50 CABINETS WITH 50 SHELVES ONLY AMOUNTS TO 2500 LINES
that's not huge for a database.
Hello
Being absent for the day, I just saw your post tonight. I have therefore made the modification regarding the respect for the chronological order.
Additionally, since the capacity of the cabinets varies, I added a small table on the right that lists the name of each cabinet with their respective sizes; you just need to input the correct values.
https://www.cjoint.com/c/CEludGkMcAC
Do some tests, create new equipment, modify positions, and observe the reactions, and let me know if it works as you desire.
Best regards
Being absent for the day, I just saw your post tonight. I have therefore made the modification regarding the respect for the chronological order.
Additionally, since the capacity of the cabinets varies, I added a small table on the right that lists the name of each cabinet with their respective sizes; you just need to input the correct values.
https://www.cjoint.com/c/CEludGkMcAC
Do some tests, create new equipment, modify positions, and observe the reactions, and let me know if it works as you desire.
Best regards
Hi Frenchie83,
When I add a new cabinet, a message appears telling me that the cabinet's capacity is not recorded.
Thank you in advance.
When I add a new cabinet, a message appears telling me that the cabinet's capacity is not recorded.
Thank you in advance.
Good evening
It is necessary that it be listed in the right table; I had given you some examples, but you can add as many as you want, as well as the possible number of equipment.
It is necessary that it be listed in the right table; I had given you some examples, but you can add as many as you want, as well as the possible number of equipment.
small reminders
- column A is automatically filled by the program, but the value can be entered manually (always a numeric value)
- column F is automatically filled by the program, but the value can be entered manually; the entry must start and end with a space, in order to distinguish between 2 different values containing the same digit, for example 4 and 14
- in the table on the right, column J, the name of each locker must be identical to the one listed in column C (the best approach is to copy and paste)
- there are no limitations on the number of lockers or the number of equipment
- column A is automatically filled by the program, but the value can be entered manually (always a numeric value)
- column F is automatically filled by the program, but the value can be entered manually; the entry must start and end with a space, in order to distinguish between 2 different values containing the same digit, for example 4 and 14
- in the table on the right, column J, the name of each locker must be identical to the one listed in column C (the best approach is to copy and paste)
- there are no limitations on the number of lockers or the number of equipment
Hello Frenchie83,
I am in the process of adapting your file, for example, if I have another tab where I can extract the size of the cabinets, is it possible to do that?
# the size is written as follows in a third tab that I need to add:
exp: AR 60H 50L 100P H:height; L:width; P:depth
AR 42H 50L 100P ......
In this case, how can I modify your program to import the cabinet size from the third tab? Because I want to keep a single table in the 'database' tab.
# Does it pose a problem if I insert other columns (other information about equipment such as its weight...) between columns C and D?
# Column A is not really necessary for me; if I remove it (which part should I take out of the macro?), would that be a problem? Because I'm fine with just filling in the equipment one after the other.
# Which part of the macro pertains to the display of the smiley? Is there a way to display a yellow triangle with an exclamation point? This point is really optional; the little smiley is perfectly fine for me, just for my own knowledge!
I am in the process of adapting your file, for example, if I have another tab where I can extract the size of the cabinets, is it possible to do that?
# the size is written as follows in a third tab that I need to add:
exp: AR 60H 50L 100P H:height; L:width; P:depth
AR 42H 50L 100P ......
In this case, how can I modify your program to import the cabinet size from the third tab? Because I want to keep a single table in the 'database' tab.
# Does it pose a problem if I insert other columns (other information about equipment such as its weight...) between columns C and D?
# Column A is not really necessary for me; if I remove it (which part should I take out of the macro?), would that be a problem? Because I'm fine with just filling in the equipment one after the other.
# Which part of the macro pertains to the display of the smiley? Is there a way to display a yellow triangle with an exclamation point? This point is really optional; the little smiley is perfectly fine for me, just for my own knowledge!
Hello
It's a pity you didn't say earlier that you were going to add more columns, but we can do everything, though it will require modifying some lines of code since we will need to shift the columns. If you feel up to it, try to do it; otherwise, just hang on a bit.
Column A is essential to put the file back in chronological order.
The display of the smiley is nothing more than the character "L" in the Wingdings font. I couldn't find an equivalent for the triangle with the exclamation point (only the triangle by itself). I'm attaching a file with the equivalent of each character in other fonts; maybe you'll find what you’re looking for?
https://www.cjoint.com/c/CEmlDfUvXrT
If you could send me a representation of the final table structure, it would be easier for me.
See you soon
It's a pity you didn't say earlier that you were going to add more columns, but we can do everything, though it will require modifying some lines of code since we will need to shift the columns. If you feel up to it, try to do it; otherwise, just hang on a bit.
Column A is essential to put the file back in chronological order.
The display of the smiley is nothing more than the character "L" in the Wingdings font. I couldn't find an equivalent for the triangle with the exclamation point (only the triangle by itself). I'm attaching a file with the equivalent of each character in other fonts; maybe you'll find what you’re looking for?
https://www.cjoint.com/c/CEmlDfUvXrT
If you could send me a representation of the final table structure, it would be easier for me.
See you soon
Hello PHILOU10120,
First of all, I would like to thank you immensely for taking the time for my issue, it's really great work.
That said, I'm a bit limited by the way of doing things, since I've already imported the equipment following a previous post resolved by via55, so all that’s left for me is to display an error message; unfortunately, I can't adapt your method to my file.
That said, I will keep your method safely, I know it will come in handy sooner or later!
Have a great day.
First of all, I would like to thank you immensely for taking the time for my issue, it's really great work.
That said, I'm a bit limited by the way of doing things, since I've already imported the equipment following a previous post resolved by via55, so all that’s left for me is to display an error message; unfortunately, I can't adapt your method to my file.
That said, I will keep your method safely, I know it will come in handy sooner or later!
Have a great day.
Hello again
I anticipated a bit while waiting for a response to my previous post
I inserted 5 columns between C and D, 4 of which are for weight and height, and 1 other is available
https://www.cjoint.com/c/CEmqKmSJau1
The weight and height are to be entered in the table on the right for each cabinet, and they are automatically retrieved from the table by a formula.
Is this in line with what you were expecting?
If it's not right, feel free to let me know, and I will adjust it accordingly
Best regards
I anticipated a bit while waiting for a response to my previous post
I inserted 5 columns between C and D, 4 of which are for weight and height, and 1 other is available
https://www.cjoint.com/c/CEmqKmSJau1
The weight and height are to be entered in the table on the right for each cabinet, and they are automatically retrieved from the table by a formula.
Is this in line with what you were expecting?
If it's not right, feel free to let me know, and I will adjust it accordingly
Best regards
Hello
I created a 3rd sheet with the tab "TailleDesArmoires", where I put the coordinates of each cabinet.
On the table, I shaded the columns that are filled automatically.
I added 2 arrow buttons at the top of the column to sort by cabinet and another one to reorder chronologically (this might be useful).
I attached 2 files, there is a difference in how the cabinet dimensions are presented (if neither works for you, I can modify it), which in one case frees up 1 column and in the other 3 columns, and which can be used for data not yet planned.
Important: If you don't need these columns, don't hide them, just reduce the column width to 0.01, otherwise the offsets won't be correct.
Here are the 2 files
https://www.cjoint.com/c/CEnsrFt0hST
https://www.cjoint.com/c/CEnssgasNe8
That's it, I think that's about everything.
I believe there will surely be more improvements to make.
Best regards
I created a 3rd sheet with the tab "TailleDesArmoires", where I put the coordinates of each cabinet.
On the table, I shaded the columns that are filled automatically.
I added 2 arrow buttons at the top of the column to sort by cabinet and another one to reorder chronologically (this might be useful).
I attached 2 files, there is a difference in how the cabinet dimensions are presented (if neither works for you, I can modify it), which in one case frees up 1 column and in the other 3 columns, and which can be used for data not yet planned.
Important: If you don't need these columns, don't hide them, just reduce the column width to 0.01, otherwise the offsets won't be correct.
Here are the 2 files
https://www.cjoint.com/c/CEnsrFt0hST
https://www.cjoint.com/c/CEnssgasNe8
That's it, I think that's about everything.
I believe there will surely be more improvements to make.
Best regards
Hello
Here is a first draft, I haven't had much time to go through everything in your file,
Questions:
Is the column placement fixed?
Why do you repeat the columns "chrono order, positions occupied, warning..." in the size sheet?
I probably have a lot more questions, but I’m short on time, I have to go to work,
Take a look at the file, and I'll revisit it this evening when I get back
https://www.cjoint.com/c/CEoe6dIWZMY
Have a good day
Here is a first draft, I haven't had much time to go through everything in your file,
Questions:
Is the column placement fixed?
Why do you repeat the columns "chrono order, positions occupied, warning..." in the size sheet?
I probably have a lot more questions, but I’m short on time, I have to go to work,
Take a look at the file, and I'll revisit it this evening when I get back
https://www.cjoint.com/c/CEoe6dIWZMY
Have a good day