move data with text identifiers

[arlie]

Please help!!! How can I get data from an initial data sheet to move to its proper column in an in-between sheet, with text as the column identifier?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

My goal is to convert a data sheet that imports numbers daily, into a fixed version of this sheet. I need to create this in-between sheet so that office staff can use their daily reports or templates. These reports will have formulas that will add different columns of data for sales info and comparisons.<o></o>
This ‘initial data sheet’ does not always have all our product columns because when there are no items sold of a particular product, the zero data column won’t pull in. Therefore, the column index number for each product is always changing on this initial sheet. So, then i must identify the column heads by the text heading or the product identity. Right?
I am attempting to create an ‘in-between sheet’, a ‘fixed’ sheet with all our columns of product. The ‘initial data’ would be told to move the product counts into their proper columns on the ‘in-between sheet.’ And reports/templates would be created, reusable, with constant formulas and all. <o></o>
<o></o>
I was being trying to use Hlookup and Match to accomplish this, but am lost.<o></o>
There are 121 columns of products, all identified by text in the column heads, and 21 rows of 'storefront locations.' Example follows:<o></o>
<o></o>
1. Initial data sheet: ( No product3 sold this week, so no column of data)<o></o>
storefront, product1, product2, product4, product5, product6,<o></o>
maysville, 23, 17, 21, 22, 19,<o></o>
kenton, 35, 12, 0, 21, 6<o></o>
tarten, 26, 0, 24, 15, 9<o></o>
<o></o>
2. In-between sheet: (With a column for product3)<o></o>
storefront, product1, product2, product3, product4, product5, product6<o></o>
maysville, 23, 17, 0, 21, 22, 19,<o></o>
kenton, 35, 12, 0, 0, 21, 6<o></o>
tarten, 26, 0, 0, 24, 15, 9<o></o>
<o></o>
3. Report sheets have formulas referring to fixed columns from in-between to create sales comparisons, etc.<o></o>

How can i move this data, with text as the column identifier? <o></o>
<o></o>
Got the following string from examples online…<o></o>
HLOOKUP($A5, 'initial Data'!A2:IV2, MATCH("product1",A5:IV5, 0), FALSE)<o></o>
Didn’t give errors. Just zeros. <o></o>
Please can you offer me some clues or what can I read on this or can this be done? I am not sure what I have done is even on the right path. Or what other options are. Does this scenario make sense? Thanks very much, j<o></o>
<o></o>

 

[Fowmy]

Try this formula:

=IFERROR(INDEX(Sheet3!$A$1:$F$4,MATCH($A2,Sheet3!$A$1:$A$23,0),MATCH(Sheet4!B$1,Sheet3!$A$1:$J$1,0)),0)

I put the orginal table this way

Excel Workbook
	A	B	C	D	E	F
1	storefront	product1	product2	product4	product5	product6
2	maysville	23	17	21	22	19
3	kenton	35	12	0	21	6
4	tarten	26	0	24	15	9
Sheet3

Excel 2010

 

[arlie]

Thank you!
Will this work if the data changes daily? The $A$23, I mean.
Am working on your suggestion. Can't quite grasp it yet. Am up looking IfError, Index and Match to understand this.

 

[arlie]

OK. Wrapped my brain around it. All works smoothly thanks to your advice!
Thanks again for the help. j