Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 10-24-2020, 02:21 PM
Purfleet Purfleet is offline Multi Worksheets get Columns to match Windows 10 Multi Worksheets get Columns to match Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Quote:
Take Order
When you hit the Add Child Name and then hit cancel it adds a blank row instead of stopping.. Again not a big deal just did not know if it was suppose to stop and is not for some reason
Done - will now display a message and exit

Quote:
Take Order
2. I updated an equation in the Balance, is there a way to get excel to not show the O or – when the equation yields a zero. Just let the cell be blank?
Done, we just dont clear that cell now

I think the other options are going to take a bit of time and possibly make a fair bit of what we done redundant



I dont mind having a look, but maybe we should say that this is Phase 1 and is at least useable? Or are there still parts that are needed to make this useable?
Attached Files
File Type: xlsm Master popcorn Template_Lafko Update 1.1.xlsm (123.0 KB, 5 views)
Reply With Quote
  #17  
Old 10-24-2020, 02:32 PM
dlafko1 dlafko1 is offline Multi Worksheets get Columns to match Windows 10 Multi Worksheets get Columns to match Office 2016
Advanced Beginner
Multi Worksheets get Columns to match
 
Join Date: Apr 2019
Posts: 31
dlafko1 is on a distinguished road
Default

The biggest thing to make it useable would be how to delete out the data so you can start fresh. But yes deff useable other wise.

Definitely dont want to be redundant so if you want let me know what you think is just so I can scratch it off the list.

Thanks again you have take this so much further than i could have.
Reply With Quote
  #18  
Old 10-24-2020, 10:33 PM
Purfleet Purfleet is offline Multi Worksheets get Columns to match Windows 10 Multi Worksheets get Columns to match Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Sorry missed the merge question.

if you highlight 2 cells, right click > format cells then in the horizontal alignment drop down you will see 'Center Across Selection'

It looks the same as merge but doesn’t cause any of the downstream problems.

As an example on the attached if you click on column F, you high select column F. if you click on column D you select columns D & E

In my experience Merged Cells is the work of the Devil and i would avoid in all cases!
Attached Files
File Type: xlsx Merged vs Center accross selection.xlsx (9.7 KB, 5 views)
Reply With Quote
  #19  
Old 10-25-2020, 06:25 AM
dlafko1 dlafko1 is offline Multi Worksheets get Columns to match Windows 10 Multi Worksheets get Columns to match Office 2016
Advanced Beginner
Multi Worksheets get Columns to match
 
Join Date: Apr 2019
Posts: 31
dlafko1 is on a distinguished road
Default

Cool thanks that is really neat
Reply With Quote
  #20  
Old 10-25-2020, 06:37 AM
dlafko1 dlafko1 is offline Multi Worksheets get Columns to match Windows 10 Multi Worksheets get Columns to match Office 2016
Advanced Beginner
Multi Worksheets get Columns to match
 
Join Date: Apr 2019
Posts: 31
dlafko1 is on a distinguished road
Default

Ok SO when i manually delete the data and enter a scouts name it adds it right in the Take order and puts it in row 4 since they are all blank

But then in SS hours and Combined Profit it puts them up in row 2 and 3 which is the header columns?

So would deff need to be able to delete the info to start the new year.

maybe a button for deleting that works like adding?
Attached Images
File Type: jpg SS hours.JPG (45.9 KB, 8 views)
File Type: jpg Combined Profit.JPG (58.0 KB, 8 views)
Reply With Quote
  #21  
Old 10-26-2020, 01:06 AM
Purfleet Purfleet is offline Multi Worksheets get Columns to match Windows 10 Multi Worksheets get Columns to match Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Morning

Right, I think your original question kind of snowballed and caused its own problems. I thought the spreadsheet was for continuous use and wouldn’t need to be cleared and to start again, so the original method seemed to work.

However knowing that you will start a new one each year I have gone in a different direction, which is actually more simple.

There is now a temp page (as a work around to having 2 input pages (take order & SS Hours)) and a control Page. The control page I was using to store names for testing but also for the clear all button.

We still need to work on a way of delete just one child and to look at formatting.

Can you do some testing to make sure your formulas are correct and in the place you want them? I have moved the totals on Take Order to the top as well as the others.
Attached Files
File Type: xlsm Master popcorn Template_Lafko_New.xlsm (116.0 KB, 7 views)
Reply With Quote
  #22  
Old 10-26-2020, 04:15 AM
dlafko1 dlafko1 is offline Multi Worksheets get Columns to match Windows 10 Multi Worksheets get Columns to match Office 2016
Advanced Beginner
Multi Worksheets get Columns to match
 
Join Date: Apr 2019
Posts: 31
dlafko1 is on a distinguished road
Default

Ok where do I enter the names then Still on the Take Orders? Looks like. Is there not a way to keep the quantities on the bottom? does that make it too complicated?

Can I rebuild the green boxes with data thats all gone.
Reply With Quote
  #23  
Old 10-26-2020, 04:49 AM
Purfleet Purfleet is offline Multi Worksheets get Columns to match Windows 10 Multi Worksheets get Columns to match Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Sorry yes, all the names go in b & c on take order.

Putting the totals at the bottom does make it a bit harder, impossible no, just effort vs reward........

Damn, i forgot about the green boxes!!!!!!!
Reply With Quote
  #24  
Old 10-26-2020, 04:53 AM
dlafko1 dlafko1 is offline Multi Worksheets get Columns to match Windows 10 Multi Worksheets get Columns to match Office 2016
Advanced Beginner
Multi Worksheets get Columns to match
 
Join Date: Apr 2019
Posts: 31
dlafko1 is on a distinguished road
Default

OK Sounds Good we can get use to that for the totals.

I can rebuild them .. Playing with it so far it seems to be working well.
Only Issue is the Clear all data --- It clears all the formulas out of the green boxes which would need to stay.
It does not clear the data out of the Inventory Counts which it could Row 4 7 10 13 if that is more cumbersome then its work they can just manually delete it too.


two questions
1. When you hit add child it buts the border around the name any way to do the boarder along the entire row. On Take orders I had some different color boarders is that something we have to just not have or is there a way to get those colors to drop down too

2. would be just deleting a child. Working nicely so far.
Reply With Quote
  #25  
Old 10-26-2020, 04:56 AM
Purfleet Purfleet is offline Multi Worksheets get Columns to match Windows 10 Multi Worksheets get Columns to match Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Use this one so the clear all doesnt kill the green boxes! Only done it quickly so check it.

We can sort out formatting in version 2

and deleting a child is in version 2
Attached Files
File Type: xlsm Master popcorn Template_Lafko_New_v1.xlsm (117.3 KB, 5 views)
Reply With Quote
  #26  
Old 10-26-2020, 05:32 AM
dlafko1 dlafko1 is offline Multi Worksheets get Columns to match Windows 10 Multi Worksheets get Columns to match Office 2016
Advanced Beginner
Multi Worksheets get Columns to match
 
Join Date: Apr 2019
Posts: 31
dlafko1 is on a distinguished road
Default

So not sure this matters since we will never have just 1 kid but thought i would let you know.

In combined profit if you add just 1 child then it drops the row 2 down rather than the equations picture attached
Attached Images
File Type: jpg Combined.JPG (39.5 KB, 9 views)
Reply With Quote
  #27  
Old 10-26-2020, 06:21 AM
Purfleet Purfleet is offline Multi Worksheets get Columns to match Windows 10 Multi Worksheets get Columns to match Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

that happens due to the way the formulas are copied down, can put in a simple if to stop that.
Reply With Quote
  #28  
Old 10-26-2020, 06:24 AM
dlafko1 dlafko1 is offline Multi Worksheets get Columns to match Windows 10 Multi Worksheets get Columns to match Office 2016
Advanced Beginner
Multi Worksheets get Columns to match
 
Join Date: Apr 2019
Posts: 31
dlafko1 is on a distinguished road
Default

Ok no problem then Like i said we will never have one kids just wanted to double check.

Thanks again. From playing with it I think its working

Last thing really then is the report cause that is not working at all now which I assume is because its a pivot table and the data positions where changed but thats over me too

If you have a better way to make a report i am all for it.
Reply With Quote
  #29  
Old 10-26-2020, 06:29 AM
Purfleet Purfleet is offline Multi Worksheets get Columns to match Windows 10 Multi Worksheets get Columns to match Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Okay, can you do a little mock up of what you want to do see an how you want it laid out? No need for formulas etc just a visual mock up will be fine
Reply With Quote
  #30  
Old 10-26-2020, 06:46 AM
dlafko1 dlafko1 is offline Multi Worksheets get Columns to match Windows 10 Multi Worksheets get Columns to match Office 2016
Advanced Beginner
Multi Worksheets get Columns to match
 
Join Date: Apr 2019
Posts: 31
dlafko1 is on a distinguished road
Default

So something like this.
Each Scouts name with the product that they ordered underneath and the number of those items. With the total next to their name. The report would need to adjust with the data presented so if we delete someone or add someone the report would need to pick them up or take them off.

Automatically keeping each scout on one page so if Half of charest order would land on page 2 then his name and all of his order would move to page 2.

This is taken from the Take order information.

As long as it is grouped by scout name then product name,

What will happen, we will print the report and give it to the people who will pick the order that's why we can't split a scout onto 2 pages. The orders are delivered to us by the case of each product rather than by the scout who ordered it so we open all the cases and use this report to then Pick the orders and put them together for each scout.

They will then manually check the picked box and it will not be entered back into the system. We just print the report maybe it can be produced as a PDF To keep a digital copy?

Does that make sense?
Attached Images
File Type: jpg Report.JPG (43.7 KB, 8 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multi Worksheets get Columns to match Comparing between 2 worksheets and retreiving data if a match FUGMAN Excel Programming 8 02-18-2017 09:47 AM
Multi Worksheets get Columns to match Counting only the unique names in 3 columns on separate worksheets. Ryga38 Excel 1 04-15-2016 07:21 AM
Multi Worksheets get Columns to match Match Multi Cells gdavey Excel Programming 1 09-06-2015 11:09 AM
Index Match Function across different worksheets shay_mt Excel 2 04-27-2015 06:04 AM
Multi Worksheets get Columns to match Multi Level Subtotals w 2 Grand Totals That Don't Match DavidW Excel 3 10-09-2014 07:08 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:55 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft