Excel: Referencing a whole column from another sheet?

Soldato
Joined
20 Oct 2002
Posts
17,854
Location
London
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!
 
Soldato
Joined
13 Nov 2006
Posts
23,712
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?
 
Soldato
OP
Joined
20 Oct 2002
Posts
17,854
Location
London
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 :o
 
Soldato
Joined
18 Oct 2012
Posts
8,332
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)
 
Associate
Joined
24 Jun 2008
Posts
1,168
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
 
Back
Top Bottom