Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-22-2020, 09:34 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 Multi Worksheets get Columns to match

I have 4 worksheets in one workbook. 1 of those sheets is where I put names.



I would like the other three sheets to match. I know I can do = and the cell info and that will pull it over.

My question is if someone adds a row to the Take Order sheet. How can i get it to add that space to all the other sheets or is the only chose to do it manually each time?

Thanks
Reply With Quote
  #2  
Old 10-22-2020, 10:28 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

Depends on if you want formulas or VBA. you could just reference the cell on sheet 1 with and if blank so something like =IF(Sheet1!A1="","",Sheet1!A1)

or you coud have vba do it as a 1 off process with

Code:
Sub UpdateNames()

Dim NameList As Range
Dim I As Integer
Dim R As Integer

    R = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

    Set NameList = Worksheets("Sheet1").Range("a1:a" & R)

    For I = 2 To 4
        NameList.Copy Worksheets("Sheet" & I).Range("a1")
    Next I

 End Sub
Or you could have VBA do it as new items are added


Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Set Target = Range("a:a")

Dim I As Integer

    For I = 2 To 4
        Worksheets("Sheet1").Range("a:a").Copy
        Worksheets("Sheet" & I).Range("a:a").PasteSpecial xlValues
    Next I

End Sub
Attached Files
File Type: xlsm Multi Worksheets get Columns to match.xlsm (24.0 KB, 6 views)

Last edited by Purfleet; 10-22-2020 at 10:29 AM. Reason: upload
Reply With Quote
  #3  
Old 10-22-2020, 11:43 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

@purleet..

Great this looks like it would work. It is not moving any other rows or columns which is exactly what i need. So I like the idea.. My workbook is a little more complex because it has text above the names so I am going to attach the workbook here.

Also the name entry would be on the tab called take orders and then fill the name on the other 3 sheets to the left.

Thanks for offering to help. I am not a great coder..
Attached Files
File Type: xlsx Show and Sell Inventory Template.xlsx (16.2 KB, 7 views)
Reply With Quote
  #4  
Old 10-22-2020, 11:46 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

A Couple of things - there is only 1 sheet on the attached and its not got anynames on it


and most importantly the merged cells in a1 & b1 have to go!
Reply With Quote
  #5  
Old 10-22-2020, 11:52 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

Sorry Wrong Workbook.

The Take order is where it gets complicated.

Need cells B4 to AA 4 to shift down when a new name is added and to move calculations with it. While keeping the rest of the columns to the right of AA4 in tact.

If you do messenger or email might be quicker LOl
Attached Files
File Type: xlsm Master popcorn Template.xlsm (113.6 KB, 9 views)
Reply With Quote
  #6  
Old 10-22-2020, 01:32 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

So we enter the name in Take order and this copies to Combined Profit - Scoutbucks & SS Hours scoutbook money? Where is the third sheet?

I dont think the live update will work in this case, so i have added a button that will ask for the first and last names then add in the row copy down formulas

This does cause a few issues
  • You are using direct referenced cells for example ='SS Hours Scoutbook Money'!I7. This will make the data wrong when we paste in the new child. I would suggest you use index & match (or vlookup) or sumifs to bring back the data on the 2 other worksheets as that will then be dynamic
  • the total at the bottom of the combined sheet will need to be factored into the code. As will the totals on the SS hours sheet.
Test this bit out and let me know what you think.


Bed now!


Messenger? is that facebook? No way!
Attached Files
File Type: xlsm Master popcorn Template(1)_Purfleet.xlsm (113.9 KB, 8 views)
Reply With Quote
  #7  
Old 10-22-2020, 02:01 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 three tabs are
1. combined Profit Scoutbucks
2. Inventory Counts
3. SS Hours- Scourtbook Money

All getting their name order from take order
Reply With Quote
  #8  
Old 10-22-2020, 09:19 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

Must be me, i cant see any names on Inventory counts
Reply With Quote
  #9  
Old 10-23-2020, 04:12 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

OMG I am so sorry.. My brain was fried last night you are right. Just the
Combined
SS
Take orders

So sorry thanks for being patient.
Reply With Quote
  #10  
Old 10-23-2020, 04:19 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

No problems, the good news is that means that part might be done. so you need to test


then we can crack on with the totals issue and the lookups
Reply With Quote
  #11  
Old 10-23-2020, 05:43 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

Perfect I missed the button originally LOL.

1. ON Take Order.
a. We would need to shift all rows down on the entire sheet when we add a child so the Money Collected and Far Right side shift as well So basically all the way over to AJ column would need to shift down
b. Could we have a button then to remove a row of if you do the traditional delete row it will take it from all the worksheets?
2. SS Hours Would need to shift all the columns down from A-J to keep it all equal.
3. Combined Profit.. Need to shift rows from A to H

This worked pretty neat. I love buttons they make it easier for the end user.
Reply With Quote
  #12  
Old 10-23-2020, 02:13 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

Its getting more complex now.....


on the SS Hours sheet you have various numbers against date and location - where is this data pulled from?
Reply With Quote
  #13  
Old 10-23-2020, 02:50 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

That info is added as we determine where we will sell the popcorn. We set up a table and stand in front of a bussines and changes year to year. IN therory we can teach someone to use insert for the column but if there is a way to do something like you did for the roster with abutton thats cool but dont want to push my luck.

Big thing is shifting the rows down with out effecting the other data so shift A-J down and leave the rest alone

then shift columns right to add Date and Location.

We would enter 10-23-2020 Lowes hamburg for example
Reply With Quote
  #14  
Old 10-24-2020, 02: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

I did spend last night making the totals move down but after thinking about it this morning it is a lot of effort for little pay back when we can just move the totals to the top.

Combined profit sheet
  • Removed any merged cells
  • Updated the Prize level lookup to something a bit easier to read
  • Added in an extra column (c) to find the correct child from the take order sheet (doing this in one column makes the worksheet much faster)
  • Then added in an index and match to columns D, E and G so that the numbers are dynamic as the child name changes
  • Lastly, I have moved the sums to the top of the work sheet and extended the range so they will not need moving down each time – you could put these over on the right if you preferred.
SS Hours sheet
  • Removed any merged cells
  • I have moved the sums to the top of the work sheet and extended the range so they will not need moving down each time – you could put these over on the right if you preferred.
I had to update the childrens names on the SS hours sheet in a different way as this is basically an input sheet, but i think it works as it should.

It does take 5 seconds or so to complete but we can look at that if it works okay

Not looked at deleting yet (phase 2)

Have a look through and test it thoroughly, its it your workbook, so if there is anything i have done that you dont like or cant work with i will not be offended.

The one thing i do have to insist on is NO merged cells - they are the worse thing ever in a worksheet and will only cause problems. if you must have words spreadover cells use 'Centre accross selection' instead

Last edited by Purfleet; 10-24-2020 at 02:29 AM. Reason: typo
Reply With Quote
  #15  
Old 10-24-2020, 06:30 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

This looks and works great
One question what to you mean by a Centre Across Selection? Never heard of that.

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
Combined
I don’t mind the totals at the top. I have them just to verify the other info any way.

SS Hours Sheet
I don’t mind the totals at the top.

Take Order
1. Looks good. The time lag is more for me than you, but not a problem as long as I put that in the instructions. Might be a little annoying when adding kids at the start of the year.
a. Question with this. Can we add a lot of names at once like a copy and
paste from a roster? With out using the button and not messing anything
up? Or is it better to enter each kid one at a time.
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?

NEXT THING--- if this is again asking too much I understand so you have been awesome with helping so. Deleting a record or deleting all data to start the new year is critical but the rest is just again trying to simplify for the end user. If we can simply just select all the white spaces and delete that is fine too.

1. To delete an individual record if needed
2. To delete all the data to make it ready for a new year. So this data is for 2020 and when we do this in 2021 we would need to start from scratch.
a. TAKE ORDER
i. We would delete everything from with the exception of Rows 2 and 3
ii. We would also keep row 46-48
iii. If we would just delete rows then does the button work to start over or
since it is locked in to add from row 32 you could not delete all the rows?
b. SS Hours
i. We would delete all the data entry since the names come from Take order,
they would already be deleted
c. Inventory Counts
i. We would delete data where date and business name is
ii. Zero out the Start and End inventory
3. Inventory Counts
a. As we add product (row 1 and 2) Add Events with dates and Times is there
a way to get that data to auto populate in the SS hours then?
i. May be too complicated vs just doing a manual insert just trying to
make things uber user friendly. A lot of our parents don’t use excel
or even have a computer.
b. Then with in SS Hours the green box adjusts as well?
4. The final thing then would be the report for take orders. This is for picking the product. So when it is delivered it is delivered in cases 1 case of Carmel 1 case of cheddar and so on. So the report would be used to hand out to people on paper so when they are picking an order its easy to read. The big issue with the one we have is it does not refresh well and you have to manipulate the page breaks to get one order on a page if it ends up being split.

Thanks again. Again if some stuff is complicating it rather than just being simple with the manual version I am fine with that too just let me know.
Attached Files
File Type: xlsm Master popcorn Template_Lafko Update 1.xlsm (124.9 KB, 7 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 07:13 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