IF---Then formula

[Flower R]

I have two sheets, one is named "details" and other is "query". The data in sheet "details" is listed in columns A, B,C, then D is blank and again data is in columns E,F.G. (in text forms)

In the Query Sheet, wish to enter in A1 the value contained in A, B or E, F of "details" sheet, the result of columns C or G may appear in Column B1 of Query sheet and also if value is equal to C or G, the result of A,B, or E,F may appear:

=IF(AI=A1& B1,E1,F1!Details, etc. etc. Thanks and sorry if could not explain better.

[sglife]

Thanks and sorry if could not explain better

Then why not attach a file?

[Flower R]

Then why not attach a file?

Actually the file is not readily available due to weekend.

Basically it is getting data from one sheet to another with the help of a formula. Thanks for replying.

[logic and macros]

http://macro.withmrlee.com/sheetsandcell.html

That should cover it.

[teylyn]

http://macro.withmrlee.com/sheetsandcell.html

That should cover it.

The link does not work. The website itself is full of bad links, too. Not the best resource for Excel and definitely not the best resource to get someone started in VBA.

Apart from that, I don't think the solution requires macros at all. It will probably be a combination of If statements and Vlookups.

I agree that a worksheet with an example would help, because the explanation in post #1 is not clear enough.

Teylyn

[Flower R]

Apart from that, I don't think the solution requires macros at all. It will probably be a combination of If statements and Vlookups.

I agree that a worksheet with an example would help, because the explanation in post #1 is not clear enough.

Thanks for the reply. I give below an example of worksheet named details:

A B C D E F

1. 1234 4321 Grocery 0981 3216 Fishing

2. 7869 6987 Building mat 5982 0001 Bakery


In Query sheet, I wish to type in A1, contents of A or B or D or E, in B1 the formula may retrieve the value of C or F. Example:

If I type 1234 or 4321 the value in BI may be Grocery.

And also if in A1 is typed the value of C or F, (full or part word like build instead of building) the answer may be the contents of , A,B,D, and E in B1 and C1: Example:

If I type the word Building mat or Build the answer may be 7869 and 6987 in BI and CI.

I hope it is clear now. Thanks.

[teylyn]

Here's a starting point for you

http://chandoo.org/wp/2008/11/19/vlo...-spreadcheats/

If you understand Vlookup, Match and Offset, you should be on your way to a solution.

[Flower R]

Here's a starting point for you

http://chandoo.org/wp/2008/11/19/vlo...-spreadcheats/

If you understand Vlookup, Match and Offset, you should be on your way to a solution.

Thanks but am not so much expert in constructing formulas. If you can help me out with one. Please also guide in which column of Query sheet the formulas have to be inserted and can they be copied and dragged down for other columns also. Thanks once again.

[Flower R]

I tried the following:

=VLOOKUP(A1,Details!A1:F10,2,FALSE) and inserted this formula in Query Sheet B1 column. Although it worked for values of A1 in Details sheet, but not for B, C, D,E,F etc values. For example if I typed:

1234 it gave me value of 4321 but if I typed 4321 it did not give anything like value of C.

I suppose some more suitable formula needed. Thanks for giving that link it is very informative.

[teylyn]

Hi,

the thing you're after can not be done with a simple, short and sweet formula. If you try to say it in plain English

Please Login or Register  to view this content.

..... and so on. Putting this into Excel requires a lot of nested If statements and Vlookups. A single Vlookup won't do.

Maybe a combination of Index and Match will do the trick shorter, but you'll still need nested ifs, since you're testing against values from five different columns on sheet 2

cheers

teylyn

[Flower R]

Hi,

the thing you're after can not be done with a simple, short and sweet formula. If you try to say it in plain English

Please Login or Register  to view this content.

..... and so on. Putting this into Excel requires a lot of nested If statements and Vlookups. A single Vlookup won't do.

Maybe a combination of Index and Match will do the trick shorter, but you'll still need nested ifs, since you're testing against values from five different columns on sheet 2

cheers

teylyn

I think it is beyond my capability, so I give up. Thanks.