Increment letter on different sheet

[drchris]

Guys,
I'd really appreciate your help with this cause I can't figure this one out. Its very simple what im trying to do!

I have a cell reference in A1 with ='P&L Summary'!C100

I want to drag it down (to A2, A3, A4 etc) but I want the letter to increment - not the number.
So in:
A2 i will have ='P&L Summary'!D100
A3 i will have ='P&L Summary'!E100
etc

how do I do it?

Thanks
Chris

[teylyn]

You will need to create a custom list

A
B
C
D
etc

Then create a list of letters in a helper column, for example in column B. Start with the entry "A" (without the quotes), and drag the fill handle down, to increment the letters. Next, use a formula like

=Indirect("'P&L Summary'!"&B1&"100")

As an alternative, use

=Index('P&L Summary'!$A$100:$Z$100,1,row(A1))

and copy down. The row(A1) will return a 1 (=equals the first column in the range). Copied down, it will be row(A2) returning 2 (= the second column in the range)

The Index version is faster than the Indirect and with less overhead.

and copy down.

[drchris]

thank you so much!