View original (French)

Retrieve cell with variable sheet name

Solved
Muratti -  
 Muratti -
Hello,

I am creating an excel file that contains all the client information + product supplies, etc...
With this, I have managed to create quotes much more quickly.

I also want to "automate" my invoices, so I have a quote number that is linked to an invoice number: for example, F0010 is the invoice for quote D0350. F0011 is for quote D0375 (all of this is filled in on a tab). So when I create an invoice, I do invoice number --> F0010
then on another line I display: "Invoice F0010 for quote D0350". (Note that D0350 appears using VLOOKUP, so for a new invoice number, it changes the quote number accordingly.

But I am unable to retrieve a specific cell from quote D0350.

I wanted to retrieve a cell (the total amount of the quote) to display it on my invoice.
This is quite simple with: ='sheet_name'!A1 (but it remains fixed if I change the invoice number and thus the quote number) ideally I want the sheet_name to be linked with the quote name, which is variable.

I tried to improvise but nothing works, I searched on Google but I couldn't describe my issue correctly, so I didn't get any results.

Could you please help me?

Thank you

Configuration: Windows / Chrome 86.0.4240.111

2 answers

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    didn't understand too much but randomly, to adapt:
    tab name in cell B1 of the editing sheet
    to retrieve the value of A1 from the sheet whose name is in B1:
    =INDIRECT(B1"!A1")
    if the tab name contains no spaces or operators (works without apostrophes for the sheet name)

    otherwise valid in all cases
    =INDIRECT("'"&B1"'!A1")
    to add apostrophes
    thx

    --
    The quality of the answer mainly depends on the clarity of the question, thank you!
    1
    1. Muratti
       
      I'm sorry, I misunderstood.

      I have a tab named "Devis D0350" and in cell A1 I have some data that I want to retrieve.
      I have another tab named "affaire" where F0010 and D0350 are on the same line.
      I have another tab named "Facture F0010"; in this document I have:

      Cell A18: data list, I choose F0010
      Cell A20: =VLOOKUP(A18,affaire!$1:$1048576,2,FALSE) and I find D0350.
      Cell A22: here I would like to retrieve the value of cell A1 from "Devis D0350". I tried: =INDIRECT(A20&"!A1") but it doesn't work.
      0