tbourner Posted October 12, 2011 Share Posted October 12, 2011 Is it possible to use the information in a cell as the reference to another cell? I'm guessing not but hoped there might be a workaround that didn't involve VB coding! Basically if I have say the number '5' in cell A1, can I then put a formula in cell B1 that points to a table of information and drags out the data in row 5 of that table? Quote Link to comment Share on other sites More sharing options...
tbourner Posted October 12, 2011 Author Share Posted October 12, 2011 I can use "=ADDRESS(A1,23)" to get the address for cell W5 in the above example (cell shows $W$5), but can't seem to use that in another formula? Quote Link to comment Share on other sites More sharing options...
johnny g Posted October 12, 2011 Share Posted October 12, 2011 Can you attach the spreadsheet? I'm having a tough time seeing what you mean Quote Link to comment Share on other sites More sharing options...
tbourner Posted October 12, 2011 Author Share Posted October 12, 2011 Not really, it's got lots of company confidential stuff on it! Would have to rejig it first. I think I've cracked it now anyway. It's 2 worksheets, sheet1 has a table of info from A5 down to AM106, sheet2 I want an input table from A1 to A10 where you can designate a row number, and it will source the data from sheet1 on that row (in column W in my case) and show it to you, so anyone can go in and fill in the ten slots with random row numbers and it will tell them the data from column W in all those rows (on sheet1). The formula I've now got is: =INDIRECT("'sheet1'!"&ADDRESS($A1,23)) Seems to work, I had to concatenate with the ampersand to get it to look at sheet1 as well as using the 'ADDRESS' function, so even more confusing! Quote Link to comment Share on other sites More sharing options...
johnny g Posted October 12, 2011 Share Posted October 12, 2011 I think the ampersand was your biggest friend Quote Link to comment Share on other sites More sharing options...
Scott Posted October 12, 2011 Share Posted October 12, 2011 It's fairly easy to do. You can actually get excel to write it for you by just clicking the cell you want to have the reference in, type in "=" and then click the cell you want to reference. I don't think it matters if it's a seperate sheet or workbook, it puts in the address for you. I had a sheet that did exactly what you are saying a few years ago. Quote Link to comment Share on other sites More sharing options...
tbourner Posted October 12, 2011 Author Share Posted October 12, 2011 It's fairly easy to do. You can actually get excel to write it for you by just clicking the cell you want to have the reference in, type in "=" and then click the cell you want to reference. I don't think it matters if it's a seperate sheet or workbook, it puts in the address for you. I had a sheet that did exactly what you are saying a few years ago. OK I'm confused, surely that just shows the data in whichever cell you click on after the "="? It would be easier to just find the cell and look at the data wouldn't it? I want the referenced cell to change depending on an input from a completely separate cell. Quote Link to comment Share on other sites More sharing options...
Scott Posted October 12, 2011 Share Posted October 12, 2011 OK I'm confused, surely that just shows the data in whichever cell you click on after the "="? It would be easier to just find the cell and look at the data wouldn't it? I want the referenced cell to change depending on an input from a completely separate cell. It shows the current data, it updates everytime the sheet is opened or the sheet is refreshed (can be refreshed auto or manual). Try it for a test, open up 2 seperate workbooks. Reference one to the other, save & close the one that has the formula, edit the original, save it, close it then open the one with the formula. It will now have the updated information. Works fine for me anyway. Might not be doing exactly what you are after but I would give it a try to see if it's what you need. Quote Link to comment Share on other sites More sharing options...
Scott Posted October 12, 2011 Share Posted October 12, 2011 Oh, just to add. If the sheet if both sheets are open the forumla will update live. Not sure if that will make any difference to you as I assume you just want to pull the information from the sheet without opening it 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.
Note: Your post will require moderator approval before it will be visible.