Line number in formula is not counted when dragged
I have the below formula that I use to reference a specific sheet and cell in my workbook, which contains a graph for each record. In the sheet I also refer to, each graph is about 20 cells compared to the previous one. I now have over 100 graphs and it will grow over time, so I tried to use the HYPERLINK formula and not the Hyperlink button to do this. I thought I could just insert the formula in the first row, insert it in the second row with 20 cells added, select two and drag them, but it won't count with step 20.
Is it possible?
=HYPERLINK("#'Trends'!A25","Click To View Trend")
source to share
I think you will need to use some type of concatenation to get your behavior. You can use the "helper" column for this. For example, enter "numbers" that you are after in a column B
- below you will see that I have increased it by 5.
Now your formula HYPERLINK
in cell A1 is written as:
=HYPERLINK("[Book1]Sheet2!A"& B1,"Click Me for Sheet2, Cell A"&B1)
(Assuming the workbook is called Book1
. Now I can drag this formula down and it will dynamically update to reflect changes in column B.
source to share