Forum Discussion

Witsend_Solutions's avatar
Witsend_Solutions
Copper Contributor
May 02, 2020
Solved

Excel formula on iPad

Team.

I have a simple spreadsheet that I input shares into. It consists of a tab for a summary (Summary) followed by tabs for the individual company shares (BHP, CSL etc..).

I want the Summary tab to take the first record (2nd row, cell c2) from each. This works fine by using the =BHP!C2.

The issue with this is that when I copy, insert new row into BHP at row B2, the function =BHP!C2 changes to =BHP!C3. I need this to be static as =BHP!C2.

Appreciate your help

Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    May 03, 2020

    Witsend_Solutions To achieve that you can use INDIRECT and named formulae. 

     

    On the Formula ribbon, Define Name. Where it says "Enter name...." enter, for instance, "shBHP" (without the quotes). And where it says "Select range......" enter the formula (below) and press OK.

     

    =INDIRECT("BHP!C2")

     

    Then, on your Summary sheet, where you want to display cell C2 from BHP, enter

     

    =shBHP

     

    Now, no matter what you do in sheet BHP, the summary page will always display what it finds in C2 of that sheet, provided that the sheet exists, of course.

     

    EDIT: Wondering if you can actually do this on Excel for iPad (don't use it myself). Couldn't find how to enter named ranges/formulae on Excel for iPhone. It's just too small and inconvenient, I think. But a sheet made in regular Excel with the above method will work as intended when you open it in Excel for iPhone. So I expect it to work as well on the iPad.

    If you must create the solution on the iPad, you can enter the text BHP!C2 anywhere (e.g. in A1) on your Summary schedule and enter =INDIRECT(A1) where you need the content of BHP!C2 to show-up.

3 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    May 03, 2020

    Witsend_Solutions To achieve that you can use INDIRECT and named formulae. 

     

    On the Formula ribbon, Define Name. Where it says "Enter name...." enter, for instance, "shBHP" (without the quotes). And where it says "Select range......" enter the formula (below) and press OK.

     

    =INDIRECT("BHP!C2")

     

    Then, on your Summary sheet, where you want to display cell C2 from BHP, enter

     

    =shBHP

     

    Now, no matter what you do in sheet BHP, the summary page will always display what it finds in C2 of that sheet, provided that the sheet exists, of course.

     

    EDIT: Wondering if you can actually do this on Excel for iPad (don't use it myself). Couldn't find how to enter named ranges/formulae on Excel for iPhone. It's just too small and inconvenient, I think. But a sheet made in regular Excel with the above method will work as intended when you open it in Excel for iPhone. So I expect it to work as well on the iPad.

    If you must create the solution on the iPad, you can enter the text BHP!C2 anywhere (e.g. in A1) on your Summary schedule and enter =INDIRECT(A1) where you need the content of BHP!C2 to show-up.

Resources