# Extract numbers from Multiple cells & sheets

#### Nanda

##### New Member
I am working on a single page report that needs to show data from multiple sheets..

Say.... I type in cell a1 "Project No.1" & in cell A2 the month, say "Nov.04".
The cell in a3 will have the account ( Say Utility GAS).

(Please note that cell A1 and Cell A3 will be keyed in with several project names and months.The workbook has number of actual project data.)

The question is what formula need to be used in cell "B3" to get the
amount from the worksheet Project No.1 and coloumn Nov.04 .

Thank you.

Nanda

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### NBVC

##### Well-known Member

=VLOOKUP(A2,INDIRECT(A1&"!A1:B100"),2,0)

#### NBVC

##### Well-known Member

=VLOOKUP(A2,INDIRECT(A1&"!\$A\$1:\$B\$100"),2,0)

Edit:

I forgot to make the range absolute in my previous post.

#### Nanda

##### New Member
I think I did not explain completely.

My project one worksheet is as follows...with their respective column and rows.I have more than 30 worksheets for each project with same format.
The only variable is the name of the worksheet for each project.Each sheet
is named project1,project2,project3 etc...

I want a formula, on sheet one of the workbook , to show the respective amount for the respective month when I key the project#, Name of Account and the month.

A B C D E F

1. Nov 04 Dec04 Jan05 Feb05
2.UTILITY 25,648 45,789 74,879 45,896
3.MNTN 12,824 22,895 37,440 22,948
4.SWGS 12,824 22,895 37,440 22,948
5.OHEA 1,924 3,434 5,616 3,442
6.TROR 3,847 6,868 11,232 6,884
7.RDAY 7,053 12,592 20,592 12,621

Thanks again.

##### MrExcel MVP

perhaps:
Book1
ABCDE
1Nov 04Dec 04Jan 05Feb 05
2
32.UTILITY25,64845,78974,87945,896
43.MNTN12,82422,89537,44022,948
54.SWGS12,82422,89537,44022,948
65.OHEA1,9243,4345,6163,442
76.TROR3,8476,86811,2326,884
87.RDAY7,05312,59220,59212,621
9
10#4
11AccountSWGS
12Month1/12/2004
13
14Result22895
Sheet2

...formula is

=INDEX(B3:E8,MATCH(B10&"."&B11,A3:A8,0),MATCH(B12,B1:E1,0))

#### Nanda

##### New Member
perhaps:
Book1
ABCDE
1Nov 04Dec 04Jan 05Feb 05
2
32.UTILITY25,64845,78974,87945,896
43.MNTN12,82422,89537,44022,948
54.SWGS12,82422,89537,44022,948
65.OHEA1,9243,4345,6163,442
76.TROR3,8476,86811,2326,884
87.RDAY7,05312,59220,59212,621
9
10#4
11AccountSWGS
12Month1/12/2004
13
14Result22895
Sheet2

...formula is

=INDEX(B3:E8,MATCH(B10&"."&B11,A3:A8,0),MATCH(B12,B1:E1,0))

Thank you PaddyD. This works fine.

But my issue is that I have similar matrix
(... like the one above from a1:e8) in more than 30 pages, where each sheet is labeled project#1,project#2 etc.

My intended report will be just the first page, where I key the
month, project#,name of account.

What formula should be used to pick the amount from the respective page/month column/account name row.

Thanks again.

#### XLGibbs

##### Well-known Member

To understand correctly, you want this formula to work based on the entry of row/column information on this report sheet, and subsequently pull the data from an also specified sheet?

Some factors would need to be true to begin formulating a solution:

1. Are each of the matrices identical in size, scale, and scope (meaning identical structure all the way through)
2. You intend to get a line item from each sheet and place it on your report based on whichever row/column you enter (?)

You could begin the process and make the cell references into named ranges preceded by an ! which would make them applicable on whichever sheet the named reference is used on.

Example Insert>Name> Define
Name = "MatchRange"
Refers to: =!A3:A8

INDEX(B3:E8,MATCH(B10&"."&B11,MatchRange,0),MATCH(B12,B1:E1,0))

The named ranges can be used to identify ranges and cells (among other uses)

After that some other things will have to happen, but I am not sure if I am on track with your intent.

#### Nanda

##### New Member

1.Matrix will be identical in all 30 pages. 49 columns and 51 rows.
Ist coluumn is the name of account. 2nd to 48th will be month.

The following matrix,which I have "cut and pasted", on sheet one will explain further.

( In cell b3 , sheet named project 1 ,the formula picks the data from range name "project 1". For convenience I have named each sheet Project 1..Project 2 etc. )

Sheet 2 to sheet 30 will be same format.

Row--> A B C D E
Nov-05 Dec-05 Jan-05 Feb-05
2.

3 2.UTILITY =INDEX(project1, MATCH(A3,\$A\$1:\$A\$8,), MATCH(B1,\$A\$1:\$E\$1,))
4 3.MNTN
5 4.SWGS
6 5.OHEA
7 6.TROR
8 7.RDAY

My probelm is to insert a name range ( in this formula project#1 ) , to change everytime I key in the disired range name in a differant cell.

Thank you so much.

##### MrExcel MVP
review the help file for indirect().

whith the set up as above, but this time including B9 as the cell to indicate which sheet in the file to look up the table in:

=INDEX(INDIRECT(B9&"!B3:E8"),MATCH(B10&"."&B11,INDIRECT(B9&"!A3:A8"),0),MATCH(B12,INDIRECT(B9&"!B1:E1"),0))

Replies
6
Views
73
Replies
2
Views
252
Replies
0
Views
327
Replies
1
Views
94
Replies
0
Views
116