Excel Help

spackbace on 04/08/2017 - 14:03

Not sure how else to title this, but I'm stuck on something that's above my Excel knowledge and Google couldn't answer this, so I'm hoping someone can.

OK so I have 3 different Excel files, each with a table in it like this:

A B C D
1
2
3
4
5
6

Now I'm trying to have a Master File, which collates everything from the others. The code I have for the 1st cell is this:

='[File1.xlsx]August'!$B$12+'K:[File2.xlsx]August'!$B$12+'K:[File3.xlsx]AUG 17'!$B$12

So the bottom right cell would be:

='[File1.xlsx]August'!$Y$31+'K:[File2.xlsx]August'!$Y$31+'K:[File3.xlsx]AUG 17'!$Y$31

I don't want to manually change that B12 cell code for each and every one (have a 31x24 table to fill!), and just trying to work out what code I need so I can enter it once, then drag the highlight down so it automatically adds the necessary info.

Can someone please help? :)

Computing

Comments

  • loke on 04/08/2017 - 14:05
    +8

    Tried removing the $ signs? Those are used to keep the column and row references stationary

    edit: quick reading via first hit on google re: cell reference absolute/relative http://www.informit.com/articles/article.aspx?p=2031329&seqN…

    • spackbace on 04/08/2017 - 14:16

      cell reference absolute/relative

      Well I didn't know the name so I didn't search that :P

      • ellave on 04/08/2017 - 16:24
        +2

        Use f4 in the formula bar and it cycles through

  • dasher86 on 04/08/2017 - 14:09
    +3

    $ signs denote absolute referencing.
    remove the $ in front of the number value

    ='[File1.xlsx]August'!$Y31+'K:[File2.xlsx]August'!$Y31+'K:[File3.xlsx]AUG 17'!$Y31

    i could be wrong!

    • battler on 04/08/2017 - 14:11
      +2

      remove $ from letter and number to allow copy down and across

  • spackbace on 04/08/2017 - 14:11

    Thanks so much guys that worked! :) Here I thought it would be more than that!

  • tsunamisurfer on 04/08/2017 - 14:51
    +9

    Never thought I'd see the day $ signs are unwanted on Ozbargain.

  • havebeerbelywillsumo on 04/08/2017 - 15:26
    +2

    Good to see all is now well but just remember that the reverse is also true ie when you don't want the cell references to change "$" is a godsend.

Login or Join to leave a comment
Login Join