Lookup based on Sheet name and references

[casper3043]

Hi,

I've been struggling to come up with a formula that works for my scenario. I have a worksheet called "Summary." In this Summary worksheet, I am trying to reference a cell ($ amount) in another worksheet called "Project1." In worksheet "Project 1", there are $ amounts by week (columns) and different employees (rows). In my Summary worksheet, I would like to bring in the $ amount of a specific employee (row) and reference a specific week (column) based on the specified worksheet name. Also, in my Summary worksheet, I already have the employee name and week, so I just need to create a formula that would use these cells already and look them up in the "Project1" worksheet, if that makes sense...

The problem I have with this formula is that each Project workbook has $ amounts for different weeks and the columns aren't the same. And since I have many Projects, I would like to use 1 formula to reference all worksheets. I have attached a file if this helps.

[Richard Buttrey]

Hi and welcome to the forum.

May I suggest that you are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

In your case you should create a single sheet database in which your have columns for

Date
Project 'cells in this column could be drop down pick list values
Staff Type ' ditto cells in this column
Value

and in which you enter a new record on each row.

I'd urge you to adopt this approach before you waste too much time developing your current layout. When you have this post back for advice on how to set up a Pivot Table or report in other ways.

[casper3043]

thanks for the quick response Richard. I am working off a single sheet database. this is the source for my pivot tables that drives the information in each project. I only included what i thought would be necessary to help solve my issue. i thought my data was setup in a way that is pretty normal (weeks - columns and rows - employees). perhaps, my scenario is a bit more difficult as the data is specific for each project (i.e. the number of weeks a project is going on is not the same for each project). let me know if there's any additional information I can provide to help...

[Richard Buttrey]

No,

Your data is straightforward but the way you are setting it up is not normal if you wish to take advantage of all Excel has to offer you. As I said you are failing to distinguish between the layout of raw data and the end result that you want to see. The two are quite different.

I think you are misunderstanding what's being suggested. I had assumed that when you had week numbers across your columns, e.g 5-8 for Project 2, that 'weeks' meant specific dates i.e. perhaps a week commencing or week ending date, that's why I suggested a Date column. No matter, Where I said have a column for Date before change that to a column labelled Weeks.

So your master database would look like. Once you have this in place then you can quickly analyse with a Pivot Table.

Please Login or Register  to view this content.