Using lookup function to summarise data in a sheet

[Col]

Hi all,

At work I've been tasked to produce a workbook that can be sent to various
offices where the local managers can produce a report from the main data
sheet. I think my solution would be to use access but the IT section won't
buy a runtime kit so can Excel provide the solution?

The data spreadsheet is very simple. The column headings are the financial
weeks of the year (a drop down box will be used as the medium for the
managers to select an appropriate week)

Data in the rows of column A would be vehicle registrations, creating a
matrix. The data in the matrix would be one of maybe five identifiers
(M,T,X,C,L) depending on which type of service check required.

So for week three of the year in column D you may have a M in row 4 another
in row 6, a C in row 10 and so on.

Which function or combination of functions would I use to bring the data
together on another sheet? I've tried Index/Match, Lookup, Vlookup, but I
need something that can skip blanks in the sheet and bring together only the
data on a separate sheet with just the use of a drop down box.

I'm pretty OK with functions and their use if someone could point out the
ones I need to look at I may be OK.

Many thanks for the help,

Colin.

 

[Vasant Nanavati]

Perhaps using Autofilter on the data and filtering out blanks would work for
you.

 

[Col]

Yes, it's something I considered but I prefer to use a function or functions
if possible though.

Thanks,

Colin.

 

[Vasant Nanavati]

I think that would be somewhat complicated and time-consuming to work out
.... but maybe that's just me!

 

[onedaywhen]

At work I've been tasked to produce a workbook that can be sent to various
offices where the local managers can produce a report from the main data
sheet. I think my solution would be to use access but the IT section won't
buy a runtime kit so can Excel provide the solution?

Yes. If you have a Jet database on a network share, you can build
client applications in Excel that accesses its data using a
client-side data access technologies such as MS Query (GUI tools) or
ADO (VBA code). You do not require the MS Access run-time because
'Access' relates to the GUI application (forms, reports, etc) not the
underlying database (tables, data, stored procs/queries, etc) which is
Jet. For your client applications to use a Jet database, you only
require Jet and possibly MDAC, which your team will probably already
have because both ship with most versions of MS Office.

--