0

This may be a little confusing to understand but in a simple word, I want to be able to extract selective data from Sheet2 into Sheet 1.

Example. in Sheet1 Cells A8:A50 are empty, I want to be able to either type in a sheet (name) into one of those columns and the sheet to extract data from Sheet2

UPDATED: Explanation with Images.

enter image description here

Here in the image one, you can see I am inside my Overview tab (Main Tab). I have selected a box around CELL A8. I would like to be able to type the Sheets name into CELL A8 from the Sheets I already own "AAPL", "DIS" or have the entire row automatically update every time a new Sheet is made.

enter image description here

Let's say, I typed AAPL inside CELL A8, I want the formula to automatically head inside the AAPL tab and grab content from there and bring it into the Overview TAB and paste it inside the box below the "Company Name"

enter image description here

Something like this, I know it's possible but it's beyond my ability and in need of some guidance on how to get this complete.

Update: A gentleman said this is possible using Google Script. I am new to the script side of this - anyone able to recreate something as to what I am asking I'll send you £10 via cashapp or PayPal as part of an appreciation for your effort. Unfortunately, I cannot share the script as it's my main stock portfolio.

Second Update: I have discovered the beautiful but yet simple function "=Sheet2!A1" - That grabs any information for Sheet2 and brings it straight into that Cell. With a little bit of creativity, it's working pretty well so far.

  • Welcome. Please read how to and share a test sheet so as you can be easier helped. – marikamitsos May 15 at 23:31
  • 1
    What you are asking is posible by using Google Apps Script.Please checkout developers.google.com/apps-script/guides/sheets then do some search/research. If you still need help, please edit the question to add a brief description of your search/research efforts as is suggested in How to Ask. – Rubén May 16 at 0:08
  • You should be aware that by choosing to share your spreadsheet, your email address will be unavoidably exposed. – Tedinoz May 16 at 0:42
  • You should also be aware that by signing up to stackexchange one's email is also unavoidably exposed to every stranger having access to your account details. And also to the public once the site is compromised. Same as when someone signs up in any site (Facebook, Pinterest, Vimeo, GitHub, myphonebill.com, myinsurance.com, sitexyz.com etc). To avoid all the horror and retain your anonymity, you could use a great -and wisely, widely spread- alternative of using a "throw-away" account. – marikamitsos May 16 at 6:34
2

I note that you have recently discovered =Sheet2!A1. This suggests that you are fairly new to spreadsheets and some allowance is justified in terms of the research that you might do and assist you.

There are several ways that you might achieve your goal, consider this answer as an example of one method.

As noted by @Rubén, what you are asking is possible by using Google Apps Script. However, for sheet 1, your degree of effort is really driven by how many companies you have in your portfolio. If it is less than, say, 20, then using formula may be more sufficient.

You will find it productive to research three things:

  • the ADDRESS function Doc Ref, which returns a cell reference as a string,

  • the INDIRECT function Doc Ref, which returns a cell value specified by a string,

  • the difference between absolute/relative cell references when entering a formula Doc Ref.

Used together, these will provide most of the information you need on sheet 1.

Formula to use:

  • in cell A2 of Sheet#1, enter the sheet name of Sheet#2 (APPL)

  • in cell A3 of Sheet#1, enter this formula.

    =indirect(address(1,4,1,1,$A2))
    address returns "APPL!$D$1" = cell D1 (absolute reference) on sheet "APPL". This cell contains the Ticker reference.
    indirect returns the ticker value of "NASDAQ:APPL"

  • in cell A4 of Sheet#1, enter this formula.

    =indirect(address(1,5,1,1,$A2))
    address returns "APPL!$E$1" = cell E1 (absolute reference) on sheet "APPL". This cell contains the Company name
    indirect returns the ticker value of "Apple"

So far as the number of shares and average value are concerned, I suggest that you create a row at the top of APPL that picks up these values from the sheet; you can then also use address/indirect to display those values on Sheet 1

| improve this answer | |
  • 2
    Something else to mention is the use of arrays in Google Sheets i.e. ={Sheet2!A1:B,Sheet2!C1:F} – Rubén May 16 at 4:34
  • 2
    Aurimas,@Rubén makes a very good point. Arrays provide ways to arrange and collect data that may not be obvious at the outset. This is the Doc Ref for arrays. Personally, I still struggle with arrays (though I am a slow learner) but there are many source of solutions on Webapps and Stackoverflow. – Tedinoz May 16 at 4:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.