Excel VBA Assistance - Referencing Sheet Tab Names in Formulas

[steelervince]

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!

 

[venkat1926]

the main sheet is sheet1 and reference sheet is sheet3(D9 to D18 are having some data)

sheet3 is like this

Excel Workbook
	A	B	C	D
1
2
3
4
5
6
7
8
9				123
10				124
11				125
12				126
13				127
14				128
15				129
16				130
17				131
18				132
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
	A	B
1	123	sheet1
2	124	sheet2
3	125	sheet3
4	126
5	127
6	128
7	129
8	130
9	131
10	132
Sheet1

 

[steelervince]

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!

 

[Jonmo1]

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")