Lookup Table

[tedc]

Some time ago, with the help of contributors to this forum, I was able to create a fairly sophisticated Excel 'spreadsheet'
I haven't used the program for a while and have since installed Win 7 and Office 2007 ( the original Excel program was written in Excel 2003 which I am now using)
I have found when adding extra data in lookup tables do not appear on the sheet that points to the table.
I could post the workbook if required.
Could someone please help .?

Thanks
Ted

 

[p45cal]

SI could post the workbook if required.

Yes please, though I doubt you'll be able to attach a file here, try something like box.net, or skydrive.

 

[Ruddles]

Check the formulae which use the table. You'll probably find that one of the following has happened:-

1. You added stuff to the end of the table but didn't adjust the formulae which pointed to it to cover the new rows.
2. The table is in a named range and that still points to the original number of rows.

The fix is to cut the extra rows and insert them before the original last row. This will (1) adjust the formulae or (2) stretch the named range.

 

[tedc]

Hi Ruddles
I've just tried what you have suggested, but no luck.

 

[tedc]

Hi p45cal
here's the link to box.net
http://www.box.net/shared/7ty3709irl
The error occurs between MAIN (SHOW column) and lookup SHOW

cheers
ted

 

[p45cal]

I have found when adding extra data in lookup tables do not appear on the sheet that points to the table.

Could you pinpoint (some of) the cells which aren't behaving - sheet name and cell addresses?
How are data added to the table concerned? Is it a manual process, or has it been automated?

 

[tedc]

Could you pinpoint (some of) the cells which aren't behaving - sheet name and cell addresses?
How are data added to the table concerned? Is it a manual process, or has it been automated?

On the SHOWS sheet you will notice an entry (2nd last) <b>The Bay City All Breeds Kennel Club</b> This is the latest entry. Cell A-D 93
This should appear in the dropdown list on the MAIN sheet under the Column SHOW. Cell C2. At the moment it is not appearing. I hope this is the information that you needed.
The data on the SHOW sheet is added manually.
Cheers
Ted

 

[p45cal]

On the SHOWS sheet you will notice an entry (2nd last) The Bay City All Breeds Kennel Club This is the latest entry. Cell A-D 93
This should appear in the dropdown list on the MAIN sheet under the Column SHOW. Cell C2. At the moment it is not appearing. I hope this is the information that you needed.
The data on the SHOW sheet is added manually.
Cheers
Ted

Well, the file you have posted at box.net is a little different from the one you're looking at. The Shows sheet has an entry for The Bay City All Breeds Kennel Club but on row 82, not 93 (maybe you sorted it) and incidentally also has an entry for Bay City All Breeds Kennel Club at row 6), but this is of little importance.

The ComboBox2 at cell C2 of the Main sheet has a ListFillRange of AT2:AY201, but this range is on the Main sheet and there is no Bay City entry there.

Note also that the formulae on the Main sheet in cells C4:C8 of the ilk:
=VLOOKUP(C3,Show,2,FALSE)
refer to a named range Show whose address is Main!$AT$2:$AY$650

Neither the combobox nor these formulae refer to the sheet Shows at all.

 

[tedc]

ah so what your telling me is the new entries should be in MAIN sheet Main!$AT$2:$AY$650 etc

Seem to work okay now .. Thanks mate, yourve been a great help
Ted
addend: Its been so long since this was put together that I thought it LOOKUP referred to SHOW sheet.