Creating column formulas to copy all rows within a column from one sheet to another

bstormer

HI --

I feel like there's an easy answer for this that I'm just not finding, so apologies if this is a repeated question. I have several sheets used to capture client intake data and am hoping to create a version of the sheet with only the de-identified columns for our evaluators to easily access for analysis. I'm hoping to set them up as column formulas so that when anything is added or changed in the original sheet, it is reflected in the secondary sheet. So, basically, the original sheet has:

Family ID First Name Last Name Address City State Zip Income Household Size

And my new sheet has:

Family ID City State Zip Income Household Size

I was trying to use INDEX with @row but the formula is returning UNPARSABLE. I don't think I need INDEX MATCH because I don't have multiple criteria, and I'd be at it for weeks if I was trying to set it up cell by cell. Any thoughts?

Paul Newcome

You would need to insert a text/number column (called "Number" in this example) and have it manually populated with the numbers 1 - however many rows you need. Then you would use this formula:

=INDEX({Column To Pull From}, Number@row)

Paul Newcome

Have you looked into creating a row report?

bstormer

Thanks for the suggestion! This technically works, but I'd love to have a formula-based solution for a couple of reasons: 1) Using a grid instead of a report gives me the ability to add/input data that isn't in the original sheet (e.g. adding a column for "days between intake and referral" or "computed age" instead of birthday to hide the DOB identifier but give the evaluators a data point they need; 2) The folks who designed the original sheet did put identifying data in the primary column, which I can hide but not remove from the row report.

I'm in a bit of a weird spot because I didn't design the original sheet, but I'm trying to create something with it that works for myself and our evaluators. With a grid, I have a bit more flexibility, which would be useful for this project.

Paul Newcome

You would need to insert a text/number column (called "Number" in this example) and have it manually populated with the numbers 1 - however many rows you need. Then you would use this formula:

=INDEX({Column To Pull From}, Number@row)

bstormer

That did it! Thank you!

Paul Newcome

Happy to help. 👍️