"Mirror" Cell Contents elsewhere in a workbook

[Thewyzewun]

Hi

I'm trying to do two things. One is very simple, and not ideal, the other perhaps slightly harder, but performs the same task as task one much more efficiently.

If I have fill out a few rows in Sheet 1 of a workbook, I'd like the ability to also show that information on Sheet 2.

I've tried entering =CELL(customcellrangename) into an individual cell of Sheet 2, and =(customcellrangename), but in response to either I get #VALUE!.

I've also tried entering =(customercellname) into a particular cell, and can see when I'm referencing an individual cell rather than a cell range (i.e. a selection that includes a few columns and rows) this works fine, I don't understand how to do it with a range though.

That's the simple version, I'm probably just using some syntax wrong.

What I'd really like to do is this:

In Sheet 1 I have rows and rows of data.
In Sheet 2, I want to display the entirety of any row containing the words "Action Required" in Column D. These words will not be mispelt, abbreviated etc so Excel will be able to find them if only I knew how to ask it to look!
To futher complicate matters, data from Sheet 3, Sheet 4 etc will need to be displayed aswell, and I will need some way of telling it apart, so I need to insert the Sheet Name into Column A, preceding each individual row of data.

Like I say, I'm sure I've done this before, but it was so many years ago I've either forgotten or...?

So thanks for any assistance you can offer

 

[GlennUK]

If you need to see subsets of data in sheet1, why not simply filter the list, so that you can see all Action Required, or any other column D item subset?

 

[Thewyzewun]

If you need to see subsets of data in sheet1, why not simply filter the list, so that you can see all Action Required, or any other column D item subset?

Hi

Because there's going to be over a hundred sheets. This is itself is a problem; I should really be building a database rather than consolidating all my separate workbooks into one new workbook (each belongs to a particular customer).
I do have a very good CRM solution called Solve360 which we use extensively, but without building a database, which I don't have the skill, time or mostly, money to build or commission.

For now though, this is a much better solution than either a. putting this on hold til I have the time or money for a database or b. using what scarce resources I currently have to build a database. Not a nice position to be in but I must make the best of what I have.

Thank you again for any help you can lend, I just need the vaguest nudge in the right direction and Google will indeed by my friend! Not to say that a more detailed explanation would not be so very appreciated .

 

[GlennUK]

What I mean is, why do you need to see the subsets in different sheets instead of filtering. Just saying that there will be over 100 sheets is not answering the question at all. What is the purpose of having the subsets mirrored?

 

[Thewyzewun]

What I mean is, why do you need to see the subsets in different sheets instead of filtering. Just saying that there will be over 100 sheets is not answering the question at all. What is the purpose of having the subsets mirrored?

Hi

Sorry when you said filtering, I imagined perhaps some drop-down boxes at the top of each individual sheet that would allow me to filter for rows containing a particular phrase, such as "Action Required" (I used to use a sheet someone else had coded that did this, but it was for a different purpose).

I'll read up tonight on filtering, it's probably what I'm looking for by the sounds of it .

 

[Thewyzewun]

Hi

Thanks very much! I've just read a few articles on Filtering (the first three google results to keywords "filter excel") and I can create advanced filters now which copy selected data to another sheet based on criteria.
Amazed how easy this is to use.

Unfortunately there's two things this tool cannot do for me.
It will not automatically update any changes made to one or the other copy. I've been looking at some forum posts with example VBA code and I should be able to address this.

It will not automatically re-apply the advanced filter. Again I can see this can be done with VBA.

I've set aside a few hours to get this done tonight and with my (very, very) basic understanding of Visual Basic I think I'll be able to do this.

Thanks for pointing me in the right direction, hopefully I've got this from here!

 

[Thewyzewun]

Hi

Thanks very much! I've just read a few articles on Filtering (the first three google results to keywords "filter excel") and I can create advanced filters now which copy selected data to another sheet based on criteria.
Amazed how easy this is to use.

Unfortunately there's two things this tool cannot do for me.
It will not automatically update any changes made to one or the other copy. I've been looking at some forum posts with example VBA code and I should be able to address this.

It will not automatically re-apply the advanced filter. Again I can see this can be done with VBA.

I've set aside a few hours to get this done tonight and with my (very, very) basic understanding of Visual Basic I think I'll be able to do this.

Thanks for pointing me in the right direction, hopefully I've got this from here!

 

[Thewyzewun]

I know it's been less than an hour, but I can't do this, and I can see it's complex enough that even if someone walks me through it, it's over my head..

Thanks for pointing me in the right direction Glenn, that Filter function will come in very handy until I've got the money to hire a programmer. I've actually got a few I've used in the past, I'm just not making enough money at the moment to justify the expense - I've been wanting some work done with Solve360's API for months but that's on-hold for now aswell.

 

[GlennUK]

I still don't see why you need to split the data and copy it to other sheets. Tell me why you cannot simply apply a normal Data AutoFilter on the main data, to view any subset of data at any time?

 

[Thewyzewun]

Here's a typical usage scenario, how I envison it anyway. If the individual steps I'm taking are illogical because there's a better, simpler way of doing this please let me know .

Each of the hundreds of sheets, which often have over 100 rows each, contains a status in row D.

I want to find and edit every row with a particular status. The edits have to be done manually, because I'm recording information on actions carried out elsewhere.

I click on Data, Autofilter, and select a table on a particular sheet.

Then I go to "Filter Sheet" and select the appropriate filter (all my filter options are very simple, there's generally only one or two basic critera).

Lastly I click on the Copy to Sheet radio button, navigate to "Review Sheet", select a blank cell and tell the filter to run.

It works brilliantly, but if I want to change anything, I cannot change it on the original sheet nor the "Review Sheet" without re-running the filter afterwards. Since I am making constant edits, all day long as I work through tasks, keeping the filter up-to-date is going to be a pain.

However, one possible solution has just occurred to me. I could look at the data every morning, grab the most important info and create tasks in another system I use, Solve360. I've already been doing this already, I just wanted an overview of everything available to me and AutoFilter even with the copy function does give me that ability.

Not that I've got that ability right now, the data's not in this new megaworkbook now, it's split across hundreds of individual workbooks, some of which are so old there's duplicates (from when collaboration wasn't so easy) and even if there's only one copy of each of those workbooks, I've still got to manually audit the work each row refers to before I'm storing it in the new workbook. So it should be finished in about 6 months (I don't have time to spend more than an hour on this every day, I'd spend all my time organizing my tasks rather than addressing them!).

If I sound massively understaffed, I am, I almost went out of business last year after one of my employees stole tens of thousands from me and I've had to lay off two staff just to stay afloat.

Thanks for your help Glenn .