Forum Discussion
Help with Formula/VBA
Hello Everyone!
I am trying to get my inventory tracking sheet to do something very specific. Now I am very new and I've been learning on my own as I go. I have 3 spreadsheets, all labeled as different locations (CNC Room, Boiler, Container). Right now I have them formulated by Concatenation (which includes the location name) so that when I receive product in my receive sheet, I click on the proper concatenation and that product gets registered into the correct location sheet. My issue is, sometimes we move these products around and what was originally received in - lets say CNC Room, now gets moved to Container.
So now onto what I would like to happen. I've made a data consolidation list of the names of the 3 locations. In each of those location spreadsheets, I would like to utilize this list, so if I have product in the "CNC Room" spreadsheet and I need to move it to the "Container" Spreadsheet, I can drop down the data list, switch it from CNC to Container and that whole row of info moves from the CNC spreadsheet to the container. I also would like it to delete that row from the CNC Room when it moves it so it doesn't leave blanks and I can use that Row again. Again, I need to have this happen on all 3 of the spreadsheets.
Is there a way to do this with Formulas or VBA?
I've attached my Excel spreadsheet if that helps at all.
Thank you!
6 Replies
- After I've examined the attached file, I'm unable to understand you need,,, could you please RELOAD the updated file,,, create one new sheet & show me what U are trying to pull from others Sheets.
Krista1228 Allow me to get to this from another angle. You have used PowerQuery to combine the three location sheets into a consolidated inventory sheet. Then you have a separate sheet for "Received" and created, more or less, a copy of the consolidated sheet to which you add information on received and sold quantities to arrive at the closing stock.
Why not start by creating a table that holds all product specific data (species, core, grain, width and size) and give each product a unique code.
Then, collect all your transactional data (i.e. product code, qty, transaction type [e.g. received, sold, return, transfer], location, date, etc.) in one single table. For instance, a transfer would be entered as a minus for the "from-location" and a plus for the "to-location", with the type "Transfer". In the end, all transfers should add up to zero.Now you can create current stock listings from all sorts of angles, using pivot tables. By location, by product, by size, by species. Whatever. It's totally dynamic and without the need to concatenate a mock-up product names containing all that information information. No need for VBA code that cuts rows of data from one sheet to paste it into another. Easier to maintain, less prone to error and giving you full detail and history of how product has moved through the organisation over time.Riny_van_Eekelen I was thinking about it this AM and came to a very similar thought. Basically make a master list table-that if needed I can add to as we're constantly getting different stock in because its very custom work. I need to have the 3 locations broken up and visualized because I'm not the only person looking at this and most the guys that use it are not Excel knowledgeable or tech savvy. I can easily vlookup or query the product and input it into separate sheet labeled with that location, based on location (which would be my data validation list in location column). Essentially I'd be working backwards from what I currently have done. I would be pulling from a master list and inputting it into the location sheets instead of compiling all data from sheets into one.
This way when something moves- in the master list I can drop it down from lets say CNC Room to Container and when I update the queries etc it would automatically update.
I hope that made sense haha.
The concatenation was something I was asked to do by our Financial Advisor as this list will eventually go into quickbooks. We use specific "terms" in quickbooks for each product, usually starting with thickness, size, species, core, then grain/grade. This would make it easier to implement it into quickbooks since the terms are already being used there.
Lastly, I had the received on a separate sheet as well because its easier to have a table on another sheet vlookup the information from received and add it in, vs adding it in manually by looking for it down the very long list.
As I said, I am very new and have been learning by myself as I go. These tables have come a long way since what they looked like a couple months ago (haha). I keep striving to do better and make them more easy to use as I learn more.
I thank you very much for your time and getting back to me 🙂
Krista1228 ,,
Yes you can use Excel function with VBA,, a few I would like to show that how to use.
Dim Minvalue as long
Minvalue = Application.WorksheetFunction.Min(Range("A1:A10").Value)As soon you type Application and put DOT a list will appear need to pick the appropriate reserve word then DOT, the list of Excel Functions appears ,, pick one.
Another is,,
Range("A1").Formula = "=SUM(B1:B20)"If you want to use Array (CSE) formula then use this :
Range("A1:A10").FormulaArray= "=Index($C$1:$C$10, Match(1,($D$1:$D$10=G1)*($E$1:$E$10=H1),0),1)"
Thank you for replying!
I cant seem to get any of these to work when I put them in VBA and try to run as a Macro. Nothing happens. It does not show up in my macro list. When I put in the VBA Code
Dim Minvalue As Long
Minvalue = Application.WorksheetFunction.Min(Range("A1:A10").Value)
I get "Compile error: Invalid outside procedure"
Krista1228 ,,
I was expecting that you can handle it ,, since are the SAMPLE code and need to use as SUBROUTINE,,,, using VB editor.
Anyways check the attached file,,, and read the instruction before U proceed.
- Please remember,,, the VBA code I've shown you are just SAMPLE code,, helps that how to use Excel formula with VBA code,,, you need to construct formula as needed.
