Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I'm trying to drag copy some cells, which are on top row for headers, they get their values from a VLOOKUP formula (Basicaly all group names are in column B and I want them as column headers). Before each cell there is another cell just containing string "ID".

Table that I want drag autofill top row according to list in column B

What I want is to select first pair (ID and Machinery) and drag to right so that this pattern continues for all group names in column B.

enter image description here

It doesn't work. I thought having first 4 cells (ID Machinery ID Clothing) and drag them might help Google Spreadsheet to recognize pattern, but it didn't. I tried same thing on Excel and if fails there too.

Then I realized dragging right only changes column letters in formulae not row numbers (which makes sense).

Is it possible to change this behaviour? If not, any suggestions for me not to copy paste and edit for each entry.

Thanks

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

As explained here when drag copy formula horizontally incrementation happens with anything related to columns. Nothing happens with rows. In order to get an increasing number one can use COLUMN() function in this scenario.

We get row number of original list with a calculation involving COLUMN() function, but then we have to create a string defining our original list's range and get the value in that cell. This is done by INDIRECT() function. (VLOOKUP method not good)

=INDIRECT("$B"&COLUMN()/2)

Working Formula in Spreadsheet for Horizontal Drag Auto-Fill

Now selecting ID and Machinery and dragging right fills all consecutive columns as desired.

Finished Spreadsheet, it WORKS

Another alternative:

=INDEX($B:$B;COLUMN()/2)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.