Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this community
I know this formula: ='Sheet1'!A2
When pasted on Sheet2, it will put the contents from Sheet1's A2 cell into the Sheet2 cell that holds this formula.
I can drag the formula to the subsequent cells.
Without doing the drag, is there any way to dynamically (based on number of rows in the Sheet1) mimic the cells in Sheet2 to have the values of the column from Sheet 1?
Not sure if my understanding is correct, you can try this formula:
=INDIRECT("'"&CELL("filename")&"'!"&SUBSTITUTE(ADDRESS(1,COLUMN(A1),4),"1","")&ROW(A1))
What you are doing is making a linked cloned worksheet.
I believe your biggest issue with "dragging" the formula is not really the dragging but the fact the it doesn't clone well when you insert and delete, or add new rows. In the above answer I detail how you can use INDEX to maintain the clone even if you insert or delete rows in the master. And because you return "" for blank cells, you can easily extend the formula as far and wide as you need it and it will auto-populate with new data.
That being said, Excel 365 offers "dynamic arrays with spilling". This mouthful basically means you can enter a multi-cell formula or range in a single cell, and the answer will automatically spill over into adjoining cells. You can thus clone a whole sheet by putting a single formula in Sheet2!A1
=Sheet1!$A:$XFD
However this is a very bad idea and will slow down your spreadsheet, as it returns 0's for every empty cell in the whole sheet1 (max rows x max columns). In order to only clone what is in use (with say the max boundary define by row 1 length x column 1 length):
=Sheet1!$A$1:INDEX(Sheet1!$A:$XFD,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
This will function a lot better but even referencing up to column XFD might result in issues. Perhaps limit it $IV (max columns in Excel 2003 and earlier). Also you will still see plenty of zeros where sheet1 had blanks, so instead do:
=LET(usedrange, Sheet1!$A$1:INDEX(Sheet1!$A:$IV,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)), IF(ISBLANK(usedrange), "", usedrange))
The following will work nicely and has the feature that it only returns rows with something in column A:
=FILTER(Sheet1!A2:A31, Sheet1!A2:A31<>"")
Since you ask for only column A... Edit the A31 as needed to suit the rows you need and also, if you really need more columns than just A, edit the column label part as well.
The problem is in the end point. Let's say the last used row is (presently) row 20,000. You can simply use that for the row number edit, if you like, and it will handle row insertions and deletions just fine as Excel does the keeping track of those effects. But it won't handle someone simple typing the next entry in row 20,001, and so on.
One can get past that by specifying some suitably larger value, say A25000, so as to allow for a lot of that before it is an issue again. Of course, the farther down one goes like that, the longer it will be till reaching it and the likelier one is to forget about that... or remember to pass it on to the person taking it over when one gets promoted.
Before the next part, I should mention that the function mentioned a lot can be used in two ways in this kind of problem. One is to use it to build a string that is the range address you need, then give that to INDIRECT() to make it a real boy. This is not an issue nowadays given memory and speed in modern machines, but pile it up in a HUGE number of cells, and the fact it recalcs every time anything changes and you could see some actual slowdown, even today. 100,000 rows and 30 columns bogs me down to taking a noticeable second or so lag, but at 20 columns, nothing apparent. Still, a consideration. The other way is to use it in a function like INDEX() as Mobus does. INDEX() is not volatile so it only recalcs when Excel thinks it needs to.
FILTER() is happy to have either present it with the range you require. So probably you'd prefer INDEX().
Or, avoid either way by using the approach I recommend at the end.
The way of choosing an end point for the range COULD seem to be to use something like COUNTA() to give you a dynamic and accurate number, even adding some amount to it to accommodate any Excel "funny business" on the matter. But... if blank cells are possible, you immediately start seeing lines from the bottom not being presented on your output sheet. So, rows 2-2,001 have entries and COUNTA() gives 2,000. And immediately your formula is only returning things from the range A2:A2000, not A2:A2001. Again, adding some suitably (seemingly anyway) high value to that would cover you for a time, but eventually, the problem would occur.
(Naturally, if blanks are not possible, it is not a problem.)
There is no function in Excel to return the last used cell in a row or column. There are plenty of workaround approaches, but nothing is surefire. Adding columns complicates that issue. Even "polling" the columns involved with MAX(COUNTA(one column after another, then MAX choosing the highest value returned)) does not assure success if blanks are involved: The blanks do not all have to be in the same rows. So A1:B5 can have in column A a, blank, d, blank, g and give 3 while column B might have blank, a, blank, blank, blank and give 1. Neither is the right value as 5 was needed. Approaches like "textjoining" all the cells in a row and presenting that to COUNTA() run into problems with the limits on internal array length in Excel's formulas (not the row textjoining, probably, but when the array of all those rows is put together for COUNTA() to count from).
Personally, I would just give the range used by FILTER() a suitably higher number, maybe even get a little fancy and just multiply its result by some factor like 1.25, or whatever seems suitable to your needs for the project in hand. (ROUND(xxx,0) of course, to get an integer!)
To really solve your problem, you can use the gold standard: VBA. Either a full-blown, do much/all kind of macro, or just a UDF that uses VBA to find that true LastCell, make a range of it and the known starting cell, and give that to the formula. VBA is almost always appreciably faster than Excel formulas and if you fashion the whole thing in VBA, you just need the UDF. Though it'd have to be volatile to update as changes occur, so the VBA code would run lots more than the formula calculates... might be very "pound foolish"...
