Excel: Referencing a whole column from another sheet?

Scam · 21 Nov 2018 at 13:50

So, I have two sheets in the same workbook. One that looks nice for users to add details (first name, last name, email address etc.) and another that takes that info and puts it into a non-formatted style so it's ready to be exported into a simple CSV to import into a bit of software. It does other stuff like creates usernames etc too.

Anyway, I've managed to take the a cell reference from the first sheet into the second sheet by using the below formula;

Code:

=PROPER('Sheet1'!B11)

This works fine if on my second sheet, I drag down the formula to fill as many rows as I can be bothered to drag down to.

Seen as I don't know how many rows the user is going to fill in on Sheet1, my question is how do I mirror the rows from Sheet1 regardless if they fill in 3 rows, or say 150? :confused:

Or is dragging the formula down for miles on the second sheet, the only way? :confused:

It seems a little messy, is all.

Thanks!

dl8860 · 21 Nov 2018 at 13:53

~~Just click the actual column header. Or you can type in =PROPER('Sheet1'!B:B)~~

Didn't read OP properly

wingman · 21 Nov 2018 at 14:00

Think I know what you mean/need. I've dropped you a trust.

GarethSouthgate · 21 Nov 2018 at 15:15

Either VBA or use an IF statement? IF statement might come a cropper if there are blanks between filled cells though.

Set a formula to count the number of cells populated and use that as the cell reference value.

Dragging the formula down is surely the easiest option?

Scam · 21 Nov 2018 at 17:25

Ack sorry I can't send the sheet as it contains email addresses etc. and I can't be faffed wiping it all. I've actually been tasked with migrating this to our online Google Sheets-esque corporate platform now which means probably half of this stuff won't work

TNGL · 21 Nov 2018 at 19:04

VBA or drag it the full way down.

adolf hamster · 22 Nov 2018 at 08:36

what you can do to save time is use the fill>series to create a numbered list in one column, then put your formula in the column next to it and ctrl+double click on the bottom right hand dot, it'll then automatically copy the formula down for as long as there's data in the first column (which could be to the spreadsheet limit if you like), you can then delete the first column if required.

this is assuming that you don't need to worry about the formula returning values if it's feeding cell on the first sheet it blank, if so then you can use =if(isblank(cell on first sheet),"",your formula) so that the empty references will return "" which is essentially nothing (although it might still show up in csv conversion, not sure on that)

dirtychinchilla · 22 Nov 2018 at 13:43

I don't fully understand your problem, but if there's no reason that the selection can't contain empty cells, just name the range and be done with it.

SimonCHere · 27 Nov 2018 at 16:28

Well you don't need to drag, but you do need to formula in all the cells on sheet 2 to make it work.
So just write it in the top cell say A1 to make sure it works.
Copy the cell A1,
Select the entire column by clicking on the column heading (A) at the top
Right click on one the selected cells in column A and choose "Paste Special" -> "Formulas"
All the cells in column 1 will now have the formula referencing the cells in sheet 1

simon