zmienić formułę komórki łącza między arkuszami

Mam skoroszyt o nazwie „stu”, ma od 1 do 30 arkuszy. W komórce H4połączyłem formułę:

=[sub.xlsx]Sheet1!$B$2

w arkuszu 1 : komórka H4 formuła w komórce H4powinna znajdować się =[sub.xlsx]Sheet1!$B$2
w arkuszu 2 : komórka H4 formuła w komórce H4powinna znajdować się =[sub.xlsx]Sheet1!$B$3
w arkuszu 3 : komórka H4 formuła w komórce H4powinna znajdować się =[sub.xlsx]Sheet1!$B$4
w arkuszu 4 : komórka H4 formuła w komórce H4powinna znajdować się =[sub.xlsx]Sheet1!$B$5
w arkuszu 5 : komórka H4 formuła w komórce H4powinna być… i =[sub.xlsx]Sheet1!$B$6
tak dalej…

..................................

Dlatego w każdym arkuszu komórka nr nie powinna zmieniać się automatycznie. Nie będę musiał naciskać = znak i wchodzić raz po raz.

W programie Excel wpisz Alt+, F11aby otworzyć okno Visual Basic. Po lewej kliknij prawym przyciskiem myszy nazwę swojego arkusza kalkulacyjnego i Insert→ Module. Module1 - CodeSub-okno zostanie otwarte po prawej stronie. Wstaw następujące okno w tym oknie:

Function SHEET_NUM()
    SHEET_NUM = Application.Caller.Parent.Index
End Function

Teraz wróć do arkusza kalkulacyjnego. Dla zabawy, aby zobaczyć, jak to działa, wpisz =SHEET_NUM() dowolną komórkę. Powinien wyświetlać numer arkusza, na którym jest. Więc wejdź

=INDIRECT("[sub.xlsx]Sheet1!$B$" & (SHEET_NUM()+1))

do komórki H4na każdym arkuszu. SHEET_NUM()+1Podwyrażenie oceni się 2na Sheet1, 3na Sheet2, etc ... - innymi słowy, liczba wierszy, które chcesz w celach informacyjnych.  &jest operatorem konkatenatu; połączy ciąg stały "[sub.xlsx]Sheet1!$B$" z obliczonym numerem wiersza, co da pożądane odwołanie. INDIRECTFunkcja przyjmuje ciąg znaków, który wygląda jak punkt odniesienia i DE-IT odniesienia; tzn. pobiera wskazaną wartość.

Kilka ostrzeżeń:

• Jeśli masz do czynienia z jednym skoroszytem stu.xlsxodwołującym się do innego ( sub.xlsx), musisz mieć otwartą drugą książkę za każdym razem, gdy pracujesz z pierwszą - chociaż program Excel może odwoływać się do danych w zamkniętej książce, INDIRECTfunkcja nie może. Jeśli jest to problem, możesz wykonać manewr Kopiuj i Wklej Wartości.
• Musisz zapisać swój skoroszyt jako .xlsmplik i włączyć makra przy każdym otwarciu. (Lub wykonaj wyżej wspomniany manewr Kopiuj i Wklej Wartości).
• AFAICT, arkuszom roboczym przypisuje się numer podczas ich tworzenia i trudno je zmienić. W szczególności, jeśli przenosisz swoje arkusze, zachowają oryginalne numery, co będzie mylące. (Ale z drugiej strony możesz zmienić ich nazwy bez obawy o zmianę ich liczby).

Poniższe kroki powinny uzyskać dokładnie taką formułę, jakiej szukasz, bez VBA:

1. Wybierz wszystkie arkusze, w których ma się pojawiać formuła (możesz dodać każdy arkusz do zaznaczenia, przytrzymując „Ctrl” i klikając jego zakładkę)
2. Wprowadź następującą formułę w innej pustej komórce, być może H3: = JEŻELI (LEWO (PRAWO (KOMÓRKA („nazwa pliku”, $ A $ 1), 2), 1) = „t”, PRAWO (KOMÓRKA („nazwa pliku”, $ A) 1 USD), 1), PRAWY (KOMÓRKA („nazwa pliku”, 1 A $ 1 USD), 2))
3. Wprowadź następującą formułę do komórki H4 (zastępując H3 komórką użytą w kroku 2): = "= [sub.xlsx] Arkusz1! $ B $" i H3 + 1
4. Wybierz komórkę H4, skopiuj i wklej wartości
5. Wyczyść formułę utworzoną w kroku 2
6. Znajdź i zamień „=” na „=” w całym skoroszycie (Ctrl + H, aby znaleźć i zamienić)
7. Anuluj wybór innych arkuszy (możesz usunąć każdy arkusz z zaznaczenia, przytrzymując „Ctrl” i klikając jego zakładkę)

Nie rób nic innego między krokami 1 i 7, chyba że wiesz, jak pracować z wybranymi wieloma arkuszami.

Najlepszym rozwiązaniem może być otwarcie pliku sub.xlsx, zgrupowanie wszystkich 30 arkuszy w stu, wstawienie jednej wersji formuły (np. = [Sub.xlsx] Arkusz 1! $ B 2 USD w H4 arkusza 1 stu), rozgrupowanie, a następnie ręczne dostosowanie H4 w każdym arkuszu STU (z wyjątkiem tego, w którym wprowadzono wzór, np. W arkuszu 2 zamień 2 na 3 itd.).