[Solved] Fill multiple sheet cells referencing sheet#

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Tango44
Posts: 2
Joined: Thu Mar 25, 2021 9:43 pm

[Solved] Fill multiple sheet cells referencing sheet#

Post by Tango44 »

Hello

I am trying to make a macro that would automatically fill a sheets cell with data and assign a # value based on the number of the sheet.

Example
I have 20 sheets. I want every sheets A2 cell value to be filled with "=Sheet1.A#" where # would equal that sheets position in the list of sheets. So sheet #2 would get its A2 cell set as "=sheet1.A2".

I would like it to not apply for sheet 1. I also need it to fill the B2 value of every sheet with "=Sheet1.B#" in a similar fashion.
Last edited by Tango44 on Thu Mar 25, 2021 10:30 pm, edited 1 time in total.
OpenOffice 4.1.6 on Windows 10
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Macro to fill multiple sheet cells referencing sheet#

Post by MrProgrammer »

Hi, and welcome to the forum.
Tango44 wrote:I want every sheet's A2 cell value to be filled with "=Sheet1.A#" where # would equal that sheets position in the list of sheets.
No need for an evil macro. Select sheets 2 (click) through 20 (shift-click) and enter formula =INDIRECT("Sheet1.A"&SHEET()) once. Takes 30 seconds, tops.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Tango44
Posts: 2
Joined: Thu Mar 25, 2021 9:43 pm

Re: [Solved] Fill multiple sheet cells referencing sheet#

Post by Tango44 »

Worked like a dream thank you very much!
OpenOffice 4.1.6 on Windows 10
Post Reply