1

I was given a rather large Excel file recently to use for some data entry purposes. I'm trying to perform a find/replace in this file to replace specific text with the value in the current column's heading (row 1).

I've been able to accomplish column-by-column (rather slowly) by inserting the following where applicable:

=$A1

Is there a way I can, instead, just select everything and dynamically reference the column of the current cell? I've looked into COLUMN() but I'm unsure of how to use it.

Thanks!

Vecta
  • 111

1 Answers1

0

Open up a new tab and copy over the column headers (just the column headers). Assuming your column headers are in A1:Z1 and your data is A2:Z50000 and that the condition for which you want to use the column header value is when the cell says "Moose":

=if(<originalsheetname>!A2="Moose", index($A$1:$Z:$1, 1, column()), <originalsheetname>!A2)

Index, here, grabs the value from the row (1), and column (whatever column the cell is in) for the range "A1:Z1" (your header rows). The IF statement says to only grab the column header value when A2 is "Moose" (change for whatever criteria you are searching out), otherwise just use the value in A2)

Now just copy and paste that formula so it's the same number of cells covered by your data in the original tab.

JNevill
  • 1,241
  • 6
  • 12