1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Excel: Referencing a whole column from another sheet?

Discussion in 'HTML, Graphics & Programming' started by Scam, Nov 21, 2018.

  1. Scam

    Capodecina

    Joined: Oct 20, 2002

    Posts: 12,248

    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!
     
  2. dl8860

    Wise Guy

    Joined: Jul 25, 2010

    Posts: 2,420

    Location: Surrey

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

    Didn't read OP properly
     
  3. wingman

    Mobster

    Joined: Dec 27, 2011

    Posts: 4,348

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

    Suspended

    Joined: Nov 13, 2006

    Posts: 16,137

    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?
     
  5. Scam

    Capodecina

    Joined: Oct 20, 2002

    Posts: 12,248

    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
     
  6. Tingle

    Wise Guy

    Joined: Jul 2, 2010

    Posts: 2,338

    VBA or drag it the full way down.
     
  7. adolf hamster

    Soldato

    Joined: Oct 18, 2012

    Posts: 6,729

    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)
     
  8. dirtychinchilla

    Sgarrista

    Joined: May 2, 2011

    Posts: 8,390

    Location: Woking, Surrey

    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.
     
  9. SimonCHere

    Wise Guy

    Joined: Jun 24, 2008

    Posts: 1,116

    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