#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 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 Last edited by Purfleet; 10-22-2020 at 10:29 AM. Reason: upload |
#3
|
|||
|
|||
@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.. |
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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
Bed now! Messenger? is that facebook? No way! |
#7
|
|||
|
|||
The three tabs are
1. combined Profit Scoutbucks 2. Inventory Counts 3. SS Hours- Scourtbook Money All getting their name order from take order |
#8
|
|||
|
|||
Must be me, i cant see any names on Inventory counts
|
#9
|
|||
|
|||
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. |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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. |
#12
|
|||
|
|||
Its getting more complex now.....
on the SS Hours sheet you have various numbers against date and location - where is this data pulled from? |
#13
|
|||
|
|||
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 |
#14
|
|||
|
|||
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
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 |
#15
|
|||
|
|||
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. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Comparing between 2 worksheets and retreiving data if a match | FUGMAN | Excel Programming | 8 | 02-18-2017 09:47 AM |
Counting only the unique names in 3 columns on separate worksheets. | Ryga38 | Excel | 1 | 04-15-2016 07:21 AM |
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 Level Subtotals w 2 Grand Totals That Don't Match | DavidW | Excel | 3 | 10-09-2014 07:08 AM |