Retrieve cell with variable sheet name
Solved
Muratti
-
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
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 réponses
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!
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!
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.