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

msofficeforums50314.xlsx (10.7 KB, 8 views)

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.

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.

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).

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

msofficeforums50314.xlsx (13.1 KB, 4 views)

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
will probably work. And the returned range will expand automatically when Table tData expands.

No.

Yes.

Correct. All my suggestions require the results table NOT to be a proper Excel table.
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).

While awaiting HSTACK, take the 3 components and put them in adjacent cells:
=Table1[Hdr1]
=Table1[Hdr2]/2
=Table1[Hdr2]^2

Attached Files

msofficeforums50314.xlsx (10.7 KB, 6 views)

Thanks Folks, this seems to do the trick. Much appreciated!