vlookup looking at many worksheets


G

Guest

I have a problem with one of my spreadsheets.
I have worksheets for 12 months of the year(Jan-Dec) and a summary sheet.
The format for each sheet and summary sheet has names down one side and code
names going across as shown.
A B C
Hotel Travel
1 John Smith 300
2 Barry George 29

The current formula is simple and prone to errors when inserting new
columns/rows. Jan B1+Feb B1 and so on to return the total for all months in
the summary sheet.

Firstly I dont think it is possible but can you vlookup using two references
e.g search by John smith and Hotel to return 300?

Secondly what do you suggest is the best formuala to use in this situation
for it not to look too messy.
 
Ad

Advertisements

S

Stephen

dave said:
I have a problem with one of my spreadsheets.
I have worksheets for 12 months of the year(Jan-Dec) and a summary sheet.
The format for each sheet and summary sheet has names down one side and
code
names going across as shown.
A B C
Hotel Travel
1 John Smith 300
2 Barry George 29

The current formula is simple and prone to errors when inserting new
columns/rows. Jan B1+Feb B1 and so on to return the total for all months
in
the summary sheet.

Firstly I dont think it is possible but can you vlookup using two
references
e.g search by John smith and Hotel to return 300?

Secondly what do you suggest is the best formuala to use in this situation
for it not to look too messy.

Take a look here:
http://www.cpearson.com/excel/lookups.htm#DoubleLookup
 
Ad

Advertisements

G

Guest

Thank you for this function it works very well on this sheet and many others
similar to this.
=OFFSET('January 2007'!A6,MATCH(A14,'January 2007'!A7:A17,0),MATCH('YTD
2007'!C12,'January 2007'!B6:p6,0))

However I still have a problem as I will have to add into this formula 11
further sheets Feb through to Dec. By the end of the formula it will be huge
and anyone else looking at it might not know where to start.

Is there a way of using this formula or one like it so that I can add the 12
worksheets together.

Any suggestions???

Many thanks

Dave
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top