stevecasper Posted August 12, 2009 Report Share Posted August 12, 2009 Hey gurus. I have a not-very-MEP-related question, but since I know many, if not most, of the posters on this forum are savvy on numerous applications, I was hoping to get a really quick Excel assist. I am building a relatively basic spreadsheet, but I'm having a logistical problem. Here's a very simple example of what I'm trying to do: Sheet 1, Cell A1 = 100 Sheet 2, Cells A1:A300 = Sheet 1, A1 In other words, I need each cell of a 300 cell long column in Sheet 2 to match the contents of Sheet 1, A1. The simple answer is just to drag-duplicate the entire Sheet 1 A column and then drag-duplicate the Sheet 2 A column. The problem with that is that I have other information in the A column that I can't move out of the way (not conveniently, anyway), and also, because I want to be able to manipulate all the information in Sheet 2 by simply making a change to Sheet 1, A1. Traditionally, I've just copy and pasted the =Sheet1!A1 text into each cell that needs it, but for a column 300 cells (or more) it is an ugly burden that I'd like to avoid. So if there is a trick to be able to paste =Sheet1!A1 into a range of cells all at once, I'd be grateful for the knowledge. Thank you for being patient with my non-MEP request. Worse comes to worst, I'll build a macro that does: Repeat start = 300 times Text Type =Sheet1!A1 arrow down Repeat End Hey look! That's ME related! phew! Quote Link to comment Share on other sites More sharing options...
eric123 Posted August 14, 2009 Report Share Posted August 14, 2009 Hey gurus. I have a not-very-MEP-related question, but since I know many, if not most, of the posters on this forum are savvy on numerous applications, I was hoping to get a really quick Excel assist. I am building a relatively basic spreadsheet, but I'm having a logistical problem. Here's a very simple example of what I'm trying to do: Sheet 1, Cell A1 = 100 Sheet 2, Cells A1:A300 = Sheet 1, A1 In other words, I need each cell of a 300 cell long column in Sheet 2 to match the contents of Sheet 1, A1. The simple answer is just to drag-duplicate the entire Sheet 1 A column and then drag-duplicate the Sheet 2 A column. The problem with that is that I have other information in the A column that I can't move out of the way (not conveniently, anyway), and also, because I want to be able to manipulate all the information in Sheet 2 by simply making a change to Sheet 1, A1. Traditionally, I've just copy and pasted the =Sheet1!A1 text into each cell that needs it, but for a column 300 cells (or more) it is an ugly burden that I'd like to avoid. So if there is a trick to be able to paste =Sheet1!A1 into a range of cells all at once, I'd be grateful for the knowledge. Thank you for being patient with my non-MEP request. Worse comes to worst, I'll build a macro that does: Repeat start = 300 times Text Type =Sheet1!A1 arrow down Repeat End Hey look! That's ME related! phew! You can in Sheet2, select A1:A300, entry an array formula, =Sheet1!$A$1, then press CTRL+SHIFT+ENTER. Quote Link to comment Share on other sites More sharing options...
stevecasper Posted August 15, 2009 Author Report Share Posted August 15, 2009 You can in Sheet2, select A1:A300, entry an array formula, =Sheet1!$A$1, then press CTRL+SHIFT+ENTER. Eric... you are a genius! Lifesaver! I did end up just building a macro that did exactly as I explained in my original post, but this information will save me the trouble when I need to build/modify spreadsheets in the future. Thank you VERY much! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.