IF/AND Formula in Multiple Tabs

Does anyone know if it's even possible to create an IF/AND formula that draws data from multiple tabs?

For example,

(assume there are tabs named exactly the same as each of the data entries in column A)

IF A2 = "ARI" then D2 = ARI!H3. Is this possible?

Hi,

What is the logic for ATL,BAL, BOS.etc, do you just required Address(ARI!H3) in D2 or the Value of ARI!H3.

Punnam

Let's say the value of A2 = "ARI". I would like to display =ARI!H3. But let's say I change the value of A2 = "ATL". I would like to display =ATL!H3.

Hi,

The work i have done has no logic , but as per u r requirement i have provided it.


Punnam

I'm not sure how to use that formula to do what I asked. I entered it in my spreadsheet and nothing happened.

Hi,

I have used a & , even u can try CONCATENATE formula to achieve this but only one thing at the place of 3, use ROW()+1,

Punnam

Let's try this.

In this example:
A2 = "ari" then D2 = "=ari!D1"
A3 = "atl" then D3 = "=atlD1"


Is there a formula I can put in D2 & D3 that will automatically switch their values if I switch A2 & A3?

So,

A2 = "atl" then D2 = "=atl!D1"
A3 = "ari" then D3 = "=ari!D1"

Hi Quibility,

I am unable to analyze you are requirement, many things are possible with excel, as per your latest thread we can make use of Indirect, address, lookup,Vlookup, Index, Match and many other function which will help in transfer data from one sheet to another. Provide the real raw data i can help you.

Punnam

Tony, that's perfect. Do you think you could help me take this equation a whole lot further?

Could you write a formula that incorporates A1, A2, B2, & C2?

A1 would determine the value in column "A" in each tab in which the data would represent (A1-1)
A2 would determine the tab in which the data came from
B2 & C2 would determine the columns in which the data would come from
IF B2=H & C2=W then display N3, O3, P3, Q3, R3, S3 in D2, E2, F2, G2, H2, I2
IF B2=H & C2=<>W then display Z3, AA3, AB3, AC3, AD3, AE3 in D2, E2, F2, G2, H2, I2
IF B2=@ & C2=W then display T3, U3, V3, W3, X3, Y3 in D2, E2, F2, G2, H2, I2
IF B2=@ & C2=<>W then display H3, I3, J3, K3, L3, M3 in D2, E2, F2, G2, H2, I2


If that's not clear, here's an example:

In the attached file A1=7, A2=ATL, B2=H, & C2=W. So the formula I'm looking for would find the data in tab "ATL" (A2), row 17 (A1-1; 7-1=6, so row 17 because A17 = 6), and display columns N17, O17, P17, Q17, R17, & S17 because B2=H and C2=W.

I hope that is clear! I know it's a lot, but this would be a HUGE help!

This should work.

B2's "H" tells me I'm looking for data that falls under the headers that have an "H" (i.e. W→W H, W→L H, L→W H, & L→L H). C2's "W" tells me I'm looking for data that falls under the headers that have a "W" to left of the arrow (W→W H, W→L H, W→W @, & W→L @). Combined (B2=H and C2=W), means I'm looking specifically for the overlapping data (W→W H & W→L H) under the header .

Oh, OK. I understand. Think of
1) L→W @ & L→L @,
2) W→W H & W→L H,
3) W→W @ & W→L @,
4) L→W H & L→L H

as 4 different headers. The six cells under each header are the desired formula results.

So, one of four combinations of B2 & C2 will determine which six cells are displayed.

B2: H, C2: W means looking for the data under header W→W H & W→L H,
B2: H, C2: L means looking for the data under header L→W H & L→L H,
B2: @, C2:W means looking for the data under header W→W @ & W→L @,
B2: @, C2: L means looking for the data under header L→W @ & L→L @.

There are only 4 possible combinations. The end result of the formula is to display 1 of 4 possible strings of data (6-consucutive cells ).

N3, O3, P3, Q3, R3, S3
Z3, AA3, AB3, AC3, AD3, AE3
T3, U3, V3, W3, X3, Y3 or
H3, I3, J3, K3, L3, M3



Which 6-consecutive cells are displayed is determined by A1, A2, B2, & C2.

A1 would determine the value in column "A" (the row) in each tab in which the data would represent (A1-1)
A2 would determine the tab in which the data came from
B2 & C2 would determine which one of the four 6-consecutive cells is displayed.

B2: H, C2: W means looking for the data under header W→W H & W→L H (N3, O3, P3, Q3, R3, S3)
B2: H, C2: L means looking for the data under header L→W H & L→L H (Z3, AA3, AB3, AC3, AD3, AE3)
B2: @, C2:W means looking for the data under header W→W @ & W→L @ (T3, U3, V3, W3, X3, Y3)
B2: @, C2: L means looking for the data under header L→W @ & L→L @ (H3, I3, J3, K3, L3, M3)

If you look at the example, you'll see that there are 30 entries in "Sheet1" in column "A". The idea is to copy paste the formula from D2:I2 all the way down to D31:I31 so each value in column "A" will produce the 1 of 4 6-consecutive cells based upon each row's data.

So for A3's data: the cells determining which 6-consecutive cells are displayed are A1 (row), A3 (tab),B3, & C3 (columns).
And for A4's data: the cells determining which 6-consecutive cells are displayed are A1 (row), A4 (tab),B4, & C4 (columns).
So on until A31's data: the cells determining which 6-consecutive cells are displayed are A1 (row), A31 (tab),B31 & C31 (columns).

I AM really into baseball!


OH, excuse me. I made a typo in my posts where I list "L" as a possible value of C2. It should read

IF B2=H & C2=W
IF B2=H & C2=<>W
IF B2=@ & C2=W
IF B2=@ & C2=<>W

I COULD put an "L" in C2.... it doesn't matter. Either C2=L or C2=<>W is the same thing.

That's insane that you can do this! I wish I knew how to write these types of formulas!

That's right! Wow. Just, wow. I don't know if computing that formula was difficult, but I am thoroughly impressed.

Now, I don't want to push my luck, but I was hoping you could help me improve this a bit...?


Keeping the formulas in D2:I2 (they stay the same), pretend the cells in B2 and C2 were empty. Could you write a formula for B2, C2 and J2 so that:


B2 = column "C" data in team tab (in the example B2 would = C17 because row 17 is determined by A1 (A1=6), this time it's just A1, and not A1-1) THE ONLY "catch" with B2 is that in column "C" (in the team tabs) there's only "@" and "0", "0" would have to = "H" in order for D2:I2 formula to work, or I guess the formula in D2:I2 could just be modified to change "H" to "0".... either way
C2 = column "F" data in team tab (in the example C2 would = F17 because row 17 is determined by A1 (A1=6), this time it's just A1, and not A1-1)
J2 = column "G" data in team tab (in the example J2 would = G17 because row 17 is determined by A1 (A1=6), this time it's just A1, and not A1-1)
A2 is the only information that would be entered manually and it would tell the formulas in B2, C2, D2:I2, & J2 which tab the information can be found


So, the end result would be the manual entering of two cells: A1 & A2 and then automatically cells B2:J2 would generate based upon only those two cells. I feel like this would be a no brainer after that last formula!

Tony, you really are a forum guru! I can't thank you enough.

Oh, wait. I found ONE hiccup. When C2's formula returns a "W" the formulas in D2:I2 don't register it, thus the formulas in D2:I2 treat the value in C2 as "<>W". Any ideas?

Wait... nevermind... the problem could be in how I'm copying/pasting...

Yep, that was it.

One LAST question. (I know I sound like Columbo, but for real this time)!

When I copy/paste the D2:I2 formulas to cells to the right, the formula doesn't work properly. I have to individually copy paste each formula from D2:I2 and then copy/paste down again. Is there a way to just copy/paste those formulas to the right without individually copying/pasting?

You know what? Nevermind. I got it. Thanks.