Hello,

I am really new to excel and playing around with a calculator of sorts. What I am trying to do is autofill a specific cell (C2) of a calculator. I made a separate table on the same sheet that has 6 columns, M2:R:30.

I thought that if I could place an "x" in any cell of R2:30, I could have a forumla look for the x and then autofill C2 with data from the cooresponding row. So for example place "x" in R2 and the data from M2 gets populated in C2. This is what I have so far. The problem I have is that it works by only putting the data from M2 into C2, no matter where I place the "x" in R.

=IF(VLOOKUP("x",R2:R30,1,)="x",M2:M30," ") I also tried using HLOOKUP and failed.

I have 4 separate cells that I would like to do this for, C2 cooresponds to the data in M2:M30, D2 for N2:N30, C3 for O2:030, and D3 for P2:P30. Once I get the initial formula correct I should be able to progress.

Thanks a lot and I hope what I wrote is not to confusing.



Just thought of an easier way to ask.


If I place an "x" in any cell of column R that would return the data in the same row of column M to C2.

On Wednesday, September 26, 2012 2:37:37 PM UTC-7, JamesTris wrote:
Hello,



I am really new to excel and playing around with a calculator of sorts.

What I am trying to do is autofill a specific cell (C2) of a calculator.

I made a separate table on the same sheet that has 6 columns, M2:R:30.



I thought that if I could place an "x" in any cell of R2:30, I could

have a forumla look for the x and then autofill C2 with data from the

cooresponding row. So for example place "x" in R2 and the data from M2

gets populated in C2. This is what I have so far. The problem I have is

that it works by only putting the data from M2 into C2, no matter where

I place the "x" in R.



=IF(VLOOKUP("x",R2:R30,1,)="x",M2:M30," ") I also tried using HLOOKUP

and failed.



I have 4 separate cells that I would like to do this for, C2 cooresponds

to the data in M2:M30, D2 for N2:N30, C3 for O2:030, and D3 for P2:P30.

Once I get the initial formula correct I should be able to progress.



Thanks a lot and I hope what I wrote is not to confusing.







Just thought of an easier way to ask.





If I place an "x" in any cell of column R that would return the data in

the same row of column M to C2.









--

JamesTris

Hi JamesTris,

Without a full grasp of your sheet layout I'm guessing you want some variation of this Arra-Entered VLookup formula.

J2 in this formula would be the "x" on your sheet, the lookup value.

A30:E49 is the table array from which you want to extract multiple cell data.

{2,3,4,5} are the columns to the right of the lookup value cell that you want returned.

The 0 will return an exact match. And the iserror will return blank cells if no match is achieved.

=IF(ISERROR(VLOOKUP(J2,A30:E49,{2,3,4,5},0)),"",VL OOKUP(J2,A30:E49,{2,3,4,5},0))

Entering can be a bit tricky but with this formula you would select four cells in the same row across four columns and while still selected (highlighted) click in the formula bar and enter the formula. Once the formula is entered DO NOT HIT ENTER!

You now hold down thr Ctrl and the Shift keys and now hit Enter. This is called Array Enter. Excel will encase the formula (which will show up in all four cells) with { } brackets. The data returned will show up in each of the four cells representing the info in the second, third, fourth and fifth columns of the table.

If you need to make changes to the formula, select ALL FOUR of the cells as you did to enter the formula and make the changes to the formula shown in the formula bar. Ctrl + Shift + Enter to activate the formulas and notice the { } will reappear around the formulas.

So, while my example looks like this {2,3,4,5}, yours if I understand correctly will have 29 colums {2,3,...28,29} I've never done that many but it will probably work ok.

Good luck.

egards,
Howard

On Wednesday, September 26, 2012 2:37:37 PM UTC-7, JamesTris wrote:
Hello,



I am really new to excel and playing around with a calculator of sorts.

What I am trying to do is autofill a specific cell (C2) of a calculator.

I made a separate table on the same sheet that has 6 columns, M2:R:30.



I thought that if I could place an "x" in any cell of R2:30, I could

have a forumla look for the x and then autofill C2 with data from the

cooresponding row. So for example place "x" in R2 and the data from M2

gets populated in C2. This is what I have so far. The problem I have is

that it works by only putting the data from M2 into C2, no matter where

I place the "x" in R.



=IF(VLOOKUP("x",R2:R30,1,)="x",M2:M30," ") I also tried using HLOOKUP

and failed.



I have 4 separate cells that I would like to do this for, C2 cooresponds

to the data in M2:M30, D2 for N2:N30, C3 for O2:030, and D3 for P2:P30.

Once I get the initial formula correct I should be able to progress.



Thanks a lot and I hope what I wrote is not to confusing.







Just thought of an easier way to ask.





If I place an "x" in any cell of column R that would return the data in

the same row of column M to C2.









--

JamesTris

On Wednesday, September 26, 2012 2:37:37 PM UTC-7, JamesTris wrote:
Hello,



I am really new to excel and playing around with a calculator of sorts.

What I am trying to do is autofill a specific cell (C2) of a calculator.

I made a separate table on the same sheet that has 6 columns, M2:R:30.



I thought that if I could place an "x" in any cell of R2:30, I could

have a forumla look for the x and then autofill C2 with data from the

cooresponding row. So for example place "x" in R2 and the data from M2

gets populated in C2. This is what I have so far. The problem I have is

that it works by only putting the data from M2 into C2, no matter where

I place the "x" in R.



=IF(VLOOKUP("x",R2:R30,1,)="x",M2:M30," ") I also tried using HLOOKUP

and failed.



I have 4 separate cells that I would like to do this for, C2 cooresponds

to the data in M2:M30, D2 for N2:N30, C3 for O2:030, and D3 for P2:P30.

Once I get the initial formula correct I should be able to progress.



Thanks a lot and I hope what I wrote is not to confusing.







Just thought of an easier way to ask.





If I place an "x" in any cell of column R that would return the data in

the same row of column M to C2.









--

JamesTris

Hi again JamesTris,

After I sent my post I set about to try to set up a working sample sheet the best I could from your post. I got hopelessly lost on how the sheet should look and what needs to happen and where... etc.

I would be glad to look at an example worksheet and give it a shot if you want.
I'm at

Regards,
Howard

Hi James,

Am Wed, 26 Sep 2012 21:37:37 +0000 schrieb JamesTris:

I thought that if I could place an "x" in any cell of R2:30, I could
have a forumla look for the x and then autofill C2 with data from the
cooresponding row. So for example place "x" in R2 and the data from M2
gets populated in C2. This is what I have so far. The problem I have is
that it works by only putting the data from M2 into C2, no matter where
I place the "x" in R.

I have 4 separate cells that I would like to do this for, C2 cooresponds
to the data in M2:M30, D2 for N2:N30, C3 for O2:030, and D3 for P2:P30.
Once I get the initial formula correct I should be able to progress.

in C2 try:
=INDEX(M2:M30,MATCH("x",$R$2:$R$30,0))
in D2:
=INDEX(N2:N30,MATCH("x",$R$2:$R$30,0))
and so on


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thank you for trying Howard, appreciate it.



Thank you so much Claus that worked out great. I really appreciate it.