MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Need a formula to return info from a range and when that range does not have info anymore to return


Posted by Anna P. on July 19, 2000 5:58 AM

Hi there. I am sorry to bother you but I need your help to build a formula (not a macro) to do the following:

In A1:A10 I have formulas that return consecutively info (numbers or text or both) from another sheet. Not all cells in A1:A10 will return info all the time; sometimes I'll have A1=info; A2=info and the rest empty ("") or A1,A2,A3 =info and rest "" or A1 to A10=info.

In B1 to B7 I have other info.

I need in C1, C2, C3 a.s.o, a formula that will return (link) first cells with info from A1:A10 and after senses that there are no more cells with info in A1:A10 to return info from B1:B7. Let's say if

A1=33, A2=44, A3=abl12 A4=132 and
B1=78,B2=47 the results in C column will be C1=33,C2=44,C3=ab112,C4=132,C5=78, C6=47 or if

A1=47 and
B1=999,B2=am35,B3=text the results in C will be
C1=47,C2=999,C3=am35,C4=text

Thank you so much in advance.


Posted by Ryan on July 19, 0100 7:40 AM

Anna,

I figured one out! This will do the trick. Let me know how it works!

Ryan

=IF(ROW()>COUNTA(A:A),OFFSET(B$1,(ROW()-COUNTA(A:A)-1),0),A4)

Posted by Ryan on July 19, 0100 7:41 AM

Mistake

That should read:
=IF(ROW()>COUNTA(A:A),OFFSET(B$1,(ROW()-COUNTA(A:A)-1),0),A1)

Paste this in Row 1 and copy it down!

Ryan