Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
generate a list from a single row of data
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 |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
generate a list from a single row of data
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
generate a list from a single row of data
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 |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
generate a list from a single row of data
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,r ole_id) VALUES('" & prid & "','" & reid & "','" & roid & "');" con.Execute query con.Close ws.Close End Sub "Fred" wrote: 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 |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
generate a list from a single row of data
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to generate data from a list on excel from another worksheet? | Excel Worksheet Functions | |||
generate a list from a single row of data | Excel Discussion (Misc queries) | |||
How do I query a List to generate source data for a chart? | Excel Worksheet Functions | |||
How to generate a function from the given data list(Y and X) | Excel Worksheet Functions | |||
Compare worksheets and generate list of missing data? | Excel Worksheet Functions |