Extract Data from Sheeet2 and into Sheet1 (Tabs instead of actual spreadsheets)

Question

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.

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.

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"

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.

Answer 1

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