How to auto generate a table?

[Weaselwithagun]

Hi all,

Does anyone know of a formula that would auto generate the information for a table? I've got a set of data that has a VLOOKUP on it that pulls back the relevant numbers to the cell. what i want to know is, if i increase the data type in my source, can i get the table to automatically put it into and apply the lookup?

Probably not explained this well, so i've attached an example - the first sheet shows the table with the lookup and the second is the data source (which is manually entered), So the lookup works for all the types it's set to find. but in this example bananas are entered into the source data but don't have a line on the table sheet. Is there a way of getting excel to add this to the table from the source data or do i need to type it in?

Cheers.

[Kogen]

Is this what you're looking for? I used an =indirect data validation.

test.xlsx

[FlameRetired]

I interpreted a bit differently.

1. Convert the source data into an Excel Table. The reference ranges will auto resize to fit the data.
2. In the attached I changed the default name Table1 to Source_Table. (See Name Manager)
3. Do the same with the summary.
4. This formula in A2 of Sheet1.

   Please Login or Register  to view this content.

5. Leave the VLOOKUP alone.

Is this what you envision?

	A	B
1	Summary Fruits	Summary Counts
2	bananas	26
3	berries	45
4	pears	85
5	cherries	18
6	oranges	18
7	apples	98

[Weaselwithagun]

I interpreted a bit differently.

1. Convert the source data into an Excel Table. The reference ranges will auto resize to fit the data.
2. In the attached I changed the default name Table1 to Source_Table. (See Name Manager)
3. Do the same with the summary.
4. This formula in A2 of Sheet1.

   Please Login or Register  to view this content.

5. Leave the VLOOKUP alone.

Is this what you envision?

	A	B
1	Summary Fruits	Summary Counts
2	bananas	26
3	berries	45
4	pears	85
5	cherries	18
6	oranges	18
7	apples	98

...So there's a load of formula going on there I've not come across before...., will spend time learning what they are and how they work so i can adapt them to my main sheet. Once again, many thanks.

[FlameRetired]

You are welcome. Glad to help.

Weaselwithagun, in the future please don't quote whole posts especially when immediately following the post you are responding to. In fact this is actually a forum rule:

Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.