Hi All,


What I am trying to do, I think should be simple, but I cannot figure
out a good way to do it.

I have file 1 which is set up like so:
Col A Col B
5000 product 1
5001 product 2
5002 product 3

Basically, any time someone enters a product in column B, column A
gives the next highest integer. If there is no data in Column B, then
column A for that row contains a 0.

I have a second file which has the same set up, but with different
products, and the numbers start with 6000.

I want to set up a new file that will pull in the number from column a,
unless it is a 0. then move to the second file, until it hits a zero
there, then stop.

In other words, the new file would look like:

5000
5001
5002
5003 <--- since this is the last number before 0, the next number
6000 would pull from file 2, and stop when it hit zero in
that file.
6001
6002

Any ideas on how I could set that up in the new file, so that if
someone enters a new product, my file would update?


--
HouseofRahl
------------------------------------------------------------------------
HouseofRahl's Profile: http://www.excelforum.com/member.php...o&userid=25161
View this thread: http://www.excelforum.com/showthread...hreadid=563858

I only did this with data on other sheets, not other workbooks, but the basic
idea should work the same (I think. I avoid linking workbooks like the
plague.) I set up dummy data like you described, starting in A3, on Sheet4
(5000 series) and Sheet5 (6000 series). On Sheet6, in cell A3, I entered the
following formula, then copied it down:

=IF(Sheet4!A3=0,IF(OFFSET(Sheet5!A3,-COUNTIF(A2:A$3,"<6000"),0)=0,0,OFFSET(Sheet5!A3,-COUNTIF(A2:A$3,"<6000"),0)),Sheet4!A3)

(That's all one formula. It wrapped here because of its length). This
produced the dynamically updated combined list you requested. I recommend you
try it with worksheets first, then with your multiple workbooks.

Hope this helps,

Hutch

"HouseofRahl" wrote:


Hi All,


What I am trying to do, I think should be simple, but I cannot figure
out a good way to do it.

I have file 1 which is set up like so:
Col A Col B
5000 product 1
5001 product 2
5002 product 3

Basically, any time someone enters a product in column B, column A
gives the next highest integer. If there is no data in Column B, then
column A for that row contains a 0.

I have a second file which has the same set up, but with different
products, and the numbers start with 6000.

I want to set up a new file that will pull in the number from column a,
unless it is a 0. then move to the second file, until it hits a zero
there, then stop.

In other words, the new file would look like:

5000
5001
5002
5003 <--- since this is the last number before 0, the next number
6000 would pull from file 2, and stop when it hit zero in
that file.
6001
6002

Any ideas on how I could set that up in the new file, so that if
someone enters a new product, my file would update?


--
HouseofRahl
------------------------------------------------------------------------
HouseofRahl's Profile: http://www.excelforum.com/member.php...o&userid=25161
View this thread: http://www.excelforum.com/showthread...hreadid=563858

I know what you mean about linking workbooks, unfortunately, the first
two workbooks are from two different people deadset against change.

Your formula worked very well, thank you. I was trying to work it out
with offset, but was unable to put it all together.


Tom Hutchins Wrote:
I only did this with data on other sheets, not other workbooks, but the
basic
idea should work the same (I think. I avoid linking workbooks like the
plague.) I set up dummy data like you described, starting in A3, on
Sheet4
(5000 series) and Sheet5 (6000 series). On Sheet6, in cell A3, I
entered the
following formula, then copied it down:

=IF(Sheet4!A3=0,IF(OFFSET(Sheet5!A3,-COUNTIF(A2:A$3,"<6000"),0)=0,0,OFFSET(Sheet5!A3,-COUNTIF(A2:A$3,"<6000"),0)),Sheet4!A3)

(That's all one formula. It wrapped here because of its length). This
produced the dynamically updated combined list you requested. I
recommend you
try it with worksheets first, then with your multiple workbooks.

Hope this helps,

Hutch

"HouseofRahl" wrote:


Hi All,


What I am trying to do, I think should be simple, but I cannot
figure
out a good way to do it.

I have file 1 which is set up like so:
Col A Col B
5000 product 1
5001 product 2
5002 product 3

Basically, any time someone enters a product in column B, column A
gives the next highest integer. If there is no data in Column B,
then
column A for that row contains a 0.

I have a second file which has the same set up, but with different
products, and the numbers start with 6000.

I want to set up a new file that will pull in the number from column
a,
unless it is a 0. then move to the second file, until it hits a zero
there, then stop.

In other words, the new file would look like:

5000
5001
5002
5003 <--- since this is the last number before 0, the next number
6000 would pull from file 2, and stop when it hit zero in
that file.
6001
6002

Any ideas on how I could set that up in the new file, so that if
someone enters a new product, my file would update?


--
HouseofRahl

------------------------------------------------------------------------
HouseofRahl's Profile:
http://www.excelforum.com/member.php...o&userid=25161
View this thread:
http://www.excelforum.com/showthread...hreadid=563858




--
HouseofRahl
------------------------------------------------------------------------
HouseofRahl's Profile: http://www.excelforum.com/member.php...o&userid=25161
View this thread: http://www.excelforum.com/showthread...hreadid=563858