Excel - Populating table data via List Boxes 1

[beanxx]

Using a List Box, dependant upon my selection of several variants I want to be able to enter values in a table from a larger table of raw data. Using the Cell link 1, 2, 3, 4 etc, I am able to populate the table values for a single returned value using a formula such as :-
IF('Source Data Sheet'!D2=1,'Source Data Sheet'!H6,0)
where D2 represents the Cell Link and H6 is a table value, I can get the thing to work for one of the listing items, but how do I do this when I have several variants on the List Box.

Sorry if this is a tad confusing but I have tried to simplify my query, honest!

Justin

 

[beanxx]

Having dug around a bit although a bit messy I can kind of resolve my query by nesting IF calculations, trouble is Excel only lets me nest 8 such calculations when I have 11 data values. I think I may have another way round this by doing a simply calculation as follows
If D2 Value = 1 I wish to return H2
If D2 Value = 2 I wish to return H20
If D2 Value = 3 I wish to return H38, etc

I would have hoped to cover this with the following formula :-
Cell Value = H((D2-1)*18)+2

Trouble is this don't appear to work as I'm not specifying a descreet cell value. Perhaps I should post as a seperate query......

Justin

 

[DaleWatson123321]

Hi Justin,

I created the following formula for your situation and it seems to work fine.

=IF(D2=1,H2,INDIRECT("H"&2+(D2-1)*18))

I hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[beanxx]

Dale,

Thanks for this and yes the use of INDIRECT combined with IF's has sorted out my problem, fantastic!

Oh and I have awarded a star as this has proved very very helpfull and has got rid of my headache!

Justin