How to extract names that are flagged by red dots

[excelhelp25]

Hello,

I'm trying to extract data from an spreadsheet.

The data that I need are the names of those that during a particular day have a special duty.

The day they have the duty is marked with a red cell that points to the name/date and E number.
(i like to extarct those 3 pieces of information for a particular day)

I have multiples tabs like this in the main spreadsheet, so here is my question:

1- In each tab - spreadsheet, can i create an empty column with VLOOKUP formula that looks at the red cell and extracts the day/e number and name

2 - can i just create one new tab and do the same formula that looks at ALL the spreadsheets an gives me the result for all rather than one?

I have tried several times, VLOOKUP, HLOOKUP, match etc but i'm getting frustated hence this is why i sign up to see if any of you could help me.

I have upload an example spreadsheet

Thank you very much in advance

Kind Regards,
David

[Richard Buttrey]

Hi,

I think you've made life slightly difficult for yourself since you're going to need VBA to assist you in recognising the cell colours. I don't know of any way using standard Excel functions. If you were prepared to populate the coloured cells with a character then you could use standard functions to identify them and calculate the values you're looking for. But bear in mind that functions like MATCH(), VLOOKUP(), INDEX() etc will only return one value.

However looking at the worksheet the coloured cells seem to follow a pattern. If there are always four to a row and they're always on a diagonal it should be possible to use functions like INDEX() along with a table of co-ordinates and offsets from a base cell to pick the values you want.

HTH

[excelhelp25]

Hello Richard

Thank you very much for your reply.

Would you recommend I change the red dots for ( i.e: X),

If I do so, what would be the appropiate formula?
Could i have one formula taking care of the multiple sheets.

The reason of why i need to extract de data each day is becasue i need those names to be entered in another system, by going spreadsheet by spreadsheet, sorting out, copying and paste is very much time consuming.

The formulas i'm trying are not being good friends to me today.

Any further advice will be appreciated.

I will now start the scenario of changing the colors for X since VB is not an option.

[Richard Buttrey]

Hi,

Can you add a results column which shows examples of the results you expect to see. Add at least one other sheet to reflect your requirement that data be extracted from many sheets.

Is there is an order to your data? This may be important. i.e. are the red colours always on a diagonal and either 4 or 5 per month?

Rgds

[excelhelp25]

will do right away

Thanks
David

[excelhelp25]

Hi Richard,

See attached, on the right hand side I wrote the results i expect to see.

for each day i want to extract those with an X with the match date

Not all the red cells (X) are position on a diagonal, some of them in other spreadsheets are just vertical

I have added a second tab for you to see.

Thank you
David

PS- the file is now attached

[excelhelp25]

update:

I'm trying pivot tables as well but again no much luck although i did get close to the result but no good enough

[Richard Buttrey]

Hi,

I think a macro is the only way to go given your current layout. A pivot table is not really much use since it basically summarises and counts results and is not primarily for extracting and reporting data.

You could use Data Filter Advanced - see the attached, but you're going to have to either change the AE1 cell each time or copy the AE1:AE6 cells across, change the dates in row 1 and perform many data filters. Obviously in that case you'd probably want a macro to do it for you.

To extract data from several sheets you, or a macro, would first need to build a master sheet which contains the details from all the individual sheets, and then use the master sheet with the data filter as described above.

HTH

[excelhelp25]

I finalized the final spreadsheet whith a different set of formulas for my first purpose but I'm still unccesful for my second purpose (the one we're discussing)

Is any book or something I could buy, I'm now travelling and I really want to have this done before my departure

I really thought Pivot tables would help me but you're right.

Best Regards,
David

[stanleydgromjr]

excelhelp25,

Detach/open workbook excelhelp25 - EF746777 - SDG12.xlsm.

Go to worksheet Results. Enter a text data into cell E1 and click on the Get Data command button.

[excelhelp25]

question for you..

what text data?, what should i enter under cell E1

Regards,
D

[stanleydgromjr]

excelhelp25,

what text data?, what should i enter under cell E1

In the workbook that I attached, in worksheet Results, enter into cell E1 a date like those in your sheets in row 2:
Sept 5th
Sept 21st

And, then click on the command button Get Data.

[excelhelp25]

Stan

Thank you so much, now I have something to get started this looks like a great solution.

Could you let me/us know how did you do it - what are the steps i have to follow

David

[stanleydgromjr]

excelhelp25,

Could you let me/us know how did you do it - what are the steps i have to follow

I do not understand. Please explain in more detail.

[excelhelp25]

I would like to learn how did you do it

Regards,
David

[stanleydgromjr]

excelhelp25,


I hope this helps you to understand the logic of the macro.

Please Login or Register  to view this content.