各シートの特定のセルを返したい

質問

各シートの特定のセルを返したい

積算されたのを各個人のシートを制作しています。20人程
例　シート名　「大阪」

各シート内検索条件セル　V44に「大阪　太郎」　全員同じセルに各人名をいれています。
その中でセルW58　X58　Y58をSheet1に返したい

Sheet1は
A列にシート名を固定する「大阪」
B列に検索条件の「大阪　太郎」
H列に検索条件の大阪　太郎の「W58」を返す
I列に検索条件の大阪　太郎の「X58」を返す
J列に検索条件の大阪　太郎の「Y58」を返す

以上のことを行いたいのですが、先日このサイトで教えて頂いた
=IF($A2="","",IF(COUNTIF(INDIRECT(B$1&"!A:A"),$A2),VLOOKUP($A2,INDIRECT(B$1&"!A:D"),4,0),"-"))

を改造すれば、なんとか行き着けそうなのですが、うまくいきません。
どうぞ、お知恵をお貸し頂けませんでしょうか？

回答No.1

tom04 · ベストアンサー

こんばんは！
外していたらごめんなさい。

希望としてはINDIRECT関数を使い、オートフィルで列方向に別Sheetのデータを表示させたい！
ということですかね？

一応そういうことだとして・・・
行方向に関してはオートフィルで簡単にできると思いますが
列方向はオートフィルでコピーしても列番号が簡単に変化してくれないみたいですね！
（できる方法があればごめんなさい。）

Sheet1のB列に関しては問題ないと思います。
B2セルに
=IF(A2="","",INDIRECT(A2&"!$V$44"))
としてオートフィルで下へコピー！

問題はH～J列です。
苦肉の策です。

1行目はタイトル行で、2行目からの表示とします。
Sheet1のH2セルに
=IF($A2="","",OFFSET(INDIRECT($A2&"!W58"),,COLUMN(A1)-1,,1))
としてJ2セルまでオートフィルでコピーした後に
H2～J2セルを範囲指定し、J2セルのフィルハンドルで下へコピーしてみてください。

これで何とか希望に近い形にならないでしょうか？

以上、参考になれば良いのですが
他に良い方法があればごめんなさいね。m(__)m

お礼

早速のご回答ありがとうございます。

Tom04さん　凄すぎです。私の拙い文章にもかかわらず、私の希望としていることを的確に見ぬいて頂きさらに完璧な式のご回答

B列は手入力するつもりが、まさかそこも自動にできるなんて感激です。
この2日感動させらっぱなしです。本当にありがとうございます。
そして非常に勉強になります。

シートの表としては完成したのですが、OFFSETとCOLUMNをググリながら勉強しているのですが、今回の関数式はなかなか解読が難しいです。

ゆっくり勉強してみます。

本当にありがとうございます。

補足

OFSETとCOLUMNについて

もっと簡素化したらこれでいいのでは？
=IF($A8="","",INDIRECT($A8&"!W58"))
と思ったんですが、これだとW58をX58、Y58に変更する必要がでてくるんですね！

さらになぜCOLUMN-1とされているか初め意味がつかめなかったのですが、こちらも0と入力してしまうと
コピー後に1、2と修正が必要になりました。

OFSETとCOLUMNを合わせて使う事により単にコピーをするだけですんなり問題なく出来るように式を作って頂いていのですね！！！

謎が解けてさらにこの式の素晴らしさ、簡単コピー操作で問題クリアを可能にしている気配りさに感動です。

とても素晴らしい関数ありがとうございます。

回答No.2

kagakusuki

今仮に、Sheet1の2行目からリストが始まっているものとします。
　まず、B2セルに次の数式を入力して下さい。（" "内の内容はセルが変わっても変化しないため、INDIRECT関数内の" "で囲んだ部分は$が無くても絶対参照になります）

=IF(ISERROR(INDIRECT($A2&"!V44")),"",INDIRECT($A2&"!V44"))

　次に、C2セルに次の数式を入力して下さい。

=IF($B2="","",INDIRECT($A2&"!R58C"&COLUMN(W2),FALSE))

　続いて、C2セルをコピーして、D2セルとE2セルに貼り付けて下さい。
　そして、B2～E2の範囲をコピーして、同じ列の3行目以下に（人数を上回るのに充分な行数になるまで）に貼り付けて下さい。

　以上です。

お礼

ありがとうございます。

すごい！
更にエラーを表示させない方法ですね。

勉強になります。