vlookup? indirect?

[streetboarder]

Need help!

I have two seperate sheets. RawData (Needs to stay in Priority # sort)
and Overview

On the RawData there are approx. 30 columns for current projects.
Listing things like: manager, priority number, status ("AD" : row
contains the following results: "completed", "not started", "active").
Approx. 60 projects but need to plan on 250

I need to match up two of the 30 columns from the overview sheet:
Projects Status "not started" (Column "AD" and Manager (Column "D").

"Overview" file needs to show all projects not started, by manager
including a few extra cells found in the same row (priority numbers -
"A"), and (total hours - "T"). There is a second piece to this puzzle
but I think if I can see this example I can figure the other one out.

One option is to sort the data to another wookbook, delete rows and
copy over but I would like to do this automatically so that it will
always list all projects not started. I can also probably (not sure
yet), write a horrible macro to do all of this as well, so I would be
greatful for anyone's help on this as I am sure there is a much easer
way to do this.

It's late so if no one is understanding this please let me know and I
can get back to you with specifics.

Thanks!

 

[John James]

Start with the simplest possible solution, based on my reading.

Keep it simple. Keep it all in one file if possible.

Have you considered leaving the data in place and producing your
desired results on the RawData sheet through an autofilter?


Add columns for vlookups in the RawData table:

http://contextures.com/xlFunctions02.html

e.g. In the first row enter formulae and then copy down:
=vlookup(ProjectID,OverviewTable,NoOfColumns,0)
Replace "ProjectID" & "OverviewTable" with references fo the
relevant cell in the RawData table & the Overview table range
respectively.
Replace "NoOfColumns" with a number (representing the desired number
of columns in the overview table to the right of the projectID +1)


In your data area, select Data-Filter-Autofilter
From the drop-down boxes make your selections

Once working, can then look at improvements (error conditions, etc) &
consider alternative formulae if speed is an issue.

 

[streetboarder]

Thanks John. Looking into the vlookup function.

Only problem is that I need to pull three different sets of different
columns onto the same page so using a "a" column works for one set but
not the other two.

I have a semi-working macro that copies the "rawdata" sheet, sorts it
the way I need, copies the data to the overview sheet, removes the link
(paste special - vaules) and deletes the temp data sort sheet.

It's about 50% complete so far.

How do you know when you need Access or Excel in this situation?

Most of the data is text, a few dates, no formulas "other than simple
count and sum" and a lot of different reporting.

Any ideas on which to use? I don't know Access that well (more than
willing to learn) so I would appreciate anyones comments they have.

Thanks!

 

[John James]

If you want to pull three different sets of different columns onto the
one page, use three new columns on that page with three different
(albeit similar) vlookup formulae.

Only problem is that I need to pull three different sets of different
columns onto the same page so using a "a" column works for one set but
not the other two.

Better to keep it simple. If standard excel functionality produces the
result you're after (as it apparently does based on your description),
then avoid the complexities of macros.

I have a semi-working macro that copies the "rawdata" sheet, sorts it
the way I need, copies the data to the overview sheet, removes the link
(paste special - vaules) and deletes the temp data sort sheet.

It's about 50% complete so far.

There seems no need to consider Access for your need. Your database of
60 columns (fields) by 250 rows (projects) is small and is suited to
Excel. Access copes with huge databases better than Excel, largely
because of differences in how memory is used.

Access is generally more robust. For example, in Excel a user can
accidentally delete a formula in a cell. Much harder to make similar,
hard-to-detect mistakes in Access. Building in checks is an important
part of spreadsheet design.

Excel models are generally far quicker to build, and more flexible in
reporting.

I'd suggest a better investment of your time is to understand vlookups,
filters, etc than to learn an entirely new product (Access) which
doesn't seem to have significant advantages for your needs. Using
Vlookups is a core excel skill that you'll use repeatedly once learnt.

 

[streetboarder]

Thanks!

Ok. Iv'e deleted the macro's and not looking at access and trying to d
this through Vlookups and other functions.

I have information from the rawdata sheet going to the followin
worksheets.

"Summary. Shows totals and active projects only"

"Overview - not Started projects sorted in three different ways (b
manager, by # and by hours)

Project list by type (all projects totaled by status (active, no
started, etc),

Project list by manager with totals per manager.

All of this data is on my RawData sheet.

My question is can this different information all pull from the RawDat
sheet or do I need to include other hidden sheets to do this? Also
using a Vlookup or other functions how do i find the "project status
Cell: AD13 match it to a single option and then pull the whole row o
info? This would be my solution if I can use hidden worksheets.

Thanks