vlookup problem

[arytxer]

Hi I have two tabs on my spreadsheet, one (input) contains all of the data and the other (cnn) has vlookup formulas to search the input sheet. However the input sheet has become corrupted so I need to delete it and create another sheet. But when I try to do this the formula does not work. I have tried to create a whole new spreadsheet but I cannot get the formula to work. Please help.

Below is the formula I am using.
The CNN in the formula below first looks at a cell named CNN (in this cell will be something like this CRE101) in the cnn sheet and then go to the input tab and find CRE101 then return the value of what is in column 5.

=VLOOKUP(CNN,input,5,FALSE)

[dkorbat]

I think you need to put the table range in the Input Sheet? Try something like this:

Please Login or Register  to view this content.

Where A1:E10 contains your data.

Or maybe I misunderstood. Can you post the workbook here?

[Sam Capricci]

In your first version was "input" also a table name? if it was that might explain why it worked. Otherwise you have to put parameters around the formula such as those suggested by dkorbat.

[arytxer]

Thank you for your replies.

the problem I have is that it always worked perfectly before and still works on my old spreadsheet.

However I cannot get it to work now if I delete the input tab and recreate another with the same name. It does not recognize the new sheet.

[arytxer]

I want to attach a working example and a non working one but there is absolutely nowhere on this page to attach it. Please help.

[arytxer]

This is my non working example

[dkorbat]

I've took a look and it will work if you put the range as I've mentioned in my first reply.
Should this be enough to answer your question? Or you'd prefer to use a named range instead?

[azumi]

You forgot type the ranges:

=VLOOKUP(CNN,input,5,FALSE)

Should be
=VLOOKUP(CNN,Input!A1:E1,5,FALSE)

[arytxer]

But I've never needed the ranges because the actual spreadsheet I work from has about 60 columns and it has always worked without the ranges.

Do you know how I delete files from attachments because I cannot add the working example as it says there is not enough space.

If you could see the working example you will see what I mean.

[dkorbat]

arytxer: go to Settings on the top right corner of this forum and in the bottom left there's a menu Attachments under Miscellaneous. You can delete your attachments from there.

[arytxer]

I have tried that and deleted something but when I checked they are all still there.

I am stumped. Is there any other way of deleting them or do I have to log out-in again?

[azumi]

its not possible without ranges, unless the ranges given name by name manager on excel so the ranges hidden by the name....

[AliGW]

Thank you for your replies.

the problem I have is that it always worked perfectly before and still works on my old spreadsheet.

However I cannot get it to work now if I delete the input tab and recreate another with the same name. It does not recognize the new sheet.

By deleting the sheet referenced in the formula, you have broken the formula. Adding the sheet back will not work. The best thing would to be to add a new sheet with a different name, then use find and replace to change the reference in the formula.

[arytxer]

Thank you Azumi you hit the nail on the head. I didn't realise that the range was hidden. I guess this is to keep the formula as short as possible.

I added the range and it worked a treat and then I removed it and it still worked, I am guessing that it is still there but now just hidden.

Can you tell me how I unhide it please so I can amend the range?

[arytxer]

Hi everyone thank you all for your help I have played around and have finally sorted the problem. I have amended the data in the name manager and it works a treat.

All the best