Excel - Find and Replace issue

[Ramzon]

I've got multiple spreadsheets for tracking various different departments budgets. Each of these spreadsheets have a column for our internal Purchase Order Number.

 

When our finance officer wants me to look up an order number, I often can't remember what order number went into which department's spreadsheet - as such, I need to open each spreadsheet and run a CTRL + F on each individual spreadsheet until I find it.

 

In order to save time, I made another spreadsheet purely for tracking each order number for each department. The spreadsheet has a table - below is an example.

 

[TABLE=class: grid, width: 500, align: left]

[TR]

[TD]Media[/TD]

[TD]Science[/TD]

[TD]ICT[/TD]

[TD]Music[/TD]

[TD]Drama[/TD]

[/TR]

[TR]

[TD]PO-001[/TD]

[TD]PO-005[/TD]

[TD]PO-009[/TD]

[TD]PO-013[/TD]

[TD]PO-017[/TD]

[/TR]

[TR]

[TD]PO-002[/TD]

[TD]PO-006[/TD]

[TD]PO-010[/TD]

[TD]PO-014[/TD]

[TD]PO-018[/TD]

[/TR]

[TR]

[TD]PO-003[/TD]

[TD]PO-007[/TD]

[TD]PO-011[/TD]

[TD]PO-015[/TD]

[TD]PO-019[/TD]

[/TR]

[TR]

[TD]PO-004[/TD]

[TD]PO-008[/TD]

[TD]PO-012[/TD]

[TD]PO-016[/TD]

[TD]PO-020[/TD]

[/TR]

[/TABLE]

 

The cells below the departments name pick up the values by referencing the other spreadsheets. For example, the first Order Number of the Media column would have a reference of

[b]=('[Media - Budget & Orders.xlsx]Admin'!A2)[/b]

 

This all works fine, and the spreadsheet does exactly what I want it to. With this, my method of saving time, when asked for a reference number and which department it belongs to, was to open this spreadsheet and press CTRL + F (Find and Replace) then search for the Order Number.

 

The problem is that this doesn't work. If I search for 'PO-001', no results are found, despite A2 clearly having a value of what I searched for. The cells data of 'PO-001' is brought in via the reference above - if I search for the reference of

[b]=('[Media - Budget & Orders.xlsx]Admin'!A2)[/b]

it brings me to the correct cell and value, but clearly this won't work for what I'm wanting to do.

 

Is there a way to make this work? Is there an easier way?

[blueday]

So when you bring up Find, have you tried clicking on Options to see the further options and choosing Values instead of Formulas?

 

[Ramzon]

blueday - I hadn't! That worked perfectly for me - I now feel a little daft for missing that.

 

Thank you for pointing this out to me!

[Ditto]

Love an excel challenge. Attached is an example sheet where you can type in you PO number and have it return your department. It's quite a complex formula for what is a simple functional requirement, but Excel can be like that.

HeaderLookup.xlsx

[Ramzon]

Ditto - this is fantastic! I've integrated your complex formula into my Purchase Order Number Lookup spreadsheet by editing the values from your example. Figuring out which numbers/cell references to edit in your formula hit the limit of my Excel skills!

 

Thank you very much.

[Ramzon]

Ditto,

 

I don't suppose there is a way to alter the formula you provided me to return 'NO RESULTS' when the search cannot find an order number throughout the spreadsheet?

 

The formula (slightly edited from what you provided) is currently this:

 

=@INDEX($A$3:$E$3,SUMPRODUCT(MAX(($A$4:$E$200=B2)*(COLUMN($A$4:$E$200))))-COLUMN($A$1)+1)

 

Any help would be greatly appreciated!

 

Thanks

[Ditto]

There might be a neater way, but quick and dirty fix in the original would look like this. Essentially, 0 is returned from SUMPRODUCT for not found, so working with that.

=IF(SUMPRODUCT(MAX(($A$2:$E$5=B9)*(COLUMN($A$2:$E$5)))) = 0, "No matches", INDEX($A$1:$E$1,SUMPRODUCT(MAX(($A$2:$E$5=B9)*(COLUMN($A$2:$E$5))))-COLUMN($A$1)+1))

[Ramzon]

I've only just had a chance to play with this. As expected, it works exactly as I had hoped! Thank you very much for all your help!