I dont want to have to do a macro
 

I have 2 spreadsheets that need to be linked. This is what I need:
Spreadsheet A has a basic log of information. The information is logged in
rows which each have an assigned number. This number needs to be linked to
spreadsheet A. However; Each time information in logged into spreadsheet A,
the formula needs to pick up the NEXT BLANK CELL's assigned number.

I dont want to have to do a macro
 

How is the blank cell assigned a number?

Do you want the row number of the first blank cell returned?

=MATCH(99^99,A:A) + 1 if data is numerical

=MATCH(REPT("z",255),E:E) + 1 if data is textual


Gord Dibben MS Excel MVP


On Tue, 11 Aug 2009 12:37:01 -0700, CHOUSE - UCITY <CHOUSE -
wrote:

I have 2 spreadsheets that need to be linked. This is what I need:
Spreadsheet A has a basic log of information. The information is logged in
rows which each have an assigned number. This number needs to be linked to
spreadsheet A. However; Each time information in logged into spreadsheet A,
the formula needs to pick up the NEXT BLANK CELL's assigned number.

I dont want to have to do a macro
 

For Example, this is what the Row looks like that I need the number from:

P09-10-043 8/10/2009 6311 Clemens
P09-10-044 8/11/2009 7057 Julian
P09-10-045

The row that contains P09-10-045 is what I need to "pop up" in the 2nd
spreadsheet. These are just typed in and are not formulas or anything.

What I need is a formula in the 2nd spreadsheet that gives me the next
number available. Then, once information is entered into that cell, it will
automatically go to the next number.

Is that a better explanation?

This was done once before and the formula was lost.




"Gord Dibben" wrote:

How is the blank cell assigned a number?

Do you want the row number of the first blank cell returned?

=MATCH(99^99,A:A) + 1 if data is numerical

=MATCH(REPT("z",255),E:E) + 1 if data is textual


Gord Dibben MS Excel MVP


On Tue, 11 Aug 2009 12:37:01 -0700, CHOUSE - UCITY <CHOUSE -
wrote:

I have 2 spreadsheets that need to be linked. This is what I need:
Spreadsheet A has a basic log of information. The information is logged in
rows which each have an assigned number. This number needs to be linked to
spreadsheet A. However; Each time information in logged into spreadsheet A,
the formula needs to pick up the NEXT BLANK CELL's assigned number.

I dont want to have to do a macro
 

You cannot have a formula in a cell then enter data on top of it.

You will wipe out the formula.

To retrieve the text string P09-10-045 enter this formula wherever you want
on sheet2.

=LOOKUP(2,1/(Sheet1!A1:A65535<""),Sheet1!A1:A65535)

Assuming column A is text string column.

But don't overwrite it.


Gord

On Tue, 11 Aug 2009 13:34:01 -0700, CHOUSE - UCITY
wrote:

For Example, this is what the Row looks like that I need the number from:

P09-10-043 8/10/2009 6311 Clemens
P09-10-044 8/11/2009 7057 Julian
P09-10-045

The row that contains P09-10-045 is what I need to "pop up" in the 2nd
spreadsheet. These are just typed in and are not formulas or anything.

What I need is a formula in the 2nd spreadsheet that gives me the next
number available. Then, once information is entered into that cell, it will
automatically go to the next number.

Is that a better explanation?

This was done once before and the formula was lost.




"Gord Dibben" wrote:

How is the blank cell assigned a number?

Do you want the row number of the first blank cell returned?

=MATCH(99^99,A:A) + 1 if data is numerical

=MATCH(REPT("z",255),E:E) + 1 if data is textual


Gord Dibben MS Excel MVP


On Tue, 11 Aug 2009 12:37:01 -0700, CHOUSE - UCITY <CHOUSE -
wrote:

I have 2 spreadsheets that need to be linked. This is what I need:
Spreadsheet A has a basic log of information. The information is logged in
rows which each have an assigned number. This number needs to be linked to
spreadsheet A. However; Each time information in logged into spreadsheet A,
the formula needs to pick up the NEXT BLANK CELL's assigned number.