Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-26-2023, 01:31 PM
0rion 0rion is offline Auto-populating table based on another table? Windows 10 Auto-populating table based on another table? Office 2010
Novice
Auto-populating table based on another table?
 
Join Date: Jan 2019
Posts: 11
0rion is on a distinguished road
Default Auto-populating table based on another table?

Hi, I have two tables:
Table1 has two columns, A and B, that will take data entry.
Table2 will have 3 columns, D will just pull whatever's in A, E and F will do a formula on B and report.

Easy enough...what I'd really like to do is make it so that whenever I add a new row to A and B, the subsequent row in DEF will auto-populate.



Perhaps there's a better way than using tables?
Reply With Quote
  #2  
Old 01-27-2023, 01:18 AM
ArviLaanemets ArviLaanemets is offline Auto-populating table based on another table? Windows 8 Auto-populating table based on another table? Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Do you have the Table2 on separate sheet, or on same sheet with Table1? I hope you are using Defined Tables - when formula is same for whole Defined Table column, it auto-populates whenever you add new rows.

When on same sheet, and you want to report all records of your data, then there is no reason to have 2 separate Tables. Simply add those calculated columns into same Table. In case you want to print out 1st column values and calculated columns, add a calculated column which duplicates column A too. Like
Column A;
Column B;
Column C = [@[Column A]];
Column D = Formula1;
Column E = Formula2

To get the printout from columns C:E only:
Define a dynamic Named Range, which includes those columns;
Set the Print Area for sheet equal to this Named Range.

In case you want to report some subset of records, then you need a separate Report sheet, where user can determine filter conditions, and a Report Table reads matched info from Data Entry sheet. Report Table must have enough records to return any data, and can have any number of empty rows at bottom. For printout, you define Named range which includes all non-empty records in Report Table (probably you need a calculated column there which registers records with any data), and you set Print Area for Report sheet equal to this Named Range.
Reply With Quote
  #3  
Old 01-27-2023, 05:33 AM
p45cal's Avatar
p45cal p45cal is offline Auto-populating table based on another table? Windows 10 Auto-populating table based on another table? Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Are you really still using Office 2010?
Reply With Quote
  #4  
Old 01-27-2023, 08:29 AM
0rion 0rion is offline Auto-populating table based on another table? Windows 10 Auto-populating table based on another table? Office 2021
Novice
Auto-populating table based on another table?
 
Join Date: Jan 2019
Posts: 11
0rion is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
Are you really still using Office 2010?
No - O365 enterprise. I didn't notice the header...updated.
Reply With Quote
  #5  
Old 01-27-2023, 08:39 AM
p45cal's Avatar
p45cal p45cal is offline Auto-populating table based on another table? Windows 10 Auto-populating table based on another table? Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

In that case, the likes of:
=HSTACK(Table1[Hdr1],Table1[Hdr2]/2,Table1[Hdr2]^2)
at cell D2. The result is NOT a proper Excel Table, and it won't work if you try to make it a table.



2023-01-27_153524.png


Column E is half, and column F is squared.
Attached Files
File Type: xlsx msofficeforums50314.xlsx (10.7 KB, 5 views)
Reply With Quote
  #6  
Old 01-27-2023, 12:40 PM
0rion 0rion is offline Auto-populating table based on another table? Windows 10 Auto-populating table based on another table? Office 2021
Novice
Auto-populating table based on another table?
 
Join Date: Jan 2019
Posts: 11
0rion is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
In that case, the likes of:
=HSTACK(Table1[Hdr1],Table1[Hdr2]/2,Table1[Hdr2]^2)
at cell D2. The result is NOT a proper Excel Table, and it won't work if you try to make it a table.
Amazing tool, but our Enterprise version of O365 is still 2108 and it looks like hstack came out with v2203. Thanks, this may do it.
Reply With Quote
  #7  
Old 01-27-2023, 12:42 PM
0rion 0rion is offline Auto-populating table based on another table? Windows 10 Auto-populating table based on another table? Office 2021
Novice
Auto-populating table based on another table?
 
Join Date: Jan 2019
Posts: 11
0rion is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
Do you have the Table2 on separate sheet, or on same sheet with Table1? I hope you are using Defined Tables - when formula is same for whole Defined Table column, it auto-populates whenever you add new rows.

When on same sheet, and you want to report all records of your data, then there is no reason to have 2 separate Tables. Simply add those calculated columns into same Table. In case you want to print out 1st column values and calculated columns, add a calculated column which duplicates column A too.
Same Sheet.
Yes, Defined tables.
Two separate tables: here's my reasoning - Table1 is for a barcode scanner data entry...using a defined table is nice because when you scan the data into column A and then into B, it goes to the next row-column A automatically without keyboard input. I can focus on scanning with an occasional glimpse back at the laptop to make sure nothing was missed.

I used to do this all with one table, as you suggested, by unlocking columns AB then protecting the sheet and unchecking Select Locked Cells to make data entry easier. This worked great for me but I want to share this template with my team.

In the end what I'd like is a way to scan data into the two columns without keyboard input, and be able to copy the massaged data into our database tool. I thought if I could set it up with two Tables I could avoid the need to protect and unprotect the sheet for the most user-friendliness.
Reply With Quote
  #8  
Old 01-27-2023, 01:55 PM
p45cal's Avatar
p45cal p45cal is offline Auto-populating table based on another table? Windows 10 Auto-populating table based on another table? Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by 0rion View Post
Amazing tool, but our Enterprise version of O365 is still 2108 and it looks like hstack came out with v2203. Thanks, this may do it.
While awaiting HSTACK, take the 3 components and put them in adjacent cells:
=Table1[Hdr1]
=Table1[Hdr2]/2
=Table1[Hdr2]^2
Attached Files
File Type: xlsx msofficeforums50314.xlsx (10.7 KB, 4 views)
Reply With Quote
  #9  
Old 01-27-2023, 02:22 PM
ArviLaanemets ArviLaanemets is offline Auto-populating table based on another table? Windows 8 Auto-populating table based on another table? Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by 0rion View Post
...Table1 is for a barcode scanner data entry...using a defined table is nice because when you scan the data into column A and then into B, it goes to the next row-column A automatically without keyboard input. I can focus on scanning with an occasional glimpse back at the laptop to make sure nothing was missed.
I can't test it out currently, but maybe this will work:
Have calculated columns in your Table 1, but hide them. This ensures calculations are made when data are read in, but the Table will work like with 2 columns only;
Try to use FILTER() to get wanted report data displayed separately (I'm currently only aware there is such function in latest versions, but I have never used it. You have to test yourself, is it possible to get all 3 columns of data returned without applying any conditions).
Reply With Quote
  #10  
Old 01-27-2023, 03:35 PM
p45cal's Avatar
p45cal p45cal is offline Auto-populating table based on another table? Windows 10 Auto-populating table based on another table? Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by ArviLaanemets View Post
is it possible to get all 3 columns of data returned without applying any conditions.
Yes, with the likes of:
=INDEX(Table13,ROW(Table13)-ROW(Table13[#Headers]),{1,3,4})
See Sheet1 (2) of the attached.


If you want to include headers in the result:
=INDEX(Table13[#All],ROW(Table13[#All])-ROW(Table13[#Headers])+1,{1,3,4})
Attached Files
File Type: xlsx msofficeforums50314.xlsx (13.1 KB, 1 views)
Reply With Quote
  #11  
Old 01-28-2023, 02:56 AM
ArviLaanemets ArviLaanemets is offline Auto-populating table based on another table? Windows 8 Auto-populating table based on another table? Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by p45cal View Post
Yes, with the likes of:
=INDEX(Table13,ROW(Table13)-ROW(Table13[#Headers]),{1,3,4})
See Sheet1 (2) of the attached.
I haven't Excel available currently to look at attached file.

Is this INDEX() formula entered into second Defined Table, or it behaves like those newest Excel features, where you enter the formula {like FILTER()} into single cell, and you get a range of values returned? When former, as I suspect, then the second Table will not expand automatically when new rows are added to first Table.

I think I found a probable solution with FILTER(). When the 1st Table has name e.g tData, with headers like Hdr1, Hdr2 for visible columns, and then for hidden columns headers like Hdr3 for column with formula
=tData[@Hdr1]
, Hdr4 and Gdr5 for columns for OP's calculations, and an additional column Hdr6 with formula
=1,
then formula
Code:
=FILTER(tData[Hdr3:Hdr5],tData[Hdr6]=1)
will probably work. And the returned range will expand automatically when Table tData expands.
Reply With Quote
  #12  
Old 01-28-2023, 04:12 AM
p45cal's Avatar
p45cal p45cal is offline Auto-populating table based on another table? Windows 10 Auto-populating table based on another table? Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by ArviLaanemets View Post
Is this INDEX() formula entered into second Defined Table,
No.

Quote:
Originally Posted by ArviLaanemets View Post
or it behaves like those newest Excel features, where you enter the formula {like FILTER()} into single cell, and you get a range of values returned?
Yes.

Quote:
Originally Posted by ArviLaanemets View Post
When former, as I suspect, then the second Table will not expand automatically when new rows are added to first Table.
Correct. All my suggestions require the results table NOT to be a proper Excel table.
Quote:
Originally Posted by p45cal View Post
The result is NOT a proper Excel Table, and it won't work if you try to make it a table.

Quote:
Originally Posted by ArviLaanemets View Post
I think I found a probable solution with FILTER(). When the 1st Table has name e.g tData, with headers like Hdr1, Hdr2 for visible columns, and then for hidden columns headers like Hdr3 for column with formula
=tData[@Hdr1]
, Hdr4 and Gdr5 for columns for OP's calculations, and an additional column Hdr6 with formula
=1,
then formula
Code:
=FILTER(tData[Hdr3:Hdr5],tData[Hdr6]=1)
will probably work. And the returned range will expand automatically when Table tData expands.
Yes, it will probably work. The first argument of the FILTER function is the array you're going to filter, but in this case you're 'filtering in' all rows (not filtering at all), so really, you only need the first argument.
This should work:
=tData[Hdr3:Hdr5]
but in the OP's case the columns are not adjacent; he wants columns 1, 3 & 4

I would have expected
=INDEX(Table13,,{1,3,4})
or
=INDEX(Table13,0,{1,3,4})
to be enough but experimentation showed it wasn't (only one row returned).
It wants to see the likes of:
=INDEX(Table13,{1;2;3;4;5;6…},{1,3,4})
(not a real formula) hence the extra bits there in my last message (msg#10).
Reply With Quote
  #13  
Old 02-03-2023, 09:24 AM
0rion 0rion is offline Auto-populating table based on another table? Windows 10 Auto-populating table based on another table? Office 2021
Novice
Auto-populating table based on another table?
 
Join Date: Jan 2019
Posts: 11
0rion is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
While awaiting HSTACK, take the 3 components and put them in adjacent cells:
=Table1[Hdr1]
=Table1[Hdr2]/2
=Table1[Hdr2]^2
Thanks Folks, this seems to do the trick. Much appreciated!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto-Entering Values into a Column in a Table based on Value in Adjacent Column nytvsh Excel 2 12-06-2021 12:33 AM
Auto-populating table based on another table? Populating fields from a table serrix Word 3 02-24-2016 12:19 AM
Scanning Barcodes Populating Table with Info Sueade Excel 0 11-10-2015 05:51 PM
Populating a table cell wit document properties milena Word VBA 2 04-15-2015 07:28 AM
Auto-populating table based on another table? Update Word table based on another table input mpdsal Word VBA 10 10-29-2012 07:40 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:16 PM.


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