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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Assuming your lookup range is A1:B100 on each Project sheet (adjust to your actuals).

=VLOOKUP(A2,INDIRECT(A1&"!A1:B100"),2,0)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top