Auto fill down a column changing column letter

[Ell0ll]

Hi

when i use fill down column > G is not changing

how to make it change so when i fill down change G to H to I etc

=HYPERLINK(CONCATENATE("#";CELL("Address";G$5));Sheet1!$A9)

i hope someone can make it for me

Thanks

[hoyasaxa215]

Enter the formula below and copy down:

Please Login or Register  to view this content.

[Ell0ll]

Enter the formula below and copy down:

Please Login or Register  to view this content.

where should i put your formula

here is my formula
=HYPERLINK(CONCATENATE("#";CELL("Address";G$5));Sheet1!$A9)
i want auto fill down and change column letter >> CELL("Address";G$5)
column G to H to I etc

edit it for me

thanks

[hoyasaxa215]

Just put my formula wherever you want the letter G.

[Ell0ll]

Just put my formula wherever you want the letter G.

i tried it gives me > reference is not valid

it will corrupt hyperlink function

[hoyasaxa215]

My formula is completely unrelated to your formula. Delete your entire formula and enter my formula. Or just enter my formula into an empty cell.

[Ell0ll]

My formula is completely unrelated to your formula. Delete your entire formula and enter my formula. Or just enter my formula into an empty cell.

i know that

thanks

i need to fix my formula so when i use fill down - it make change to column letter ( G )

[hoyasaxa215]

Your formula clearly contains references to other cells within your workbook. Without being able to see the full workbook, it will be impossible for anyone to attempt to edit your formula.

[Ell0ll]

Your formula clearly contains references to other cells within your workbook. Without being able to see the full workbook, it will be impossible for anyone to attempt to edit your formula.

i have attached the workbook so you can see it

i want to link cash in banks in row with the one on column

i did it and it is working but

i want to drag and fill down to make every cell on row

link to the other cell on column

i hope you understand me

[Tony Valko]

=HYPERLINK(CONCATENATE("#";CELL("Address";G$5));Sheet1!$A9)

Try replacing the highlighted section with this:

CELL("address";INDEX(G$5:Z$5;ROWS(A$1:A1)))

Adjust for the correct end of range where I've used up to Z5.

[Ell0ll]

Try replacing the highlighted section with this:

CELL("address";INDEX(G$5:Z$5;ROWS(A$1:A1)))

Adjust for the correct end of range where I've used up to Z5.

it is working now

Thanks Tony

[Tony Valko]

You're welcome. Thanks for the feedback!

[Ell0ll]

You're welcome. Thanks for the feedback!

Another qustion for tony

can we make hyperlink by cell value
because this way if i change Cash in Banks colmun D to to another column
when i click on Cash in Banks it will go to column D not the other column

[Tony Valko]

Sorry, I don't understand.

[avk]

Dear Ell0ll, Refer attach file. In this file, done modification in hyperlink. If you click on "Retained Earnings" in "B" column row(47), then control go on "AS5".
If you come back in column "B4" then click on cell "AS5" this applicable all others row "D5" to "AS5".

[Ell0ll]

I mean

if i change Cash in Banks column D to column F
and Petty Cash to column D instead of column F

replace column D with F

when i click on Cash in Banks it will go to column D not To column F

this means hyperlink goes to cell address
and it is not searching all columns to find out where is Cash in Banks

maybe we can use match function to find which column has "Cash in Banks"

i hope you understand me

[avk]

if you click on column "B" chart of account name, then control goes to respective account name from "D5" to "AS5". If you click on any account name from "D5" to "AS5" the control goes to column "B" chart of account.

[Ell0ll]

if you click on column "B" chart of account name, then control goes to respective account name from "D5" to "AS5". If you click on any account name from "D5" to "AS5" the control goes to column "B" chart of account.

Thanks avk

but i want to do another thing

I mean

if i change Cash in Banks column D to column F
and Petty Cash to column D instead of column F

means : replace column D with F

when i click on Cash in Banks (column B) it will go to column D not To column F
and this is wrong because Cash in Banks in column F not D (look to my attachment)

this means hyperlink goes to cell address
and it is not searching all columns to find out where is Cash in Banks

maybe we can use match function to find which column has "Cash in Banks"

i hope you understand me

[Ell0ll]

i have found something same as what i mean

someone take a look to it

Thanks

[JohnTopley]

Is this what you want:

=HYPERLINK(CONCATENATE("#",CELL("address",INDEX(D$5:AS$5,MATCH(Sheet1!$A6,D$5:AS$5,0)))),Sheet1!$A6)

[Ell0ll]

Is this what you want:

=HYPERLINK(CONCATENATE("#",CELL("address",INDEX(D$5:AS$5,MATCH(Sheet1!$A6,D$5:AS$5,0)))),Sheet1!$A6)

that is it

Thank you Jhon