Have to alter existing sheet but can't find cells to alter.

limpopo

New Member
Joined
Nov 2, 2016
Messages
11
Hi all,

Just to start, I'm not an Excel expert and just have basic skills.

The situation is that i have an existing Excel sheet that was created a couple of years ago by another person who is no longer available.

At a basic level, the section i want to alter has a box in which you input a code and it appears that the VLOOKUP function is then used to check a table of values and return the relevant response against the input code. The response appears in another box.

The code is input in C7

The corresponding value appears in cell D7

The code that is in D7 is as follows;

=IF(C7="","",VLOOKUP(C7,'codelookup'!A2:D385,2,FALSE))

What i need to do is alter some of the values that are returned against the various codes that are input in C7 as the values have changed.

Being an amateur it appears to me that these values are hidden somewhere . I cant scroll down to have a look at D385 as the sheet stops scrolling at row 200.

Does anyone have any clues please ??

Thank yo
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,270
Office Version
365
Platform
Windows
The values are in a sheet called "codelookup" starting in A2
 

limpopo

New Member
Joined
Nov 2, 2016
Messages
11
The values are in a sheet called "codelookup" starting in A2
Thanks,, there are lots of sheets in the workbook but no tabs with that name. I'm guessing that somehow the tab is hidden. Cant work out how to reveal..?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,270
Office Version
365
Platform
Windows
OK 2 options
1) Right click on any tab > select Unhide (if not greyed out). If the sheet you want is there select it & click OK.
2) Alt F11 & look at the Project Window (usually top left) & the should be something like VBAProject(Filename.xlsm) where filename is the name of your file. If there is a + sign to the left of the project click it.
There should then be a folder called Microsoft Excel Objects, once again if there is a + click it & you should see all the sheets in the workbook.
Find the one saying something like Sheet2(codelookup), select it & then in the properties window below, the last line will say Visible in the box to the right of that select xlsheetvisible
 

limpopo

New Member
Joined
Nov 2, 2016
Messages
11
Thanks

Option 1 is not possible as greyed out

Over to option 2

I can see the codelookup sheet labelled Sheet 37 (code look up)

I selected it/highlight it but im not seeing a properties box below. Im seeing a box to the right (new pane) with drop down menus General Declarations

???
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,270
Office Version
365
Platform
Windows
The properties window should be directly below the Project explorer window.
If not select the sheet and hit F4
 

limpopo

New Member
Joined
Nov 2, 2016
Messages
11
OK thanks, I'll have a play for a while and see if i manage !
 

limpopo

New Member
Joined
Nov 2, 2016
Messages
11
OK,back again !

So in properties i tried to select visible and an error popup box appeared saying "unable to set the visible property of the worksheet class "
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,487
Office Version
365, 2010
Platform
Windows, Mobile
OK,back again !

So in properties i tried to select visible and an error popup box appeared saying "unable to set the visible property of the worksheet class "
Is your Workbook structure protected?
 

limpopo

New Member
Joined
Nov 2, 2016
Messages
11
Hi,,yes it is protected. I used the unprotect all VBA dialog to unprotect all then tried to set sheet 37 to unhide again but still showing same error message..
 

Forum statistics

Threads
1,082,114
Messages
5,363,244
Members
400,722
Latest member
DrewPop24

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top