Another mystery: finding the hidden values for the drop-down list Now try copying the formula one to the right, so that it reads:Īt this point you can start to copy these cells across and down until you have covered all the years, and all the countries - and the full dataset is there to be reused. That’s the first year in the table above - a positive sign. This time the formula fetches a number: 1971. That cell is empty – but try copying it down one cell, so that it reads: We could write a formula that grabs any specified cell from that sheet, like so (this is best done below the table so you have plenty of room): If that formula can fetch the data, then perhaps we can create more formulae which will cumulatively fetch all of it. Let’s go back to the formula which is fetching the data from that hidden sheet. So we need a different approach… Experiment with cell references to the hidden sheet If you are using a workbook that contains VBA macros and you encounter problems when working with hidden worksheets, contact the owner of the workbook for more information.”Īlternatively, the hidden information may have been removed using the document inspector. “Note: If worksheets are hidden by Visual Basic for Applications (VBA) code that assigns the property xlSheetVeryHidden, you cannot use the Unhide command to display those hidden sheets. However, in this particular example the instructions don’t work - possibly because, as a note on that page explains: In some instances that will be the end of the process. Instructions on how to hide a sheet in Excel - and, more importantly, how to unhide them - can be found in this Microsoft Office article. It must be hidden… Find a hidden sheet in Excel But this spreadsheet doesn’t have a sheet with that name - or at least one that we can see. The formula is looking in a sheet called 'rawdata and checks' (the exclamation point is a useful clue to look for). This one is quite a mouthful - but you don’t have to understand it to spot the name of a sheet which is not visible: The box will probably contain a formula that provides a clue to the location of the missing data. When selected, look in the formula bar above the sheet (but below any menus) - highlighted with the red box in the image above. The first step is to click on one of the cells containing the numbers you want - the numbers that change when a different option is selected from the drop-down menu. Select a cell with data and the formula bar will show a complex formula
0 kommentar(er)
