help with indirect and lookup

[flier109]

I have an excel spreadsheet which I use for generating invoices.
on my index tab I have a list of names, with a column called "Invoice".
Each tab on my worksheet has a name with corresponds with the names on the index, and I'd like each tab to lookup the name of the tab on the index page and return the invoice number from there, but I can't seem to get it to work.
I'm currently using "=VLOOKUP("Invoice",INDIRECT(Index!A2:E61),5,0)" with the column named "Invoice" being 5 columns along

Can anyone help me to get this working, or is there an easier way?

 

[Fluff]

It's not entirely clear what you are trying to do, so can you post a sample of both sheets.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also what version of Excel are you using
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[flier109]

repaired SNAP FEES INVOICE Master.xlsm
	D
1	#VALUE!
Sally

Cell Formulas
Range	Formula
D1	D1	=VLOOKUP("Invoice",INDIRECT(Index!A2:E61),5,0)

 

[flier109]

repaired SNAP FEES INVOICE Master.xlsm
	A	B	C	D	E	F
1	INDEX	Invoice Amount			Invoice	Invoice Number last changed date* change once invoices are created
2	Dave				24000	31/08/2021
3	Donald				24001
4	Sally				24002
Index

Cell Formulas
Range	Formula
E3:E4	E3	=+E2+1

 

[flier109]

It's not entirely clear what you are trying to do, so can you post a sample of both sheets.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also what version of Excel are you using
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks. I've now uploaded examples. The first is the invoice tab and teh second is teh index page. I want the invoice page to look up the name of the invoice tab, in this case "Sally" and find the corresponding invoice number created on the index page

 

[Fluff]

Thanks for that, you may have missed this part of my post

Also what version of Excel are you using
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[flier109]

Thanks for that, you may have missed this part of my post

I did indeed, now all done

 

[Fluff]

Thanks for that, how about

=VLOOKUP("Sally",Index!A2:E100,5,0)

 

[flier109]

Thanks for that, how about

=VLOOKUP("Sally",Index!A2:E100,5,0)

I need it to be more dynamic than that, using the tab name, as the spreadsheet has around 50 different tabs each with an invoice with other names

 

[Fluff]

In that case why not put the tab name in a cell & refer to that.