putting a list of values into a horiz. set of formulas

[mmenashe]

I have two worksheets, a data page and another page which just reflects the
information set on the data page into various forumlas, etc

The data page is set up something like this

 <table border="0" cellpadding="0" cellspacing="0" width="216"><col style="width: 46pt;" width="61">  <col style="width: 31pt;" width="41">  <col style="width: 38pt;" width="50">  <col style="width: 48pt;" width="64">  <tbody><tr style="height: 9.75pt;" height="13">   <td class="xl24" style="height: 9.75pt; width: 46pt;" height="13" width="61"> </td>   <td class="xl24" style="border-left: medium none; width: 31pt;" width="41"> </td>   <td class="xl24" style="border-left: medium none; width: 38pt;" width="50"> </td>   <td class="xl24" style="border-left: medium none; width: 48pt;" width="64"> </td>  </tr>  <tr style="height: 9.75pt;" height="13">   <td class="xl24" style="height: 9.75pt; border-top: medium none;" height="13">Color1:</td>   <td class="xl24" style="border-top: medium none; border-left: medium none;">Blue</td>   <td class="xl24" style="border-top: medium none; border-left: medium none;">Color2:</td>   <td class="xl24" style="border-top: medium none; border-left: medium none;">Green</td>  </tr>  <tr style="height: 9.75pt;" height="13">   <td class="xl24" style="height: 9.75pt; border-top: medium none;" height="13">Car1:</td>   <td class="xl24" style="border-top: medium none; border-left: medium none;">Ford</td>   <td class="xl24" style="border-top: medium none; border-left: medium none;">Car2:</td>   <td class="xl24" style="border-top: medium none; border-left: medium none;">Chevy</td>  </tr>  <tr style="height: 9.75pt;" height="13">   <td class="xl24" style="height: 9.75pt; border-top: medium none;" height="13"> </td>   <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td>   <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td>   <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td>  </tr>  <tr style="height: 9.75pt;" height="13">   <td class="xl24" style="height: 9.75pt; border-top: medium none;" height="13">Color 3:</td>   <td class="xl24" style="border-top: medium none; border-left: medium none;">Red</td>   <td class="xl24" style="border-top: medium none; border-left: medium none;">Color4:</td>   <td class="xl24" style="border-top: medium none; border-left: medium none;">Yellow</td>  </tr>  <tr style="height: 9.75pt;" height="13">   <td class="xl24" style="height: 9.75pt; border-top: medium none;" height="13"> </td>   <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td>   <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td>   <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td>  </tr>  <tr style="height: 9.75pt;" height="13">   <td class="xl24" style="height: 9.75pt; border-top: medium none;" height="13">Car3:</td>   <td class="xl24" style="border-top: medium none; border-left: medium none;">Lincoln</td>   <td class="xl24" style="border-top: medium none; border-left: medium none;">Car4:</td>   <td class="xl24" style="border-top: medium none; border-left: medium none;">Cadillac</td>  </tr>  <tr style="height: 9.75pt;" height="13">   <td class="xl24" style="height: 9.75pt; border-top: medium none;" height="13"> </td>   <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td>   <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td>   <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td>  </tr>  <tr style="height: 9.75pt;" height="13">   <td class="xl24" style="height: 9.75pt; border-top: medium none;" height="13"> </td>   <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td>   <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td>   <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td>  </tr>  <tr style="height: 9.75pt;" height="13">   <td class="xl24" style="height: 9.75pt; border-top: medium none;" height="13">Color 5:</td>   <td class="xl24" style="border-top: medium none; border-left: medium none;">Red</td>   <td class="xl24" style="border-top: medium none; border-left: medium none;">Color6:</td>   <td class="xl24" style="border-top: medium none; border-left: medium none;">Blue</td>  </tr>  <tr style="height: 9.75pt;" height="13">   <td class="xl24" style="height: 9.75pt; border-top: medium none;" height="13">Car5:</td>   <td class="xl24" style="border-top: medium none; border-left: medium none;">MBZ</td>   <td class="xl24" style="border-top: medium none; border-left: medium none;">Car6:</td>   <td class="xl24" style="border-top: medium none; border-left: medium none;">Cadillac</td>  </tr>  <tr style="height: 9.75pt;" height="13">   <td class="xl24" style="height: 9.75pt; border-top: medium none;" height="13"> </td>   <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td>   <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td>   <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td>  </tr> </tbody></table>

and the results page is something like

  <table border="0" cellpadding="0" cellspacing="0" width="64"><col style="width: 48pt;" width="64">  <tbody><tr style="height: 12.75pt;" height="17">   <td class="xl24" style="height: 12.75pt; width: 48pt;" height="17" width="64">Combo</td>  </tr>  <tr style="height: 12.75pt;" height="17">   <td style="height: 12.75pt;" height="17">Blue Ford</td>  </tr>  <tr style="height: 12.75pt;" height="17">   <td style="height: 12.75pt;" height="17">Green Chevy</td>  </tr>  <tr style="height: 12.75pt;" height="17">   <td style="height: 12.75pt;" height="17">Red Lincoln</td>  </tr>  <tr style="height: 12.75pt;" height="17">   <td style="height: 12.75pt;" height="17">Yellow Cadillac</td>  </tr>  <tr style="height: 12.75pt;" height="17">   <td style="height: 12.75pt;" height="17">Red MBZ</td>  </tr>  <tr style="height: 12.75pt;" height="17">   <td style="height: 12.75pt;" height="17">BlueCadillac</td>  </tr> </tbody></table>

or, the formulas for the first couple are

=CONCATENATE(Sheet1!B2," ",Sheet1!B3)
=CONCATENATE(Sheet1!D2," ",Sheet1!D3)
=CONCATENATE(Sheet1!B5," ",Sheet1!B7)
..........

and so forth

the problem is that in my case, I have 4 of 5 different cell locations in each formula, and I hope there is a faster way to get it done than manually doing 250 rows

to maybe help things out, I have another worksheet that has the concatenate formulas for those cells

 <table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" span="2" width="64">  <tbody><tr style="height: 12.75pt;" height="17">   <td style="height: 12.75pt; width: 48pt;" height="17" width="64">B2</td>   <td style="width: 48pt;" width="64">B3</td>  </tr>  <tr style="height: 12.75pt;" height="17">   <td style="height: 12.75pt;" height="17">D2</td>   <td>D3</td>  </tr>  <tr style="height: 12.75pt;" height="17">   <td style="height: 12.75pt;" height="17">B5</td>   <td>B7</td>  </tr> </tbody></table>........

what can I do to create something so that I have a set of formulas I can copy paste down the line to work, instead of manually changing each entry

thanks

Matt

 

[Warship]

Can't wrap my head around this.
I would reorganize the Data to start with I think.
I don't understand the need for so many sheets.

Instead of posting your worksheet samples with "[ code ]".
download this addin:
http://www.mrexcel.com/forum/showpost.php?p=2545970&postcount=2
And post an sample sheet that way.

 

[mmenashe]

OK, ive updated, thanks for your advice

preface, the below code is a simplified example, the project im working on is a bit more complicated but same formula problem here

------------------

have two worksheets, a data page and another page which just reflects the
information set on the data page into various formulas, etc

The data page is set up something like this

Excel Workbook
	A	B	C	D
2	Color1:	Blue	Color2:	Green
3	Car1:	Ford	Car2:	Chevy
4
5	Color 3:	Red	Color4:	Yellow
6
7	Car3:	Lincoln	Car4:	Cadillac
8
9
10	Color 5:	Red	Color6:	Blue
11	Car5:	MBZ	Car6:	Cadillac
Sheet1

Excel Workbook
	A	B	C
1		Combo
2		Blue Ford
3		Green Chevy
4		Red Lincoln
5		Yellow Cadillac
6		Red MBZ
7		BlueCadillac
8
Excel 2003 and the results page is something like Sheet2

Excel 2003

Excel Workbook
Cell	Formula
B2	=CONCATENATE(Sheet1!B2," ",Sheet1!B3)
B3	=CONCATENATE(Sheet1!D2," ",Sheet1!D3)
B4	=CONCATENATE(Sheet1!B5," ",Sheet1!B7)
B5	=CONCATENATE(Sheet1!D5," ",Sheet1!D7)
B6	=CONCATENATE(Sheet1!B10," ",Sheet1!B11)
B7	=CONCATENATE(Sheet1!D10, Sheet1!D11)
Worksheet Formulas

the problem is that in my case, I have 4 of 5 different cell locations in each formula, and I hope there is a faster way to get it done than manually doing 250 rows

to maybe help things out, I have another worksheet that has the concatenate formulas for those cells

Excel Workbook
	B	C
2	B2	B3
3	D2	D3
4	B5	B7
Sheet3

Excel 2003

what can I do to create something so that I have a set of formulas I can copy paste down the line to work, instead of manually changing each entry

so perhaps, I could concantenate these cell combinations into a formula, and then copy and paste that row of formulas all the way down

I assume I would have to use a vlookup to get this working

any ideas?

thanks

Matt