複数シートに跨る数式参照について

質問

現在、1つのファイルにAシート、Bシートが存在します。

Aシートは、Bシートから値を参照し、数式、条件付き書式、
VBAにて作成されているシートになります。
Bシートにはデータが格納されています。

今回、新しくシートが4つ増えます。(C,D,E,Fとします)
新シートの役割としては、Bシートと同じくデータが登録されます。

そこで質問なのですが、Aシートで全てのシート(B,C,D,E,F)の
データを参照をするにあたり、数式を簡単に設定する方法があるか知りたいです。

B,C,D,E,Fシートは、フォーマットが違うため、同じ項目を参照するのでも
Bシートでは、H列を参照。Cシートでは、I列を参照など条件が変わるため
シート名を覚えておく方法では、うまくいかず、結局数式でIF ELSEIF と
数式を作成する必要があり、参照数も多いためとても手間になります。

【条件】
・始めにB～Fのどのシートを使用するか選択をします。
選択をしたシート名等は格納しておけます。
・VBAは使用可能です。
・参照数は500～1000セル程度。
・できるだけ後からメンテしやすい形にしたい。
・条件付き書式はAシートのみで完結しているため、影響はないと思います。

最悪、Aシートを5ケース分作成し、使用シートを選択時にシートの表示・非表示を
切り替えるしかないと今は、考えています。

知恵をお貸し頂ければと思います。

回答No.4

GOCHISOUda

Ｎｏ１、２のコードでＶＢＡが読めていないのではと
思われますので関数をお勧めしています。
また、ＶＢＡで組む方が後々メンテが面倒ですよ。

あと分からないのがシートＡのリストから１つを選択して参照するのか
Ａのリスト全部を参照するのかが分かりません。

いずれにしてもＶＬＯＯＫＵＰやＩＮＤＥＸでいけるのでは？
（ＩＮＤＥＸは更にＭＡＴＣＨと合わせる）
どちらの関数も範囲のところはＩＮＤＩＲＥＣＴ関数を使えばいいと思います。
例えば、セルＡ２でシート名を入力（ドロップダウンリストとか）するとして
ＩＮＤＩＲＥＣＴ（Ａ２＆”商品名”）とすれば、
シート名商品名（！マークは付けない）のセル群を選択してくれます。

回答No.3

GOCHISOUda

まず、質問を読み違えていました。謝ります。

質問者さんがやりたいことはＶＬＯＯＫＵＰ関数でいいｓのではと考えます。

シートＡにミカンという商品名があって、
シートＢからミカンを引っ張ることはないかと思います。
（同じ名前をひっぱても意味がない）
たぶん、ミカンの産地、値段、入荷日などのデータを引っ張ると考えます。
そうなるとＶＬＯＯＫＵＰ関数じゃないでしょうか。

ついでに追加の質問についてですが、
範囲という意味ですよ。

お礼

回答ありがとうございました。

恐らくですが、No.1のほうが私の望む回答として近いです。
私の説明が分かり難いのが問題だと思いますが下記例を参考して下さい。

・Aシートを作成するのに対し、参照するシートが5シート(B,C,D,E,F)存在します。
・始めに5シートからどのシートを参照するか選択させます。

　例：商品名は、BシートではH6～H206まであります。
　　　Cシートでは、I10～I210まであります。
　　　Dシートでは、J50～J250まであります。

この場合に、AシートのA5～A205までを商品名の参照としたいときに
どのように値を参照するのが効率の良い方法かを教えていただきたいです。

AシートのA5セルに下記のようにIf文を駆使すればできるのはわかりますが、
今後のメンテ等考えて、下記のようにはしたくありません。
If(選択シート=B,Bシート!H6,If(選択シート=C,Cシート!I10,If(選択シート=D,Dシート!J50・・・

上記から、No.1の回答にあった、セル範囲に名前を付けて
BシートのH6～H206、CシートのI10～I210に「商品名」という名前を付けて
Aシートに「商品名」という名前で値が参照できる方法があるのであれば
教えて頂きたいです。

回答No.2

TAKA_R

コンピュータに自動的に探させてはどうでしょう。
（配列を使って全指定という手もありますが。）
今マイブームがfor each なので、私なら←これを使って検索させますけど・・・。
bにワークシート名が、全シート１行目にデータラベルが入っているとして。

dim a as range
for each a in worksheets(b).range("1:1")
if a.value="商品名"then exit for
next a

これから先、列番号を利用したくなったら、「a.column」で使用可能。

回答No.1

GOCHISOUda

多分エクセルの話なんですよね。

セル範囲に名前を付けてはどうでしょう。
でも、同じ名前はシートが違っても同じ部分になるので
シート名＆セル範囲として名前を付けます。

設定は各シートで手作業かな。

例えば
Rname=Activesheet.name & "セルの名前”
Activesheet.Range(Rname).Select
Selection.Find("探しもの").Select

最後に、”セルの名前”はメリット・デメリットも勉強して下さいね。

お礼

回答ありがとうございます。
すいません、EXCELのお話です。書き忘れてました。。。

1点、質問があります。回答頂けると助かります。

例えばですが、
Bシートに商品名という列がH列にあると仮定します。
Cシートの商品名は、I列にあるとします。
なお、商品名は最大200個あるとします。

この場合に、Aシートは、商品名をただ単に参照表示させるだけだとすると、
BシートのH列の1行目に「Bシート!商品名1」という名前で定義し、2行目に「Bシート!商品名2」
CシートのI列の1行目に「Cシート!商品名1」という名前で定義し、2行目に「Cシート!商品名2」
などと名前の定義が必要になるのでしょうか？

それとも範囲指定で一括設定ができるのでしょうか？
※定義が範囲指定できるのは分かりますが、参照の際にどうすればよいかわかりません。

名前定義となるとリストや、SUMなどの集計用に使うイメージが
強いため、ただ単に参照させるなどの場合に簡単に設定する方法があるのか
お伺いしたいです。