Select the last two names in google sheet

0
votes
  1. In my data sheet1, I want to use the 'Name in use' column (B) with the last two names with ARRAYFORMULA. 
  2. In my data sheet2, I want column (B) to be repeated with the same code next to the column A get filled 

Please find the sample date sheet below  https://docs.google.com/spreadsheets/d/1_AWRjexJNAcgNGsmrBKU_8JYL03UbAGeiyy4oI8B9fU/edit?usp=sharing   Regards, Nimal PereraSri Lanka

1
google-sheets

1 Answers

0
votes

Sheet 1

Your formula seems fine, just the ArrayFormula() needs tweaking. As noted in the docs, ArrayFormula() takes in a range, rather than a single cell. You would have to do something like this:

=ARRAYFORMULA(IFERROR(UPPER(TRIM(RIGHT(SUBSTITUTE(A2:A5," ",REPT(" ",60)),120)))))

Essentially you type in the range you'd like it to apply on. So at any moment, instead of selecting a single cell for that row, you select the range. If you'd want to apply it to the entire column, use A2:A. You even did it right on sheet2

Sheet 2

Your names seem to be importing correctly once Sheet1 is done right. To have the same code everywhere, use something like this:

=ARRAYFORMULA(IF(LEN(A2:A),"SSD",""))