Change cell reference when pasting

Question

I am working on a spreadsheet with 2 sheets. Sheet one has 71 columns x 104 rows of data. Each row is data for one individual. In Sheet 2 I am compiling a single row's worth of data into a 13x13 cell block, not every cell is used for referencing sheet 1 cells.

Is there a way to fill in the "block" of cells with all of Row 1's data and autofill/paste row 2,3,4,5, etc. with the same formatting. I want to paste the block of cells while increasing each formula's cell reference by 1. The cell references aren't linear or sequential, rather they are sort of random in their positioning.

Thanks!

screenshot of data, each row is 1 individual (data is incomplete currently)

Screen shot of block of data

Screenshot showing cell references

Yes that seems like it might work. Would you build the index formula to where a specific row number was inputted and the "table" autopopulates with the data from that row? — Matt Backus
First of all you'll need a cell to place a value to be fed to your formulas as row_num. Let's say it's cell N1. Then you'll need to write a formula for each of your cell. For exampe your A3 cell with the formula =Info!A2 will probably have to become =INDEX(Info!A:ZZ, N1,1) where Info!A:ZZ will cover you data table, N1 will determine your row (you'll have to start from row 2) and 1 will refers to the 1st column of the table (column A). You could also add a spinbutton connected to cell N1 to easily swap between rows. — Evil Blue Monkey

Foxfire And Burns And Burns Foxfire And Burns And Burns · Accepted Answer · 2020-12-05T01:21:45

As suggested in the comments, you may benefit from using INDEX combined with MATCH, or also VLOOKUP, using the column PEN (according to your data image, it looks like this field won't have duplicates, so you can use this to identify each row.

I made some fake dataset.

And My formulas in my form worksheet:

Formula in D2:

=INDEX(INFO!$A$1:$C$6;MATCH(B2;INFO!$A$1:$A$6;0);MATCH(C2;INFO!$A$1:$C$1;0))

Formula in G1:

=INDEX(INFO!$A$1:$C$6;MATCH(B2;INFO!$A$1:$A$6;0);MATCH(F1;INFO!$A$1:$C$1;0))

And then magic happens when dragging down.

Anyways, probably you'll need to combine this with each of your fields,a dn make sure it finds field names properly, so you'll need to adapt the formulas to your case.

Just as a suggestion, I strongly would recomment to import this records into an Microsoft Access Database, and then you could design there a report/form easily (and cooler) like the one of your Catalog Sheet, but Access itself would relate the records properly, no need at all of complex formulas.

Because both Excel and Access are from Office suite, both programs work pretty good combined.

Change cell reference when pasting

1 Answers