Formula find last used cell and change referece

[Zaeguzah]

Hey guys. I'm trying to do something here that i don't if it can be just by a formula. Please bear with me as I don't know how to explain this.
I have two columns of names of contiguos names. I want to copy all of them in a new column using a formula that copies the first column and when it reaches a blank cell only then move to the second column.
So basically, a formula that copies a whole column up to a blank cell and then copy the second column.

Sorry if I wasn't clear. I'll rephrase if necessary...
Thanks for all your replies and BIG help.

[martindwilson]

1. why?
2 post a workbook with what you have and what the end result should look like

[Chemistification]

Not sure if this is what your after but from your description it looks like it fits the bill. Let me know if its not

[Zaeguzah]

Done. Thanks for looking in to it.

[Zaeguzah]

Not sure if this is what your after but from your description it looks like it fits the bill. Let me know if its not

Something like that except the blanks cells are never up only after the used cells. And they are contiguos meaning it can't be a name and then blank and then another name etc. Your formula works but When the first columns reached a blank, I need it to get the values from the top of the second column. Like IF cell Z40 is blank -> go to second column and copy from AA1. As an example...

[Chemistification]

Im struggling on this one. I know what you mean but having trouble sorting it out. Any one else fancy having a pop?

[martindwilson]

will there be anything after the blank in the first column? are the cells numbers or text?

[Zaeguzah]

No, both columns are filled up with formulas that return blank if requirements aren't made. And no, nothing comes after the blanks. Both of them are general formatted.

[martindwilson]

try this uses match diferent versions for text/numbers

[Zaeguzah]

I'll need some time to play with as now it's not working right. On your wb it works fine but in mai main one between the ending of the first column and begining of the next it displays 15 cells or so filled with "0" and only after those cells appers the second column. I don't know what causes it.

LE: Sorry, i forgot to mention something important. In the real workbook, the second column starts at row 16 and with the current formula, it will always display 15 cells filled with "0" between the two columns. COuld that be fixed?

[Zaeguzah]

Any update on this?

[martindwilson]

=IF(A1="",INDIRECT("b"&ROW()-MATCH(10^6,A:A,1)+15),A1)

[arthurbr]

this light help, providing you alwas have 15 rows with 0 in col B

Please Login or Register  to view this content.

[Chemistification]

=IF(A1="",INDIRECT("b"&ROW()-MATCH(10^6,A:A,1)+15),A1)

Hi,

If it doesnt take too long could someone provide some reasoning as to what the indirect and the match does in this specific formula (specifically the 10^6, im guessing that is so that it includes the entire column?). I was trying to do something similar but i was trying to use offset and couldn't figure out how to go about it.

Thanks

[Zaeguzah]

Works great Martin! Thanks man, you saved a lot of pain with this. One more thing before I mark this thread as "solved".
Can something like this be implemented in your formula for numbers?
• if cell is > 01:00 then substract one hour from it and if false, leave the cell as it is

Please Login or Register  to view this content.

LE: Thanks to all that replied. I am very grateful.

[arthurbr]

Select a cell with the formula, then

Tools - Formula auditing - Evaluate formula

Click on evaluate to see what happens

this link will show you how to find the last value in a range

[martindwilson]

i used 10^6 but more usually you'll see this as
9.999999999E+307

=MATCH(9.99999999999999E+307,A:A,1) i.e match this very big number but as it cant it finds the last one in the array instead

[martindwilson]

if cell is > 01:00 then substract one hour from it and if false, leave the cell as it is
i think it would just be simpler to use another column to do that =IF(c1>1/24,c1-1/24,c1)

[Zaeguzah]

Thanks again Martin for all your help and the paitience you had on this. Also, a warm "thank you" to all how replied and gaved a hand on the sollution of this thread. I have a lot of work now configuring this really large workbook that I'm building with these formulas. I'll see you around and happy holidays to all the members of this forum.