LinkBack URL
About LinkBacks
Hi All,
I am wanting to reference A column in the balancing sheet of the workbook attached in the formula in column C rather than going into each individual sheet to reference the data required.
Thank you
Referencing Sheets with Sheet Name in a cell
Hi All,
I am wanting to reference A column in the balancing sheet of the workbook attached in the formula in column C rather than going into each individual sheet to reference the data required.
Thank you
a number of reasons why this isn't good practice, but I think this is the formula you are looking for:
=VLOOKUP(B6, INDIRECT(A6 & "!A4:B6" ),2,FALSE)
<<< If you have valued anyone's contributions in this thread, please click * to thank them for their efforts
Thank you for your help that is working. What would your recommendation be rather than using this?
Great, happy to hear it works.
The main reasons it's not ideal is:
- If you add extra data rows, you would need to change "A4:B6" in each individual formula
- It's not transparent, as you can't Trace Dependents
- It will break if any of the names have a space [you need to modify the formula to =VLOOKUP(B6, INDIRECT("'"& A6 & "'!A4:B6" ),2,FALSE) ]
- If you change any of the worksheet names, or the employee names in cells A6:A8 it won't work
- It is slow (only noticeable if using a very large dataset)
My recommendation would be to either
- consider restructuring the workbook so that the data for all employees are in the same worksheet; or
- keeping the same structure but using PowerQuery to compile the data https://trumpexcel.com/combine-multiple-worksheets/
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks