multiple rows as 1 record in a list/database

Mar 4th, 2002, 12:23 PM

Is it possible to assign multiple rows as a single record in a Excel list? Real world issue: I have a date (promotion period), on that date there are several items on promotion. The date is the first column in the list. I need the list filterable by date and don't want to have to re-enter the date for each row (or each item on promo under a given date). The users of the form that enters data to the list will simply get confused having to type the date over and over for each item on a particular promotion period. I hope I made this question understandable! Thanks

Mark W. · Mar 4th, 2002, 12:26 PM

Why don't you have more columns and fewer rows?

keith · Mar 4th, 2002, 12:29 PM

Not sure what you mean by more columns.. basically there are blank cells under the date column where the multiple items are listed.. so there may be something in the date colum every 5 rows or so.. see what I mean?

keith · Mar 4th, 2002, 12:33 PM

(I am one who posted the orignal just forgot to log in)

Mar 4th, 2002, 12:50 PM

ok here goes.. there are 15 total items. During a year these items are on off and on promotion a week at a time. The list needs to be organzied by date(which is actually "week of"). On a given date any combination of the 15 items can be on promotion. The user fills out a form that adds to the list based on a week. Here goes:
Week of - Item # - Price - Cost - Profit %
3/1 - 0101 - 2.99 - 1.50 - x%
- 0102 - 3.99 - 1.50 - x%
- 0108 - 4.99 - 1.50 - x%
3/7 - 0001 - 2.99 - 1.50 - x%
- 0105 - 3.99 - 1.50 - x%
ect... if there is a better way to do this I am all ears.. I just don't want to have to carry the date down every item ie - I would like 3/1 and all the items under it to be a single record. 3/7 starts a new record etc.. thanks..

Mark W. · Mar 4th, 2002, 12:50 PM

Suppose column A contains your promotional date beginning at row 2. Create an new date field ('Date2'?) in column C. Enter the formula, =IF(A2,A2,C1), into C2 and copy down for all data rows. Filter on column C.

keith · Mar 4th, 2002, 12:55 PM

(sorry forgot my name again.. all anonymous above is me)

keith · Mar 4th, 2002, 01:05 PM

ok - I think that works good enough- at least it stops the user from having to repeat the date in the form.. thanks!

keith · Mar 4th, 2002, 01:10 PM

uhoh - now I have to figure out how to include that formula in the records that are added by the form!!! The macro I was planning was going to do an "EndDown" to get to the bottom then add the data from the form

any help?

Mark W. · Mar 4th, 2002, 03:03 PM

Excel's built-in Data form (see the Data | Form... menu command) already handles the replication of the formula.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 4th, 2002, 12:23 PM	#1
Guest Posts: n/a	Is it possible to assign multiple rows as a single record in a Excel list? Real world issue: I have a date (promotion period), on that date there are several items on promotion. The date is the first column in the list. I need the list filterable by date and don't want to have to re-enter the date for each row (or each item on promo under a given date). The users of the form that enters data to the list will simply get confused having to type the date over and over for each item on a particular promotion period. I hope I made this question understandable! Thanks

Mar 4th, 2002, 12:26 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Why don't you have more columns and fewer rows?

Mar 4th, 2002, 12:29 PM	#3
keith Board Regular Join Date: Mar 2002 Posts: 88	Not sure what you mean by more columns.. basically there are blank cells under the date column where the multiple items are listed.. so there may be something in the date colum every 5 rows or so.. see what I mean?

Mar 4th, 2002, 12:33 PM	#4
keith Board Regular Join Date: Mar 2002 Posts: 88	(I am one who posted the orignal just forgot to log in)

Mar 4th, 2002, 12:50 PM	#5
Guest Posts: n/a	ok here goes.. there are 15 total items. During a year these items are on off and on promotion a week at a time. The list needs to be organzied by date(which is actually "week of"). On a given date any combination of the 15 items can be on promotion. The user fills out a form that adds to the list based on a week. Here goes: Week of - Item # - Price - Cost - Profit % 3/1 - 0101 - 2.99 - 1.50 - x% - 0102 - 3.99 - 1.50 - x% - 0108 - 4.99 - 1.50 - x% 3/7 - 0001 - 2.99 - 1.50 - x% - 0105 - 3.99 - 1.50 - x% ect... if there is a better way to do this I am all ears.. I just don't want to have to carry the date down every item ie - I would like 3/1 and all the items under it to be a single record. 3/7 starts a new record etc.. thanks..

Mar 4th, 2002, 12:55 PM	#7
keith Board Regular Join Date: Mar 2002 Posts: 88	(sorry forgot my name again.. all anonymous above is me)

Mar 4th, 2002, 01:05 PM	#8
keith Board Regular Join Date: Mar 2002 Posts: 88	ok - I think that works good enough- at least it stops the user from having to repeat the date in the form.. thanks!

Mar 4th, 2002, 01:10 PM	#9
keith Board Regular Join Date: Mar 2002 Posts: 88	uhoh - now I have to figure out how to include that formula in the records that are added by the form!!! The macro I was planning was going to do an "EndDown" to get to the bottom then add the data from the form any help?

Mar 4th, 2002, 03:03 PM	#10
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Excel's built-in Data form (see the Data \| Form... menu command) already handles the replication of the formula.