generate a list from a single row of data

[Fred]

Using Excel 97, I have a spreadsheet of Project Reports, arranged
across the sheet as follows :

Column A thru I are templates, hidden from the Project Managers
Column J I want to have a table of project names that are in the
worksheet
Column K is blank
Column L is the labels/descriptions for rows going across the
worksheet
Rows 4 - 29 Various Dates/contacts etc.
Rows 30--53 Month labels for 2 years of resource forecast data
Column M, rows 4-29 blank
rows 30-53 Project Manager absence (vacation/training) forecasts
Column N is a Summary row of Project Managers forecast time for all
projects & absence
Rows 4-29 blank
Rows 30-53 a total, using sumif, to match the team name with the
Project Manager team name
Column O rows 4-29 Various Dates/Contacts etc (see Column L above)
Rows 30-53 Project Managers forecast time for this project
Column P for a variable number of columns (based upon the number of
team entries)
Rows 4-29 overlaid with a free-form Text Box for PMs to report
project status/issues etc
Column P for a variable number of columns (based upon the number of
team entries)
Rows 30-53 Forecast resource requirements for each team

Columns O and column P, for a variable number of columns, are repeated
for each project the PM is managing.

Row 3 Column O contains the project name of the first project
Row 3 in the last but one column of a project contains the word Status
Row 3 in the last column of the project contains a RAG indicator for
the project Red/Amber/Green)

What I would like to be able to do is generate a table of Project
Names in Column J, omitting all the blanks, Status and RAG
indicators. I know where the first project name is (O3), but,
thereafter, it could be in any column across row 3 of the worksheet,
apart from the last and last-but-one for each project.

Can anyone offer a solution to the above please ?

Regards
Fred

 

[Guest]

Hi, ideally, you should maintain data of same category in a column and not
spread across all columns in a row.
If you are unable to do that, then the other alternative would be to prefix
the project name with a special text such as "PRJ-", then you can write a VBA
code to search for cells whose value begin with the special text "PRJ-".
I hope this is a simple solution to implement.

 

[Fred]

Hi Madhan,

Yes, I can scan a row and exclude the cells I don't need, it's the
wriing the results to the table bit that I'm struggling to understand.

Cheers
Fred

 

[Guest]

Hi, please find below a code snippet to insert a row into a table. You can
use it.

Public Sub myUpdate_ProjectResourceRole()
Const DSN As String = "ODBC;DATABASE=" & DB_NAME & ";UID=;PWD=;DSN=DSN_TEST;"
Dim ws As DAO.Workspace
Dim con As DAO.Connection
Dim qd As DAO.QueryDef

Set ws = DAO.CreateWorkspace("", "", "", dbUseODBC)
Set con = ws.OpenConnection(DSN, dbDriverNoPrompt, False)
query = "INSERT INTO
Map_Project_Resource_Role(project_id,resource_id,role_id) VALUES('" & prid &
"','" & reid & "','" & roid & "');"
con.Execute query
con.Close
ws.Close
End Sub

 

[Fred]

Ha ha, ok, I understand what you have given me, however that wasn't
quite what I wanted to do.

My meaning of "table" was a simple set of entries in the excel
worksheet, J4:J29 (or for as many rows as is needed to list all
project names), showing the project names that were found in row 3.
Apologies for any confusion.

Have a great weekend
Regards
Fred