0

Below there are two pictures of tables

vertical table is Temp data 2014.xlsm

horizontal table is Overview_allcities 2014.xlsm

I want to quickly setup the values in the horizontal table using the auto fill.

The value of C34 in the horizontal table is 

 =IFERROR(IF(MATCH('[Temp data 2014.xlsm]Harrison'!$A$2,A1,0)=1,'[Temp data 2014.xlsm]Harrison'!$D$4,""),"--")

Now when I drag it to the right I want to increment it to "$D$5" i.e.

=IFERROR(IF(MATCH('[Temp data 2014.xlsm]Harrison'!$A$2,A1,0)=1,'[Temp data 2014.xlsm]Harrison'!$D$5,""),"--")

I tried using changing the references to absolute and relative. None would do this step automatically.

weatherdata1 weatherdata2

newishuser
  • 610
  • 4
  • 24
user3850376
  • 23
  • 4
  • Possible [duplicate here](http://stackoverflow.com/questions/13348825/drag-down-formula-and-change-column-references-instead-of-rows). Basically you need to use the offset function which can make for an uglier formula. – gtwebb Aug 06 '14 at 18:46
  • Also, everytime i paste a set of 100 formulae in excel I have to go to each cell and press F2 and then press Enter to get the results of those formulae. Is there a faster way to do this hackneyed stuff? – user3850376 Aug 06 '14 at 19:17
  • Is the sheet sheet to calculate manual. Try pressing F9 to calculate the entire sheet. You can set this in the ribbon under Formulas>Calculations Options – gtwebb Aug 06 '14 at 19:29

1 Answers1

0

Not-so out of the box idea here. Have your tried doing it with INDIRECT() ?

In my head I'm thinking something like this in the horizontal table:

You could use COLUMN() to create a moving variable. You in this case C34 - B would be 1, then +3 offset to get 4. Merge those together into a text string and you'd get a $D$4, then $D$5, and so on.

"'[Temp data 2014.xlsm]Harrison'!$D$" & COLUMN() - COLUMN($B:$B) + 3

Then implement it into the formula using INDIRECT().

 =IFERROR(IF(MATCH('[Temp data 2014.xlsm]Harrison'!$A$2,A1,0)=1,INDIRECT("'[Temp data 2014.xlsm]Harrison'!$D$" & COLUMN() - COLUMN($B:$B) + 3),""),"--")

Now, I'm 100% grasping at straws here since I've only quickly tested this but I'm quite certain it could prove useful.

Let me know if it works, or not. Might have to work out a few kinks!