Searching for text on a multi-sheet workbook

[Gaztech]

Hi,

I'm having an odd issue with finding stuff on a worksheet...

I have a workbook that has a master sheet and several sub-sheets.

Essentially, this is a pricelist that has the products and standard pricing on the master sheet and the sub sheets (all in the same workbook) have specific pricing for certain clients.

The data (i.e. the master descriptions, product codes and base pricing) for the sub-sheets comes from the master sheet and is displayed on the sub sheets using sheet references on the sub-sheets (so that I can change something on the master and it reflects the change onto the sub-sheets). For instance, sheet reference F4 on the master contains a product code and to display it on a sub-sheet I am using the function (Mastersheet!F4). This then shows the same product code on the sub sheet. Let's say the entry is "Projector 3500".

However, if I'm on a sub sheet and do a FIND for "Projector 3500" (as I want to find it to see the price for this particular customer) it's not found as the contents of the cell is actually (Mastersheet!F4). Is there any way to make this work using the standard program buttons/options and NOT using a Macro anywhere?

There must be an easy way, surely?

 

[eduzs]

Did you try?

=VLOOKUP("Projector 3500",MasterSheetF:G,2,0)

 

[Gaztech]

Did you try?

=VLOOKUP("Projector 3500",MasterSheetF:G,2,0)

Hi, I think you miss the point... I'm sure that this will work. It makes sense, but..

There are employees generating quotes for us. They use the sub-sheets for client pricing and to get to the correct items (to get the correct price) they press the Find and Select button on the ribbon and enter the text to find. This is what doesn't work.

I want a simple way for them to do it using the controls and buttons to hand (even if they have to input the find string a specific way). If the method is complex at all they aren't going to be able to manage it. I'm looking for a standard operation method to do this.

 

[Lobsterboy1]

When you use the "FIND" try clicking "OPTIONS" and change "LOOK IN" to "VALUES".

 

[Gaztech]

Aha! More like it... this does work. It's still a bit complicated for our lot (yeah... don't say it! I encounter this all the time).

However, I think it's going to be the only way to do it. Unless someone knows a simpler way... If I could set the find to default this way maybe... anyone?

Thanks to everyone for the replies so far

 

[Lobsterboy1]

Once you have changed it to look for values once it will keep that setting until you close the workbook. Or you could use some vba code that changed it to values automatically as each sheet was selected but you said you didnt want macros.

 

[Gaztech]

Once you have changed it to look for values once it will keep that setting until you close the workbook. Or you could use some vba code that changed it to values automatically as each sheet was selected but you said you didnt want macros.

Ok. Thank you. Will see what I can do. Maybe it's just education. I can write a note about this and put it on their machines in a promonent place!. After all, it's not too much hardship to set this on the first search - -then they can leave the workshhet open.