# Macro insert formula in Index sheet next to each sheet name hyperlink in column and show data

#### Vbalearner85

##### Board Regular
Hi,

I have sheet name "Index" which in Column A (starting cell A2) contains hyperlinks (sheet name) to each sheet cell reference "A5"

I want to create a macro which shall :-

1) link and display data of each sheet cell reference "A3" in Index sheet (in column B of sheet "Index", corresponding to sheet name hyperlink in column A of same sheet "Index")
2) link and display data of each sheet cell reference "C9" in Index sheet (in column C of sheet "Index", corresponding to sheet name hyperlink in column A of same sheet "Index")
3) link and display data of each sheet cell reference "F12" in Index sheet (in column D of sheet "Index", corresponding to sheet name hyperlink in column A of same sheet "Index")
4) Put a formula "= max(Sheetname!D:D)" (in column E of sheet "Index", corresponding to sheet name hyperlink in column A of same sheet "Index")
5) Put a formula "= min(Sheetname!E:E)" (in column F of sheet "Index", corresponding to sheet name hyperlink in column A of same sheet "Index") and shall ignore blank cells- so as not to give 0 values

Regards,
PK

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

##### Well-known Member
Hello,

does this work as expected?

VBA Code:
``````Sub index()
Application.ScreenUpdating = False
With Sheets("Index")
For MY_ROWS = 2 To .Range("A" & Rows.Count).End(xlUp).Row
.Range("B" & MY_ROWS).Formula = "=" & ActiveSheet.Name & "!A3"
.Range("C" & MY_ROWS).Formula = "=" & ActiveSheet.Name & "!C9"
.Range("D" & MY_ROWS).Formula = "=" & ActiveSheet.Name & "!F12"
.Range("E" & MY_ROWS).Formula = "=max(" & ActiveSheet.Name & "!D:D)"
.Range("F" & MY_ROWS).Formula = "=min(" & ActiveSheet.Name & "!E:E)"
Next MY_ROWS
End With
Application.ScreenUpdating = True
Sheets("Index").Select
End Sub``````

not sure what you mean by ignore blank cells

#### Vbalearner85

##### Board Regular
Hi.....It does update the formulas as mentioned. But needs update for below:-

1) Macro prompts "Update:Auto" browse window for each sheet formula update..which I have to escape by pressing "Esc" key manually to continue with macro running.

2) Gives linked data in Hyperlink format, which I would like to have in original data format(numerical).

3 request for addition of 1 more code line though...for "Link last non blank data cell in column B" in column G

Regards,
PK

#### Vbalearner85

##### Board Regular
Hi sir, as I had a relook:-

1) Please ignore point 2 above when you review the code, that is working fine.
2) There are some special character worksheet names( having Hypen "-" and "&") in column A ..if that has any impact on how code is working

#### Vbalearner85

##### Board Regular
Sir I tested code after deleting sheet name hyperlinks in "Index" sheet having "-" and "&" in names. The code is working perfectly..so need to handle the sheet names having "-" and "&"..that is causing the error
And request for addition of 1 more code line though...for "Link last non blank data cell in column B" in column G

Regards,
PK

Regards,
PK

Replies
87
Views
752
Replies
15
Views
380
Replies
2
Views
84
Replies
4
Views
103
Replies
0
Views
136

1,127,531
Messages
5,625,369
Members
416,098
Latest member
jpmiralrio

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back