Automatic data alternating per worksheet

S

swannylfc

New Member
Joined
Aug 4, 2014
Messages
27
Hi there.

I have a workbook which has a worksheet for every day in the month. On each worksheet there is a table which groups, say, cities together according to the country they are in, and each city will have a 1 or a 0 in the right-hand column to determine if that city was covered that day. This table remains in the same position on each worksheet. Every day one city from each country must be covered. A '1' in the right hand column means that the city was covered that day and a '0' means that it wasn't covered. Every day, this has to be alternated. So if the below table was for the 1st of April...

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]NEW YORK[/TD]
[TD]USA[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]LOS ANGELES[/TD]
[TD]USA[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]TEXAS[/TD]
[TD]USA[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]LONDON[/TD]
[TD]ENGLAND[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]BIRMINGHAM[/TD]
[TD]ENGLAND[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]LIVERPOOL[/TD]
[TD]ENGLAND[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]SYDNEY[/TD]
[TD]AUSTRALIA[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]BRISBANE[/TD]
[TD]AUSTRALIA[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]TOKYO[/TD]
[TD]JAPAN[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]NAGASAKI[/TD]
[TD]JAPAN[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]PARIS[/TD]
[TD]FRANCE[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]LYONS[/TD]
[TD]FRANCE[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]MARSEILLES[/TD]
[TD]FRANCE[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]GENEVA[/TD]
[TD]SWITZERLAND[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]MUNICH[/TD]
[TD]GERMANY[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]BERLIN[/TD]
[TD]GERMANY[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]HAMBURG[/TD]
[TD]GERMANY[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]FRANKFURT[/TD]
[TD]GERMANY[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]BREMEN[/TD]
[TD]GERMANY[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


Then on the 2nd of April, the city which is covered will be the next city down on the list for that country, like so...


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]NEW YORK[/TD]
[TD]USA[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]LOS ANGELES[/TD]
[TD]USA[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]TEXAS[/TD]
[TD]USA[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]LONDON[/TD]
[TD]ENGLAND[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]BIRMINGHAM[/TD]
[TD]ENGLAND[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]LIVERPOOL[/TD]
[TD]ENGLAND[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]SYDNEY[/TD]
[TD]AUSTRALIA[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]BRISBANE[/TD]
[TD]AUSTRALIA[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]TOKYO[/TD]
[TD]JAPAN[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]NAGASAKI[/TD]
[TD]JAPAN[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]PARIS[/TD]
[TD]FRANCE[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]LYONS[/TD]
[TD]FRANCE[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]MARSEILLES[/TD]
[TD]FRANCE[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]GENEVA[/TD]
[TD]SWITZERLAND[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]MUNICH[/TD]
[TD]GERMANY[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]BERLIN[/TD]
[TD]GERMANY[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]HAMBURG[/TD]
[TD]GERMANY[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]FRANKFURT[/TD]
[TD]GERMANY[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]BREMEN[/TD]
[TD]GERMANY[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

The third day will be...


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]NEW YORK[/TD]
[TD]USA[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]LOS ANGELES[/TD]
[TD]USA[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]TEXAS[/TD]
[TD]USA[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]LONDON[/TD]
[TD]ENGLAND[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]BIRMINGHAM[/TD]
[TD]ENGLAND[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]LIVERPOOL[/TD]
[TD]ENGLAND[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]SYDNEY[/TD]
[TD]AUSTRALIA[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]BRISBANE[/TD]
[TD]AUSTRALIA[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]TOKYO[/TD]
[TD]JAPAN[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]NAGASAKI[/TD]
[TD]JAPAN[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]PARIS[/TD]
[TD]FRANCE[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]LYONS[/TD]
[TD]FRANCE[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]MARSEILLES[/TD]
[TD]FRANCE[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]GENEVA[/TD]
[TD]SWITZERLAND[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]MUNICH[/TD]
[TD]GERMANY[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]BERLIN[/TD]
[TD]GERMANY[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]HAMBURG[/TD]
[TD]GERMANY[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]FRANKFURT[/TD]
[TD]GERMANY[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]BREMEN[/TD]
[TD]GERMANY[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

...and so on.

The far right column (the 1's and 0's column) is a COUNTIF reading if a given range contains the city. The cells in this range are currently populated manually. The order of the country cell will change from day to day. One example is below (for April 3rd shown above), though the order of the countries/cities will change each day.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]USA[/TD]
[TD]NEW YORK[/TD]
[/TR]
[TR]
[TD]SWITZERLAND[/TD]
[TD]GENEVA[/TD]
[/TR]
[TR]
[TD]GERMANY[/TD]
[TD]BREMEN[/TD]
[/TR]
[TR]
[TD]FRANCE[/TD]
[TD]LYONS[/TD]
[/TR]
[TR]
[TD]ENGLAND[/TD]
[TD]LIVERPOOL[/TD]
[/TR]
[TR]
[TD]AUSTRALIA[/TD]
[TD]BRISBANE[/TD]
[/TR]
[TR]
[TD]JAPAN[/TD]
[TD]TOKYO[/TD]
[/TR]
</tbody>[/TABLE]


I want to know if there is a way to automatically write the city to the appropriate cell (i.e. adjacent to the country the city is in) for each day of the month, according to the selection rules described earlier in this post (rather than eyeballing the table on the day previously to figure out which is the next city to cover). Please note that each country cell will continue to be populated manually. So for the above example, if I enter 'ENGLAND' into cell A1 I want cell B1 to automatically write 'LIVERPOOL'. The next day, when I enter 'ENGLAND' into, say, A3 I want B3 to show 'LONDON' as this is the next city in the list to be covered.

Sorry if this is convoluted. I tried to edit this to make sense, honest!
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Hi,

I think you will need two tables rather like the ones you have shown above - but without the 0/1 column.

I could have combined the formulas together into one formula but that only serves to make it less understandable.

The first table needs to count up the number of cities in a country. a COUNTIF() will do that. That number can then be used to work out a remainder based on the date serial number. If we take Germany for example the calculation will be: =MOD(Date,5). That will give the offset of the city from the first row of that country.

The first row of a country can be found using a MATCH() function. The sum of the offset and the first country row will give the city row.

If you want to enter countries and have the city looked up then you will need a third type of table. Probably this will be the one on the daily worksheets. If you enter a country in column A, column B would have a VLOOKUP to extract the city from the second table above.

For the example, I have inserted a date into one of the cells. You could read that from the worksheet name, for instance, instead.


Excel 2013
ABC
1CityCountryCount
2NEW YORKUSA3
3LOS ANGELESUSA3
4TEXASUSA3
5LONDONENGLAND3
6BIRMINGHAMENGLAND3
7LIVERPOOLENGLAND3
8SYDNEYAUSTRALIA2
9BRISBANEAUSTRALIA2
10TOKYOJAPAN2
11NAGASAKIJAPAN2
12PARISFRANCE3
13LYONSFRANCE3
14MARSEILLESFRANCE3
15GENEVASWITZERLAND1
16MUNICHGERMANY5
17BERLINGERMANY5
18HAMBURGGERMANY5
19FRANKFURTGERMANY5
20BREMENGERMANY5
Sheet1
Cell Formulas
RangeFormula
C2=COUNTIF($B$2:$B$20,B2)



Excel 2013
ABCDE
1DayNo:23/03/2015
2
3CountryNoDate RmdrCountry Row
4USA321TEXAS
5ENGLAND324LIVERPOOL
6AUSTRALIA207SYDNEY
7JAPAN209TOKYO
8FRANCE3211MARSEILLES
9SWITZERLAND1014GENEVA
10GERMANY5115BERLIN
Sheet2
Cell Formulas
RangeFormula
B4=VLOOKUP($A4,Sheet1!$B$2:$C$20,2,FALSE)
C4=MOD($B$1,$B4)
D4=MATCH(A4,Sheet1!$B$2:$B$20,0)
E4=INDEX(Sheet1!$A$2:$A$20,$C4+$D4)
 
Upvote 0
RickXL said:
Hi,

I think you will need two tables rather like the ones you have shown above - but without the 0/1 column.

I could have combined the formulas together into one formula but that only serves to make it less understandable.

The first table needs to count up the number of cities in a country. a COUNTIF() will do that. That number can then be used to work out a remainder based on the date serial number. If we take Germany for example the calculation will be: =MOD(Date,5). That will give the offset of the city from the first row of that country.

The first row of a country can be found using a MATCH() function. The sum of the offset and the first country row will give the city row.

If you want to enter countries and have the city looked up then you will need a third type of table. Probably this will be the one on the daily worksheets. If you enter a country in column A, column B would have a VLOOKUP to extract the city from the second table above.

For the example, I have inserted a date into one of the cells. You could read that from the worksheet name, for instance, instead.


Excel 2013
ABC
1CityCountryCount
2NEW YORKUSA3
3LOS ANGELESUSA3
4TEXASUSA3
5LONDONENGLAND3
6BIRMINGHAMENGLAND3
7LIVERPOOLENGLAND3
8SYDNEYAUSTRALIA2
9BRISBANEAUSTRALIA2
10TOKYOJAPAN2
11NAGASAKIJAPAN2
12PARISFRANCE3
13LYONSFRANCE3
14MARSEILLESFRANCE3
15GENEVASWITZERLAND1
16MUNICHGERMANY5
17BERLINGERMANY5
18HAMBURGGERMANY5
19FRANKFURTGERMANY5
20BREMENGERMANY5
Sheet1
Cell Formulas
RangeFormula
C2=COUNTIF($B$2:$B$20,B2)



Excel 2013
ABCDE
1DayNo:23/03/2015
2
3CountryNoDate RmdrCountry Row
4USA321TEXAS
5ENGLAND324LIVERPOOL
6AUSTRALIA207SYDNEY
7JAPAN209TOKYO
8FRANCE3211MARSEILLES
9SWITZERLAND1014GENEVA
10GERMANY5115BERLIN
Sheet2
Cell Formulas
RangeFormula
B4=VLOOKUP($A4,Sheet1!$B$2:$C$20,2,FALSE)
C4=MOD($B$1,$B4)
D4=MATCH(A4,Sheet1!$B$2:$B$20,0)
E4=INDEX(Sheet1!$A$2:$A$20,$C4+$D4)
Click to expand...

Wow, Rick... thank you so much for your well thought out answer which you explained impeccably. I will try this way and post here if I encounter any issues. Thank you again, this really helps.
 
Upvote 0
Thank you for that. I have to say this was not the easiest one to explain. I am pleased you could understand it.

Just one extra point you may find useful: If you add a number to the date in the =MOD() formula then you will be able to start the sequence from your choice of city.

For instance:
=MOD($B$1 + 1, $B4)

This may be useful if you want to continue with the existing sequence and not have a jump.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
AND and OR with INDEX MATCH problem
2
Replies
15
Views
934
markly
M
G
  • Solved
How to use IFERROR, INDEX, MATCH if the amount of column is limited
Replies
6
Views
884
Fluff
Fluff
F
  • Solved
Array formula to show in matrix form data from table?
Replies
7
Views
468
Fractalis
F
C
  • Solved
Help Deleting some blank rows based on a condition?
Replies
6
Views
448
Coyotex3
C
V
  • Question Question
Create VBA loop based on list
Replies
1
Views
190
Vbanoob98
V

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top