Multiple sheet lookup formula

Skinn · Aug 25, 2011

Hi

Please be gentle, i'm totally new to excel.

I'm after a formula that will display a range of text from cells in a different sheet, depending on a check list in yet another spreadsheet.

Spreadsheet 1 is a checklist with a list of headings A3:A10 that correspond to headings in sheet 2. I've put check boxes next to the headings in B3:B10.

Spreadsheet 2 has the same headings, but there is information under each heading, 3 cells down and 1 across. for example the heading is A1, the information in A2:B4. next heading is A6, info A7:B9

Spreadsheet 3 is where i want to generate a report that shows the headings & info from spreadsheet 2, but only if the corresponding heading from the checklist in sheet 1 is unchecked.

I'm sure this is doable, as i am learning, almost anything is with excel.

Thanks in advance

Skinn

John Davis · Aug 25, 2011

Skinn said:
 Hi

Please be gentle, i'm totally new to excel.

I'm after a formula that will display a range of text from cells in a different sheet, depending on a check list in yet another spreadsheet.

Spreadsheet 1 is a checklist with a list of headings A3:A10 that correspond to headings in sheet 2. I've put check boxes next to the headings in B3:B10.

Spreadsheet 2 has the same headings, but there is information under each heading, 3 cells down and 1 across. for example the heading is A1, the information in A2:B4. next heading is A6, info A7:B9

Spreadsheet 3 is where i want to generate a report that shows the headings & info from spreadsheet 2, but only if the corresponding heading from the checklist in sheet 1 is unchecked.

I'm sure this is doable, as i am learning, almost anything is with excel.

Thanks in advance

Skinn

Not too sure I understand, but does this help??

In Sheet 3

=IF(HLOOKUP(A$1,Sheet1!A$1:A$2,2,FALSE)<>"X",HLOOKUP(A$1,Sheet2!A$1:A$5,ROW(),FALSE),"")

Skinn · Aug 25, 2011

G'day mate

Thanks for the help.

Tried that, but no go!

Bear in mind that the headings in Sheet1 are A3:A10, and the check boxes B3:B10

The data in Sheet2 that needs to be displayed in Sheet3 if unchecked in Sheet1, are in cells A1:B4, A6:B9, A13:B16, etc.

Would it be easier if i merged the info in Sheet2 into 1 cell per heading?

John Davis · Aug 26, 2011

Skinn said:
G'day mate

Thanks for the help.

Tried that, but no go!

Bear in mind that the headings in Sheet1 are A3:A10, and the check boxes B3:B10

The data in Sheet2 that needs to be displayed in Sheet3 if unchecked in Sheet1, are in cells A1:B4, A6:B9, A13:B16, etc.

Would it be easier if i merged the info in Sheet2 into 1 cell per heading?

Sorry for the late response and not taking more time to read your thread. I'm way off. I'll take another look at later, if you receive no other replies.

John Davis · Aug 26, 2011

Skinn said:
G'day mate

Thanks for the help.

Tried that, but no go!

Bear in mind that the headings in Sheet1 are A3:A10, and the check boxes B3:B10

The data in Sheet2 that needs to be displayed in Sheet3 if unchecked in Sheet1, are in cells A1:B4, A6:B9, A13:B16, etc.

Would it be easier if i merged the info in Sheet2 into 1 cell per heading?

How is the data displayed in Sheet3? Same as in Sheet1 or Sheet2. Best too provide a visual representation of the data.

Skinn · Aug 29, 2011

John Davis said:
How is the data displayed in Sheet3? Same as in Sheet1 or Sheet2. Best too provide a visual representation of the data.

Would be good if i could upload an attachment!!!!

Whats the go with that?

Anyway, this is what i was after;

=IF(LEN($A3)>0,INDEX(List!B$1:B$41,SMALL(IF(List!$A$1:$A$41=$A3,ROW(List!$A$1:$A$41)),1)+1),"")

Multiple sheet lookup formula

Skinn

New Member

Excel Facts

John Davis

Well-known Member

Skinn

New Member

John Davis

Well-known Member

John Davis

Well-known Member

Skinn

New Member

Similar threads

Forum statistics

Share this page

Multiple sheet lookup formula

Skinn

New Member

Excel Facts

John Davis

Well-known Member

Skinn

New Member

John Davis

Well-known Member

John Davis

Well-known Member

Skinn

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock