Auto-fill Formula or macro?

[jackofalltrades]

I have to complete an unfiltered table using formulas....The columns are labeled from 1-16 and the rows are labeled from A-Z. In row A it goes....1A, 2A,3A,4A all the way to column 16, which is 16A. The same happens for Row B. I have to create a formula to fill in the rest of the rows/columns with the same sequence. What formula do you use? Is this a macro function?

[Pete_UK]

Why not add a workbook (the FAQ gives details of how to do this)? Then we can see more clearly what you want to do.

Pete

[jackofalltrades]

@Pete_UK Here's the file. I can't do the autofill, I have to use a formula. Thanks in advance!

[Pete_UK]

I think this is a question related to using absolute and relative addressing (homework?) You can use this formula in C1:

=B$1 & $A4

and this can be copied across and down to any of the other empty cells.

Note the use of the dollar symbols so that row 1 does not change when the formula is copied down, and column A does not change when the formula is copied across.

Hope this helps.

Pete

[jackofalltrades]

Thank you Pete! Would you mind explaining this to me? It is for homework but I really want to understand why I used this formula and not a different one. I think I just need more experience working with formulas because this was such a simple solution that I didn't even think of.

[Pete_UK]

When you copy and paste a formula the cell references usually change to suit the row and column that you are on. Suppose you have this formula in A2:

=A1 + 2

Then, when you copy this down column A it will become:

=A2 + 2
=A3 + 2

and so on, i.e. it will always add 2 onto the contents of the previous cell, and if you copy it across row 2 it will become:

=B1 + 2
=C1 + 2

This is referred to as relative referencing, as the cell references change relative to where the formula is copied to.

There are situations, however, where you do not want the cell reference (or part of it) to change when you copy the formula. In your case, you always want to get the number from the top row and the letter from the first column. In this case you can switch to absolute referencing, where you want to "anchor" either the row or column reference (or both) to the same point in the worksheet. You do this by including a $ symbol in front of either the row or column reference (or both), so that when you copy the formula that part of it will always remain the same.

You can find further details in Excel Help - just search for "relative", and then click on "Switch between relative, absolute, and mixed references"

Hope this helps.

Pete

EDIT: as this is your first thread, you might like to be reminded to mark it as Solved (the FAQ describes how). Also, you can pass on your thanks directly by clicking on the "star" icon in the bottom left corner of any post that you have found helpful.