excel + is a cell has one value, then an arra

Microsoft Excel 2003 (full product)
July 22, 2010 at 10:51:07
Specs: Macintosh
i need to type a value in C1 and have that value trigger a different range of cells (I16 thru T40) to change to a specific set-up to collect classroom data. i've tried LOOKUP, VLOOKUP, and IF. plz help - i'm going bonkers!

See More: excel + is a cell has one value, then an arra

Report •


#1
July 22, 2010 at 11:09:37
Hi,

You need to provide a lot more information.

What are you entering into Cell C1.
Do some values in C1 'trigger' the actions and some values do not trigger the reaction.
If so what are the criteria for the action taking place, e.g. if C1 = "X" or C1>99

What do you want to happen to the cells in the range I16:T40.

Do you want them to have one value if C1 does not meet the specified criteria and another value if C1 meets the criteria,
or
do you want to use a lookup table, so that the values shown in I16:T40 obtain values from a lookup table, based on the value in C1.

Without hard data there is no chance of providing a solution. Remember, we can't see your spreadsheet, we don't know what it contains or how it's organized and we have no idea what you want to achieve - so please - lots more information.

Regards


Report •

#2
July 22, 2010 at 11:23:31
thanks for the help -
in C1, i want to enter a numeric value that comes from another worksheet. thus if C1 = any of the values in WORKSHEET2 - SHEET1 A1:A9, then i need WORKSHEET1 - I16:T40 to return the value of WORKSHEET2 - SHEET1 A29:L53.

in WORKSHEET2 - SHEET1 A29:L53 - I have set-up a table of sorts that needs to import directly into WORKSHEET1 - I16:T40 when C1 = any of the values from WORKSHEET1 - A1:A9.

thus, i think i want to use a lookup table, so that the values shown in I16:T40 obtain values from a lookup table, based on the value in C1.

i hope that helps. thanks for your time - vince


Report •

#3
July 22, 2010 at 13:06:42
Is the criteria you listed in your previous response the only criteria that we need to know about or was that just one example?

In other words, would different values in C1 cause I16:T40 to be populated with different tables?


Report •

Related Solutions

#4
July 22, 2010 at 14:38:53
hi derbydad03-
short answer - yes. i was going to try and figure that part out after the fact.

but since you asked....i have about 10 tables the exact same size as I16:T40.

whatever is input into C1 will dictate the table option for I16:T40.

thanks for the help - vince


Report •

#5
July 23, 2010 at 13:07:42
did i stump you guys with an impossible function? sorry about that - i'm obviously a novice. please let me know so i can stop trying and failing over and over. thanks, vince

Report •

#6
July 26, 2010 at 05:06:55
Hi,

Here is a small example of how to go about this.

For this example I have four cells on "Sheet1" that will be replaced according to a value in Cell A1 on Sheet1.
The Four cells are C1, D1, C2 & D2

On "Sheet2" there are sets of four cells which will show in the four cells on Sheet1 depending on the value in A1

In "Sheet2" I have this:

	C	D	E	F	G	H
1	1	2	11	12	21	22
2	3	4	13	14	23	24

When A1 contains 1, Sheet1 shows this:

	C	D
1	1	2
2	3	4

When A1 contains 3, Sheet1 shows this:
	C	D
1	21	22
2	23	24

The formula on Sheet1, cell C1 is:

=OFFSET(Sheet2!A1,0,$A$1*2)

Just drag it across and then down to fill the four cells.

You can use this as the basis for your larger range.
The OFFSET function uses an origin and then row and column offsets.
The multiplier depends on the size of the tables on Sheet2.

If the value in A1 is non-numeric, you could use VLOOKUP() to convert the text to a numeric value using a two-column table, and then use the result of VLOOKUP() in place of A1.

Regards


Report •

#7
July 26, 2010 at 07:00:09
I have one more clarification question:

Is it a single, specific value which will dictate a specific table option or are there multiple values for each table?

In other words, when you said "C1 = any of the values in SHEET1 A1:A9" did you mean that:

- A1 should return "Table A"
- A2 should return "Table B", etc.

or did you mean that:

- Any of the nine values from A1:A9 should return "Table A"

- Any of 9 values from some other range will return "Table B", etc?



Report •

#8
July 26, 2010 at 10:15:15
you guys are so good. i meant:

- Any of the nine values from A1:A9 should return "Table A"

- Any of 9 values from some other range will return "Table B", etc?

i'm going to try humar's suggestions and i'll post my results. you two are champions! thx for your time - vince


Report •

#9
July 26, 2010 at 10:34:14
so I tried Humar's solution, but i can't input an OFFSET formula into cell C1. C1 has to be free for me to input a value attached to a separate worksheet.

basically, C1 is the trigger cell. Whatever I input into cell C1 triggers actions on the rest of the worksheet.

maybe that is the problem. Cell C1 is included on 2 other LOOKUP formulas on the existing worksheet.

A4 and F4 currently have LOOKUP formulas linked to C1.

I16 is the cell in which i need differing tables to pop up depending on the data input in C1.

DerbyDay03 hit the nail on the head when he suggested that - Any of the nine values from A1:A9 should return "Table A" and any of 9 values from some other range will return "Table B", etc.

I hope this helps determine my problem and why i can't achieve what DerbyDad03 articulated.

thanks again - vince


Report •

#10
July 26, 2010 at 10:57:34
I think it would help if we knew the location of the other ranges that will be used to trigger the "table loading."

For example, if the ranges were A1:A9, then A10:A18, then A19:A27, etc. then I have a suggestion seems to work.

I won't bother posting it since I don't know if it fits your situation.

What is the relationship/locations of the other 10 ranges that might be used as input?


Report •

#11
July 26, 2010 at 11:02:17
Hi,

I was only giving you a framework for doing what you wanted to do.

but i can't input an OFFSET formula into cell C1.
Just start with the first offset formula in I6.
Adjust the formula to refer to the numeric value, either in C1 or in a cell that returns a numeric value based on the contents of C1

The initial cell in the offset formula is the top-left cell of the first replacement range (on Sheet2).

The multiplier is based on the number of columns that your range takes up (I to T =12)

Cell C1 is included on 2 other LOOKUP formulas on the existing worksheet.. This has nothing to do with getting this 'replacement' function working.

Regards


Report •

#12
July 26, 2010 at 11:03:43
derbydad03-
you are on the money once again.

the ranges are fairly random in length of cells, but they are all in column a. for example.

A1:A9 A10:A24 A25:A27 A28:A36 etc.

so let's say;
A1:A9 triggers table a
A10:A24 triggers table b
A25:A27 triggers table c

etc....

hope this helps. sounds like you might be onto something......vince


Report •

#13
July 26, 2010 at 11:11:10
I hope you realize that you are making it difficult for us by changing the "rules" mid-game.

First you answer one of my questions with:

- Any of the nine values from A1:A9 should return "Table A"

- Any of 9 values from some other range will return "Table B", etc?

Now you tell us that there aren't really 9 values in each range, there are actually a "random" number of values.

Keep in mind that we can't see your worksheet from where we're sitting nor can we can't read minds. We can only work with what you tell us. The suggestion I was going to make was based on your answer of "9" values and won't work for a "random" number of values.

I'll see what I can come up with (assuming you can't get Humar's suggestion to work) but it's really hard to help you without knowing the total picture.


Report •

#14
July 26, 2010 at 12:27:27
OK, this seems to work, but you're going to have to figure out the correct syntax based on your Sheet Names, etc. I did this by using data and tables all from the same sheet to make it easier to explain.

I started with a set of values in Column A, and added a "Group Number" for each value in Column B. (You can place this Group Number column anywhere you want, just alter the VLOOKUP portion of the final formula to match.)

     A      B
1    1      1
2    2      1
3    3      1
4    4      1
5    A      2
6    B      2
7    C      2
8    $      3
9    #      3
10   &      3
11   @      3

I then selected the upper left cell of each "table" and Named each cell as follows:

Table1
Table2
Table3
etc.

For example, for your early example of the table in A29:L53, cell A29 would be named Table1.

If your next table was in A54:L106, A54 would be named Table2

Now, in I16 (the first cell where the tables should be imported to) I placed this formula:

=OFFSET(INDIRECT("Table"&VLOOKUP($C$1,$A$1:$B$11,2,0)),ROW()-16,COLUMN()-9)

I then dragged it across the width of the table and then down the length of the table.

How this works is as follows:

VLOOKUP will grab the Group Number from Column B based on the value in C1.

We then concatenate the word Table and the Group Number to return Table1, Table2, etc. which match your Range Names.

The INDIRECT function will reference the Named Range which is the first cell in each table.

The OFFSET function will use the COLUMN and ROW calculations to determine how many Columns and Rows to OFFSET from the Named Range, which is the upper left cell of each table.

In other words, ROW()-16, COLUMN()-9 in I16 returns an offset of 0, 0. As you drag the formula across and down, those numbers will increment accordingly.

So, for example, with a 2 in C1, the formula in I16 can be reduced to:

=OFFSET(TABLE1,0,0)

which is the value in A29.

In I17, it can be reduced to:

=OFFSET(TABLE1,1,0)

which is the value in A30.

And so on and so on.


Report •

#15
July 26, 2010 at 12:53:47
i am so sorry. i didn't realize the random amounts made a such a difference. excel is so regimented like that. just goes to show how novice i am. plz don't spend anymore of your valuable time on this. i really thought it would be easier than this. i'm sorry i've created a worksheet that is too complicated. i think it might be time to throw in the towel. -vince

Report •

#16
July 26, 2010 at 12:54:49
sorry i didn't see you 12:27 post before my last yelp... i'll try it and let you know ASAP -vince

Report •

#17
July 26, 2010 at 13:26:46
re: i didn't realize the random amounts made a such a difference. excel is so regimented like that

I don't think that that is the case - or if it is, then it has to be. Let me try to explain.

Just like us, Excel can't read minds. How could it possibly know which table you want to import based simply on a value in C1 if that value is the only thing it has to work with?

It has to know which table a given value is associated with. If you had the same number of values in each group, we could do some math to determine which group the value was in.

For example, if you had 9 values in each group and they were in A1:A9, then A10:A18, then A19:27, etc. this formula would return 1, 2, 3 etc. based on the value in C1.

=INT(MATCH(C1,$A$1:$A$27,0)/9.01)+1

We could then use that value to determine which group a given value was in and import the corresponding table.

However, if the groups are made up of a "random" number of entries, with no mathematical relationship, we can't use a formula to determine which group it's in; we need to tell Excel which group it's in. That is what I did with my list in Column B in my latest suggestion.

Now, if the values contained something that related each one to it's group, such as a letter designation specific to each group, or a specific number of characters for each group, or something like that, then we might be able to have Excel determine the groups with a formula. However, absent something that Excel can look for to determine which group a value belongs in, we have to tell Excel which group it's in and also relate that group to the appropriate table.

That is what I was doing with my Named Ranges and VLOOKUP.


Report •

#18
July 27, 2010 at 15:57:42
i'm still failing to produce the results, but i think i've located my error. my VLOOKUP table array is wrong somehow. i'm going to work on it more tonight and post when i've gotten the result i'm looking to attain, or when my brain explodes! thanks for all your help - vince

Report •

#19
July 27, 2010 at 18:28:49
re: "my VLOOKUP table array is wrong somehow."

Obviously I can't diagnose your problem from afar, but in my opinion, the VLOOKUP portion of this is probably the easiest thing to get right.

Test it by itself. Just use an isolated VLOOKUP formula on the "Group Number" table and see if it returns the correct values.


Report •

#20
July 28, 2010 at 06:19:02
I don't know if this will help, but I added an IF statement to my earlier formula to help let you know if a problem exists with the VLOOKUP portion.

What this will do first verify that the value you have entered in C1 exists in the table_array. If it exists, it will import the corresponding table. If it doesn't, it will return "C1?" to let you know that the value in C1 does not exist in the table_array.

=IF(ISNA(VLOOKUP($C$1,$A$1:$B$11,2,0)),"C1?",OFFSET(INDIRECT("Table"&VLOOKUP($C$1,$A$1:$B$11,2,0)),ROW()-16,COLUMN()-9))


Report •

#21
July 29, 2010 at 16:38:13
i'll give this a whirl. i'm making progress with the last suggestion. this one might get me over the hump. tomorrow is dedicated to solving this, so i'll post my results. thx- vince

Report •

#22
July 29, 2010 at 18:22:42
I don't mean anything by this, but I'm wondering why this is so hard for you to get working.

It took me all of about 5 minutes to set up three tables on Sheet 2 and import them into Sheet 1 using a list of Values and Group Numbers as described in Response 14.

What is going wrong with your trials?


Report •

#23
July 30, 2010 at 08:44:47
it works!!! consider it solved! thanks for your help!!!

Report •

#24
July 30, 2010 at 09:34:12
I'm glad!

Report •

Ask Question