Retrieve cell with variable sheet name
Solved
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 answers
-
Contributor```html re
=INDIRECT("'Devis"&A20&"'!A1")
don’t forget the apostrophes in front of Devis and in front of the exclamation mark and the space behind Devis
if you write in a cell: ="'Devis"&A20&"'!A1"
you should be able to read:
'Devis D0350'!A1
see this file that reconstructs your data
https://mon-partage.fr/f/71Rq35FH/
have a good evening
--
The quality of the response depends mainly on the clarity of the question, thank you! ```
