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
  • 2
  • When no argument is given, COLUMN() returns the column the formula is in. How did you try to use it exactly? – Ƭᴇcʜιᴇ007 Mar 13 '14 at 16:23
  • @techie007 I tried to just use it in place of the actual column letter. =$COLUMN()1 hoping that when I did a worksheet-wide find/replace it would grab the value from the appropriate heading cell. – Vecta Mar 13 '14 at 16:27
  • http://office.microsoft.com/en-ca/excel-help/column-function-HP010062409.aspx – Ƭᴇcʜιᴇ007 Mar 13 '14 at 16:30
  • It returns the numeric representation of the column. So column "A" would be 1, B would be 2, etc. I found a dupe of your question, hopefully it and it's answers will help ya out. – Ƭᴇcʜιᴇ007 Mar 13 '14 at 16:32
  • possible duplicate of [How to get the current column name in Excel?](http://superuser.com/questions/316239/how-to-get-the-current-column-name-in-excel) – Ƭᴇcʜιᴇ007 Mar 13 '14 at 16:32
  • I'm pretty sure the issue here is trying to replace with a formula. This would get you what you want, but won't work in a find a replace - `=INDIRECT(ADDRESS(1,COLUMN()))` Can you use VBA? – Raystafarian Mar 13 '14 at 16:33
  • @techie007 I don't think this is a duplicate because it's about using the Find/Replace search function, rather than a formula alone. – Raystafarian Mar 13 '14 at 16:37
  • 1
    how could you have a head of column reference with a $A1 formula that reference a "head" of row, don't you have to use A$1 instead or i'm missing the point – NeronLeVelu Mar 14 '14 at 06:57

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,214
  • 6
  • 12