Hi everyone, new to the community, I’d be v. grateful for any advice on this head-scratcher.  Not sure if I’m asking the question in the right way so please ask if anything unclear.

I’m on a Mac using Office 365.  I have a multi-tabbed workbook that is used to record print specifications for different pieces of corporate collateral. 

The first sheet is a summary/aggregator of information from all other sheets to enable easy cross-checking and comparison.  Reference images and numerical/text data relating to each item are entered to individual sheets (with tab name as the item name) and I’d like to find a way to dynamically pull into the summary sheet key images and data from individual sheets with the ability to add or remove sheets in future and have the summary sheet update automatically. 

I know how to add manual links to the data sets, but if a sheet is added, I have to go back to the summary and add more manual links for the corresponding row to be updated.  I’d like to find a way for this to happen automatically.

SUMMARY SHEET SET-UP

Tab name = AA_SUMMARY with the Name Box entry as ‘INDEX’Column A = numbers 1-100 on individual rows, starting row 5 (to allow for sheet title etc in rows 1-4)Column B = empty (aesthetic spacer)Column C = contains a list of all the tab/sheet names, pulled in via  =INDEX(SheetNames,A5) type formula.  This relies on having the numbers in column A to work, I believe, as without the numbers, the index runs along the row, and I want it to run down the column.Column D = hyperlink to the individual sheet named in C via  =HYPERLINK(“#'”&B2&”‘!A1″,”Go To Sheet”).  Does double duty as navigation and also is what I’ve been using to direct formula to relevant sheet (see lower down). Columns E+ = these are the cells I want to populate with data from the other worksheets.  All columns have a header which corresponds to a category of information present in the other worksheets.  The idea being that if you look along a row, you get all the key information for that sheet – like this this, where the text in red italics automatically pulls in – regardless of whether you add sheets or remove sheets.

    Ref imagecategory 1category 2category 31 NAME OF WORKSHEET 1Go to sheet[image]data 1data 2data 3 2 NAME OF WORKSHEET 2Go to sheet[image]data 1data 2data 3

This set-up enables sheet names to be added or removed dynamically if new ones are added to the work book, or old ones deleted.

DATA-ENTRY SHEET SET-UP

Tab name = individual name relevant to the item, I’ve not added a named range via Name BoxRows 1-3 = sheet headings Row 5, across columns A-H = reference image inserted in-cellRow 6 = blank (aesthetic spacer)Row 7, columns A-H = merged containing the item name which is pulled in from the tab name via =TEXTAFTER(CELL(“filename”,A1),”]”)Row 8, columns A-H = individual category headers which match the category 1 / category 2 (etc) headers on the summary sheetRow 9, columns A-H = empty cells where relevant data is entered

Image pulled in here via ‘insert image in-cell)

 Item:Name of item pulled in here via =TEXTAFTER(CELL(“filename”,A1),”]”)Category 1Category 2Category 3Category 4Category 5Category 6Category 7Category 8enter data 1enter data 2enter data 3enter data 4enter data 5enter data 6enter data 7enter data 8

(sorry for odd looking table – the editor won’t allow me to merge the image, spacer and item cells at full width across the lower columns.)

THE PROBLEM

I’m hitting a wall trying to find a formula which knows to look at the hyperlink sheet destination, go to that sheet and pull in info from cells 9A > 9H. 

If I manually add:  = and click the hyperlink cell to pull in the sheet name, then navigate to that sheet and click the relevant cell in that sheet, (eg: formula looks like: =LETTERHEAD!A9  ) it does return the correct data to that cell in the summary.  But if then copy that formula to the following rows, it just pulls in the original sheet name, rather than the one relevant to that row and won’t dynamically update itself.

So the issue seems to be finding a formula that instructs to look left to the hyperlink for each row automatically, then reference the relevant cell in the destination sheet.

I hope that makes sense, and any advice v. gratefully received. 

Thanks in advance!

Soph.

​  Hi everyone, new to the community, I’d be v. grateful for any advice on this head-scratcher.  Not sure if I’m asking the question in the right way so please ask if anything unclear.I’m on a Mac using Office 365.  I have a multi-tabbed workbook that is used to record print specifications for different pieces of corporate collateral.  The first sheet is a summary/aggregator of information from all other sheets to enable easy cross-checking and comparison.  Reference images and numerical/text data relating to each item are entered to individual sheets (with tab name as the item name) and I’d like to find a way to dynamically pull into the summary sheet key images and data from individual sheets with the ability to add or remove sheets in future and have the summary sheet update automatically.  I know how to add manual links to the data sets, but if a sheet is added, I have to go back to the summary and add more manual links for the corresponding row to be updated.  I’d like to find a way for this to happen automatically.SUMMARY SHEET SET-UPTab name = AA_SUMMARY with the Name Box entry as ‘INDEX’Column A = numbers 1-100 on individual rows, starting row 5 (to allow for sheet title etc in rows 1-4)Column B = empty (aesthetic spacer)Column C = contains a list of all the tab/sheet names, pulled in via  =INDEX(SheetNames,A5) type formula.  This relies on having the numbers in column A to work, I believe, as without the numbers, the index runs along the row, and I want it to run down the column.Column D = hyperlink to the individual sheet named in C via  =HYPERLINK(“#'”&B2&”‘!A1″,”Go To Sheet”).  Does double duty as navigation and also is what I’ve been using to direct formula to relevant sheet (see lower down). Columns E+ = these are the cells I want to populate with data from the other worksheets.  All columns have a header which corresponds to a category of information present in the other worksheets.  The idea being that if you look along a row, you get all the key information for that sheet – like this this, where the text in red italics automatically pulls in – regardless of whether you add sheets or remove sheets.     Ref imagecategory 1category 2category 31 NAME OF WORKSHEET 1Go to sheet[image]data 1data 2data 3 2 NAME OF WORKSHEET 2Go to sheet[image]data 1data 2data 3 This set-up enables sheet names to be added or removed dynamically if new ones are added to the work book, or old ones deleted. DATA-ENTRY SHEET SET-UPTab name = individual name relevant to the item, I’ve not added a named range via Name BoxRows 1-3 = sheet headings Row 5, across columns A-H = reference image inserted in-cellRow 6 = blank (aesthetic spacer)Row 7, columns A-H = merged containing the item name which is pulled in from the tab name via =TEXTAFTER(CELL(“filename”,A1),”]”)Row 8, columns A-H = individual category headers which match the category 1 / category 2 (etc) headers on the summary sheetRow 9, columns A-H = empty cells where relevant data is entered   Image pulled in here via ‘insert image in-cell)   Item:Name of item pulled in here via =TEXTAFTER(CELL(“filename”,A1),”]”)Category 1Category 2Category 3Category 4Category 5Category 6Category 7Category 8enter data 1enter data 2enter data 3enter data 4enter data 5enter data 6enter data 7enter data 8 (sorry for odd looking table – the editor won’t allow me to merge the image, spacer and item cells at full width across the lower columns.) THE PROBLEMI’m hitting a wall trying to find a formula which knows to look at the hyperlink sheet destination, go to that sheet and pull in info from cells 9A > 9H. If I manually add:  = and click the hyperlink cell to pull in the sheet name, then navigate to that sheet and click the relevant cell in that sheet, (eg: formula looks like: =LETTERHEAD!A9  ) it does return the correct data to that cell in the summary.  But if then copy that formula to the following rows, it just pulls in the original sheet name, rather than the one relevant to that row and won’t dynamically update itself. So the issue seems to be finding a formula that instructs to look left to the hyperlink for each row automatically, then reference the relevant cell in the destination sheet. I hope that makes sense, and any advice v. gratefully received. Thanks in advance!Soph.   Read More

​