Hi there, Hopefully someone has a clue, as I don't I am trying to populate a sheet with data from another sheet In sheet 1 I have a column of calculated cell positons using address function and then I have a piece of data that I want to put in that cell reference on another sheet, it is probably really easy but for the life of me I am not finding the appropriate help item to point me in the right direction. Any ideas An example of what I am trying to do. Sheet one has source data Code: col1 col2 row1 D12 monkey row2 AH12 bananas With this info I would like to automagically fill a new sheets cell D12 with monkey and AH12 with bananas, seems like it should be easy but I am not a big Spreadsheet user and the solution is evading me. Thanks in Advance, Sandy
Have you looked into the INDIRECT function. Should be able to take your D12 text as input. Not sure that's exactly what you want to do but it might help.
I've been playing with that but haven't managed to get it to work the way I would like, I'm probably doing it wrong.
Place the cursor in the cell where you want the data copied Press the = key Navigate to the cell you want to reference click in it and press enter. Make sure the 'Update remote references' option (or similar depending on version) is selected in Options.
The contents are coming in from a script, so the contents of the columns will differ hence calculating the row and column I want to put the data into so I can't use = here. Vlookup appears to be the command I want but I can't get it to work. So I changed col1 to absolute addresses then tried something like the following in each cell on sheet2 adapting for example Vlookup(cell("address",a1),'Sheet1'!a1:b2,2) Using cell to feed in address to match to This did bring some text from the other sheet but only 1 label and not the 100 or so I want to populate. So I'm still at a loss. I went this route as I thought it would be easy, I can probably write some code to create a correctly formatted spreadsheet but I was trying to resist that.
Excel function will solve to provided data in the cell that the function has been called from. They won't send data to another cell. VBA could create a script that directs output to a given cell. In order to generate the result you want without VBA you'll need to use something similar to the following code in every single cell that is to be populated. Code: =VLOOKUP(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$",""),Sheet1!$A$1:$B$2,2,FALSE) Since ADDRESS(ROW(),COLUMN()) will return $D$12, I add the SUBSTITUTE function to strip off the unwanted "$". I have not check to see how the function behaves when in R1C1 Notation. As the source is from a script, it might be best just to modify the script to insert the data in the correct location.
Thank you that has done it Following your lead it seems the VLOOKUP command I was using would have also worked if I had the FALSE bit on range lookup as you had done, I don't understand the significance of that yet, perhaps I never will, I don't really need to touch spreadsheets normally. It has brought up some other issues where I have pasted it to cells that are not in the data set but once that is cleared up it going to look great. EDIT - Used IFERROR to clear up the cells with no data, all looks awesome now.
The final option of VLOOKUP parameters is approximate match. If set to TRUE or omitted, the function will return the closest match if set to FALSE it will only return an exact match. The approximate match can be finicky to use, first your list must be sort in ascending order by the first column of the reference array. I don't have a good grasp on how excel compares strings. I do know with numbers it will return the last value in the array which is less than or equal to the lookup value. For example if you have the following array in the first column: {1,2,4,3} and the look up value is 3 and approximate match is set to TRUE, the VLOOKUP function will return the data in the second row and not the fourth. I suspect that when approximate match is True, VLOOKUP stops looking as soon is reaches a value that is greater than the lookup value. IFERROR is easy way to clean up after many functions.
