Excel VBA Assistance - Referencing Sheet Tab Names in Formulas

steelervince

Board Regular
I am in need of some assistance with a multi-faceted formula:

I have a single workbook which contains 15 sheet tabs.
Each sheet tab is named
On the Summary sheet tab I have 160 rows of data in 19 columns

In column B I have cells which contain the sheet tab names (randomly listed based on needed data)

I need:

1. In column A a formula which analyzes the sheet tab name for each value in column B, matches that sheet tab name, looks in the corresponding sheet tab, finds the value onthat sheet tab in cell D9, and then returns that value into the target cell in Column A on the Summary Sheet tab.

2. The formula once created needs to be copied down through all 160 rows in column A. Rows may increase or decrease from month to month

3. A similar formula on the Summary sheet tab for values needed in columns E, F, G, H, and on out until P.

I am trying to make this as easy as possible for a novice user in Excel so any assistance which you would be able to offer would be greatly appreciated!

I am in a bit of a time crunch on this one, so the sooner the better, thanks in advance!

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
the main sheet is sheet1 and reference sheet is sheet3(D9 to D18 are having some data)

sheet3 is like this
Excel Workbook
ABCD
1
2
3
4
5
6
7
8
9123
10124
11125
12126
13127
14128
15129
16130
17131
18132
Sheet3

now see below sheet1 the name of all the sheets are in B1 to B3
highlight 10 rows from A1 to A10
copy paste this formula
=INDIRECT(\$B\$3&"!D9:D20")
invoke the formula with control shift enter

do you get what you want. if it is ok can you solve the other problems

Excel Workbook
AB
1123sheet1
2124sheet2
3125sheet3
4126
5127
6128
7129
8130
9131
10132
Sheet1

OK, when I enter in this formula

{=INDIRECT(C17&"!D9")}

I get the =#REF!

Not sure what to do to rectify the situation.

Need help on this one!

What is in C17?

If your sheet name in C17 has spaces in it, you need to add the ' around the sheetname..

=INDIRECT("'" & C17 & "'!D9")

Replies
5
Views
415
Replies
6
Views
972
Replies
1
Views
201
Replies
2
Views
244
Replies
2
Views
244

1,203,096
Messages
6,053,515
Members
444,669
Latest member
Renarian

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.

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