Cross sheet dynamic conditional reference

[Runee]

Hi,

My google-fu is simply not good enough - or what I am trying is simply not possible... Or need some VB code I am not sharp enough to do.

I have an Excel document with several sheets - the amount of tabs grows over time, so trying to avoid directly referencing them, but somehow to enumerate them.
On these tabs/sheets are a (also dynamic) number of rows, each with a cell containing one of three possible strings. (Think "open", "closed" "progress")

What I want to achieve, is to have an "overview" sheet, where a copy of all rows that contains one(or two i.e either "open" or "progress") of the possible strings across all other sheets is listed. It should be a dynamic reference, so when I update anything in that row on its original sheet, the corresponding row on the overview sheet is also updated.

Is this even possible or too far-fetched?

 

[mumps]

It certainly is possible but we would need more information. Just to clarify, you want the row copied if contains one or more of the three strings. Is this correct? Can the 3 strings be anywhere in the sheet or only in certain columns? If only in certain columns, which columns and would the columns be the same in all sheets? DO you want the row to be copied automatically after entering one of the 3 strings manually in each sheet?

 

[Runee]

Hi,

Thank you for taking the time here!

So, all the sheets (except for this overview sheet of course) will be the same (different content, same layout). The strings are in column B always and will be one of 'open", "closed" or "progress". If it is either "open" or "progress" I want to appear on the overview. If it is "closed" it should not be there.

I'm not sure a copy will suffice? Unless it updates. So, the use case is that I edit/update the content sheets and get an updated overview.

If I:
- add a new row on any sheet it shows up on overview (if open or progress - but all new rows will be that)
- edit any row, it is updated on overview reflecting those changes
- change B to closed it is removed from overview
- change B to open or progress it appears in the overview.

So it needs to have this dynamic to it. And it would need to be the entire row displayed (columns a, b,c and d)

Does this make sense?

 

[mumps]

In order to do what you want, each row would need a unique identifier, for example, an ID number or some other value that will always be in the same column on all sheets and never duplicated across all sheets. Is this possible?

 

[Runee]

So, I have added an ID which is just a VB function returning sheetname + "-" & row number, giving a unique value such as "Project1 - 1". So, this is a calculated value - if it needs to be an entered static number, that is no problem - I have full control over what is in all the sheets.

 

[mumps]

If it could be static it would be better but either way it should work. In which column will this value be in each sheet?

 

[Runee]

Column A (starting at row 2 as there is a label called ID in A1)

 

[mumps]

I hate to be a pest!!! I have a plan that I think would work but I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using if any. If I can use the actual workbook, I can more clearly see the sheet structure such as how many columns you have, if there are any blank spaces, etc. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Runee]

That's not being a pest - I am just full of appreciation that you are even taking your time to look into this!

I have a clean copy on my work PC, will share it when I get to the office tomorrow!

 

[mumps]

Thanks. I'll wait for your posting.