Extract numbers from Multiple cells & sheets

Nanda

New Member
Joined
Dec 9, 2005
Messages
7
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
 

Some videos you may like

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
Joined
Aug 31, 2005
Messages
5,828
Assuming your lookup range is A1:B100 on each Project sheet (adjust to your actuals).

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

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Assuming your lookup range is A1:B100 on each Project sheet (adjust to your actuals).

=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
Joined
Dec 9, 2005
Messages
7
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.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

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
Joined
Dec 9, 2005
Messages
7
PaddyD said:
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
Joined
Feb 25, 2005
Messages
2,446

ADVERTISEMENT

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
Joined
Dec 9, 2005
Messages
7
Thank you Gibbs. Reply for your question 01 and 02 are

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.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,118,084
Messages
5,570,131
Members
412,305
Latest member
Mozz
Top