Display cell values in formula bar

**Toenail** · 04-08-2013, 06:35 AM

I have a problem where I am linking data from two worksheets say:
worksheet 1: A1 = 10, A2 = 20
worksheet 2: A1 = =Sheet1!A1, A2 = =Sheet1!A2

I have filled this formula through a certain number of cells on sheet 2 so that if i add more data to sheet1 it will appear in the appropriate place on sheet2.
Is it possible to show the formula results in the formula bar, i.e. sheet2: A1=10, A2=20, such that i can used 'Find' or 'Autofilter' on the cell values rather than the formula?

Thanks
Niall

**Jakobshavn** · 04-08-2013, 08:28 AM

No............the Formula Bar displays formulas
The cell can display either formulas for results by toggling CNTRL-`

**JosephP** · 04-08-2013, 08:59 AM

find can be told to search values instead of formulas and autofilter only works on the values anyway

**Toenail** · 04-08-2013, 09:58 AM

Worked out how to use find to search values, thanks.

My problem with autofilter is, i have filled the formula on sheet2 to include more cells than already have data in sheet1, so that when more data it added it automatically appears in sheet2, those cells with no data appear on sheet2 as zeros (which have been removed via tool->options->view), I think the autofilter is still including these zero value when sorting.

For example, if sheet1 has 5 values and i have made the first ten values on sheet2 equal to that of sheet1 (incase more data is added to sheet1), on sheet2 the first 5 values appear and the last five show zero values (which can be removed in tools), then we i 'sort ascending', the five empty cells appear first followed by the five cells with data.

Is there anyway around this?

**JosephP** · 04-08-2013, 10:05 AM

perhaps use
=IF(Sheet1!A1="","",Sheet1!A1)

**Toenail** · 04-08-2013, 10:50 AM

That has the converse problem, the empty cell (containing formula) are sorted to the top when you use 'sort descending'. I want to exclude the empty cells from the sort

**JosephP** · 04-08-2013, 11:16 AM

they are not empty which is the problem. the best solution in my opinion is to rethink whatever your reason for linking the data is-it sounds unnecessary to me-or to use formulas that sort the data the way you want

**Toenail** · 04-08-2013, 01:04 PM

The reason I am doing this is because i have a massive database and i want to break it down into smaller classes for ease of visual inspection. Im trying to set it up for someone with absolutely no excel experience so i want it to be as simple as possible.

Thanks for your consideration.