Forum Discussion
Formula that copys data based on a random row number
Hi Excel Community,
I am wondering if it is possible to have a formula (or set of formulas), that will copy data from a row in another spreadsheet simply by typing that row number into a cell.
The formula I am currently using to copy over the data is:
Documents\Operations\[Data.xlsx]sheet1'!$33623:$33623
I need this part $33623:$33623 (which is refering to the particular row in another spreadsheet) to change without having to write the formula out each time or manually changing the numbers within the formula.
The rows of data that I need to copy from the other spreadsheet are not in any order whatsoever and cannot be put into any sort of order.
Is this possible or is there another formula that might work?
Thank you!
You could use the INDIRECT function but the other workbook must be open!
Let's say you enter the row number in A1, the formula would then look like this:
=INDIRECT("[Data]Sheet1!$" & A1 & ":$" & A1)
Thank you so much however that formula is giving me the #REF! error...
I mucked around with it and it now works! I needed to add an apostrophe to the spreadsheet name:
=INDIRECT("'[Data]Sheet1'!$" & A1 & ":$" & A1)
My goodness, thank you so much! You have cut my work load in half!
