 Notice. New forum software under development. It's going to miss a few functions and look a bit ugly for a while, but I'm working on it full time now as the old forum was too unstable. Couple days, all good. If you notice any issues, please contact me. |
Forum Index : Microcontroller and PC projects : Little spreadsheet help please....
| Author | Message | ||||
Grogster Admin Group  Joined: 31/12/2012 Location: New ZealandPosts: 9066 |
Hello.  I have made a simple spreadsheet to keep track of all the bits I am waiting on to arrive, due to the massive delay in getting stuff cos of the virus. (with the exception of DHL or FedEx, naturally.) Here is a screenshot:  This is fine, and it works, but I can only make the simplest of simple spreadsheets(cos I have not learnt how to do anything but simple stuff), so what I really want to be able to do, is delete rows as things arrive, and have column A auto-update the item number - otherwise if I receive item #17 for example, I then have to manually re-number all the numbers on the rows below. I have about 50 things I am waiting for, for various jobs at the moment, so the spreadsheet should help me keep track of that, without having to re-logon to all my suppliers to remind myself where I was getting part X from, and how many I ordered. I expect there are a few spreadsheet gurus here on the forums, so could one of you please tell me how I make the sequential numbers in column-A auto-update, if I delete any one row? Thanks for any help. Smoke makes things work. When the smoke gets out, it stops! |
||||
| astroboy Newbie  Joined: 28/12/2014 Location: AustraliaPosts: 38 |
Hi Grogs How about just using the numbering of the rows (on the left of the spreadsheet) as your item number and delete your item# column? Then when you delete a row the numbers will rearrange. Of course you'd have to delete your headings at the top of the sheet. Any use? Regards John |
||||
TassyJim Guru  Joined: 07/08/2011 Location: AustraliaPosts: 5913 |
I am far from an expert but this is what I would do. Add a column for date received. That is handy to have anyway. For the first Item# column, use a formula that increases the count only if the date received cell is empty =IF(G6="",A6+1,A6) Then use a data filter to only show rows that have the received cell empty. Or show rows that has the cell filled if you want to check delivery dates. I usually go to Google with my query. Jim VK7JH MMedit  MMBasic Help |
||||
bigmik Guru Joined: 20/06/2011 Location: AustraliaPosts: 2870 |
Hi Grogster, The VERY FAR left where the ROWS are numbered RIGHT CLICK on the number you want to delete and Excel brings up the menu and select DELETE The area to click is GREEN in this picture.  As to auto increment the Item#, just set the first cell as 1 then the one underneath (assuming cell is A2) is =A2+1 then the one under that as =A3+1 etc etc.. When a ROW is deleted the formula will auto update if you happen to delete the first row then just change the cell to 1 and the formulas keep going down A simple way to copy the formula is to do the first formula in the second ROW (first row having the number 1 and see the tiny green box on the bottom right of Cell A3 in this picture click and hold whilst dragging down and the formula will auto complete till you let go  Kind Regards Mick Mick's uMite Stuff can be found >>> HERE (Kindly hosted by Dontronics) <<< |
||||
| Grogster Admin Group Joined: 31/12/2012 Location: New ZealandPosts: 9066 |
Hey chums. Thanks for the suggestions. @ astroboy: No, not really, as the rows don't align with the actual items, due to my adding a header and column-titles plus a couple of spacer rows. @ TassyJim: Thanks - I will have a play with that formula and see if that works.  @ Mick: Does not want to co-operate.   ....then if I delete row 38(item 34):  So, no luck there - the auto-update totally loses its reference to the first cell.  Thanks anyway. Smoke makes things work. When the smoke gets out, it stops! |
||||
| bigmik Guru Joined: 20/06/2011 Location: AustraliaPosts: 2870 |
Hi Grogs, Are you using Microsoft Excel or some other spreadsheet? Are you able to post or email me your spreadsheet or snippet so I can try on my excel? Mik Mick's uMite Stuff can be found >>> HERE (Kindly hosted by Dontronics) <<< |
||||
| bigmik Guru Joined: 20/06/2011 Location: AustraliaPosts: 2870 |
Hi Grogs, Ok I just tested it you are right, I have used excel extensively and I can’t say I have ever seen this behaviour before.. I will think on it Regards, Mick Mick's uMite Stuff can be found >>> HERE (Kindly hosted by Dontronics) <<< |
||||
| MikoKisai Newbie Joined: 10/07/2020 Location: GermanyPosts: 6 |
You should be able to use the ROW function to query the cell coordinates - if you don't specify a reference it will use the cell containing the formula. In your case it would be something like "=ROW()-4". |
||||
| vk2sja Newbie Joined: 07/06/2013 Location: AustraliaPosts: 26 |
Grogs, Try formula =ROW()-4 -4 in this example is just an offset to align number '1' with row 5 as per your spreadsheet screen-shot above. Drag formula all the way down. I think this does what your after? @ Mick: Grogs is using LibreOffice Calc rather than Excel. Cheers Steve. |
||||
| bigmik Guru Joined: 20/06/2011 Location: AustraliaPosts: 2870 |
Hi Grogster, The best I can come up with is when you get your #REF errors to redo the cells formula by clicking on the little green box bottom right of the last good cell formula and dragging down again to recreate the formulae for the cells that got affected.. At least it works and is quick and easy Regards, Mick Mick's uMite Stuff can be found >>> HERE (Kindly hosted by Dontronics) <<< |
||||
| lizby Guru Joined: 17/05/2016 Location: United StatesPosts: 3017 |
Grogster--if you click on the number 33 in the example above, Ctrl-C, click on the first "#VALUE!", Ctrl-V, all the numbers will come right. I face this all the time in my budget projection spreadsheet, and it's the solution I've come to accept. But I'd want to keep the rows of arrived-items so I could check later that I actually had ordered and received them. I have a cell (or notation on a text line), indicating where I put them (Box 0-22, Tub 1-7, desk drawer, etc.). Then for my text file, I can run a bat program, "o 340" to get a listing of orders like this: + 200401 5 pcs ESP8285 WiFi CH340 ESP-1/M1/M2/M3 + 200304 2PCS USB to ESP-01 Adapter, ESP8266 Wireless WiFi Module Wi-Fi CH340G, UART PROG amz box 17 + 200302 4 pcs USB to Serial USB to TTL FT232 CH340G Adapter Gold px programmer porch (+ means for me that I received it; "o.bat" contains this: egrep -i %1 \stuff\orders.txt) Or "o aliex" will give me all my aliexpress orders: 200705 aliex nunchuk, Wii classic controller for CMM2 200625 3PCS robotdyn ac dimmer aliex 200425 Orange Pi Zero/Zero NAS H2 512MB WiFi SBC case, expansion aliex + 200408 8 pcs 1 Channel 5V 30A Relay Board Module Optocoupler Isolation High/Low Trigger aliex 200306 5 pcs Mini ESP-M3 From ESP8285 Serial Wireless WiFi Transmission Module aliex 3/30 + 200217 6 pcs NCR 18650 protected batteries aliex paypal $18.83 + 200124 10Pcs FQP27P06 P-Channel Logic Level Mosfet P-MOS, 60V, 27A, 0.070Ohm aliex . . . or "o mosfet" 200705 10PCS IRF9540 P-Channel Power mosfet 23A IP box 0 + 200124 10Pcs FQP27P06 P-Channel Logic Level Mosfet P-MOS, 60V, 27A, 0.070Ohm aliex 200121 10PCS NDP6020P NDP6020 FSC MOSFET P-CH 20V 24A 70Ohm + 200118 5pcs MOSFET Button IRF520 Driver Module + 200113 10 pcs IRLZ44N power mosfet logic level n-channel 0.022Ohm FZ + 181229 Resistor array 4.7K bussed 5- 7- 9-pin digikey; p-ch dmp2305u-7 mosfet box 0 TassyJim's suggestion of a filter for an excel table sounds good. ~ Edited 2020-07-14 22:08 by lizby PicoMite, Armmite F4, SensorKits, MMBasic Hardware, Games, etc. on fruitoftheshed |
||||
| Paul_L Guru Joined: 03/03/2016 Location: United StatesPosts: 769 |
Part of the problem is that Excel and the various look-alikes don't quit play by the same rules. Mick's screenshot seems to be an Excel spreadsheet. Grogster's seems to be the free libre-office-calc. Everybody started with visicalc and then changed some things. Libre-office-calc will not reliably test for an empty cell with [if A1=""]! You have to use [if isblank(A1)] instead where isblank(cell) returns 1 if the cell is empty. Excel will reliably copy a formula downward if you pull on the handle (the little green square). To copy a formula downward in libre-office-calc you 1. highlight the cell 2. copy it with Ctrl+C 3. highlight the block of cells below the reference cell 4. paste the formula with Ctrl+V Using a spreadsheet as a database is a very bad idea! You should be using a flat file database manager (DBMS) like dBase, FoxBase ... et. al., with a programming language. Access just won't do the job. MMbasic contains all the required primitives to be able to become a good flat file DBMS except for a work around to emulate the MS-Basic FIELD command. Would anyone care to volunteer to help me write a real DBMS using MMbasic????? I'm too old to take on such a project by myself. Paul in NY |
||||
| Andrew_G Guru Joined: 18/10/2016 Location: AustraliaPosts: 842 |
Hi Grogs, Not a very clever solution but it works for me: - I'd add another column (say "Status"). As items arrive, put say a "9" in Status - then sort on Status - the "9" items are at the bottom - then select any two Item# entries above the 9s (eg 15 & 16) and drag the handle down - this renumbers all entries from the selected ones to as far as you drag. If you don't want to alter your original Item#s then create another column and operate on that one. No its not automatic but it works - or have I missed something? Andrew |
||||
| Grogster Admin Group Joined: 31/12/2012 Location: New ZealandPosts: 9066 |
Wow - lots of replies with lots of things to try. Thanks, everyone. Specifically though @ PaulL: "Using a spreadsheet as a database is a very bad idea! You should be using a flat file database manager (DBMS) like dBase, FoxBase ... et. al., with a programming language. Access just won't do the job." I hear that!  But I just wanted something quick, easy, and without having to learn a database application. Spreadsheets are NOT a good way to do database stuff, I totally agree, BUT they DO work for simple stuff like this, even if this method is somewhat frowned apon by those who really do know databases through and through. With the demand for the CMM2 being through the roof, I have absolutely zero spare time to learn a database!   Your idea of doing one for the CMM2 is a superb idea, and I can help with testing etc, but even I have had zero time to actually write any code for the CMM2, as I am flat-out(very busy) just filling orders at the moment. Smoke makes things work. When the smoke gets out, it stops! |
||||
| Grogster Admin Group Joined: 31/12/2012 Location: New ZealandPosts: 9066 |
@ MikoKisai: Danke! Yes, that works beautifully! I can delete the dummy line, and the item column is updated correctly. Thanks a bunch. @ vk2sja: Same as above comment. MikoKisai posted the forumula before your post, so he gets the credit for that, BUT you said to just drag down to copy the forumula to all the other cells, and that did work. Great. It's doing what I wanted it to do now, so I consider this problem solved. Many thanks to all who posted on the thread, and perhaps this time next year, we will have a brilliant database application for the CMM2 with the help of PaulL and others. Just for the record, if we DID have such an application, I would most definitely use it on the CMM2 as a dedicated database for parts and products inventory etc, and I would set one up permanently somewhere in my workshop/lab/spare-room. Smoke makes things work. When the smoke gets out, it stops! |
||||
 Print this page |
