Enter values from series across multiple sheet

**kashif.shah** · 05-18-2019, 08:13 AM

Hi,

It should be simple, but couldn't really find a function to do this

Sheet1 has a column with 5 values

A1 23.4
A2 33.4
A3 44.5
A4 61.9
A5 88.3

Now, there are 5 Sheet more, Sheet2, Sheet3,.....Sheet6

Need

Value of A1 from Sheet1, to be entered in Cell C23 of Sheet2
Value of A2 from Sheet1, to be entered in Cell C23 of Sheet3
Value of A3 from Sheet1, to be entered in Cell C23 of Sheet4
Value of A4 from Sheet1, to be entered in Cell C23 of Sheet5
Value of A5 from Sheet1, to be entered in Cell C23 of Sheet6

Please help

**63falcondude** · 05-18-2019, 08:23 AM

Need

Value of A1 from Sheet1, to be entered in Cell C23 of Sheet2
Value of A2 from Sheet1, to be entered in Cell C23 of Sheet3
Value of A3 from Sheet1, to be entered in Cell C23 of Sheet4
Value of A4 from Sheet1, to be entered in Cell C23 of Sheet5
Value of A5 from Sheet1, to be entered in Cell C23 of Sheet6

=Sheet1!A1
=Sheet1!A2
=Sheet1!A3
=Sheet1!A4
=Sheet1!A5

**kashif.shah** · 05-18-2019, 08:29 AM

Yes, it is easy, but one thing I forgot to mention is actually there are fifty sheets, and several series, sick of copy pasting one by one, or entering reference for each

**63falcondude** · 05-18-2019, 08:33 AM

If you are saying that you don't want to manually put a formula in, this will require VBA which I cannot assist with.

**kashif.shah** · 05-18-2019, 08:36 AM

Oh, I thought may be it is possible with existing functions of excel, thanks for your effort anyway

**MrShorty** · 05-18-2019, 10:44 AM

You may not like this solution, either, but here's how I would probably do this:

1) The tedious step: in sheet2 C22, enter 1. In sheet3 C22, enter 2, and so on to sheet 51. (I don't have Excel on this computer, so I tested in LibreOffice Calc, and discovered that LibreOffice supports relative sheet references, which would make this step less tedious -- if you are not required to use Excel for this).
2) in Sheet2 C23, enter =INDEX(sheet1!$A$1:$A$100,C22) [INDEX() function help file: https://support.office.com/en-us/art...2-b56b061328bd ]. Copy Sheet2 C23 to clipboard (ctrl-C or however you prefer to copy cells to the clipboard).
3) Select sheet3 -> shift -> select sheet 51 to select all tabs between sheet3 and sheet51 (all of those tabs should be highlighted).
4) Select C23 of the active sheet -> paste.
5) Navigate through the sheets/tabs to make sure Excel correctly pasted the formula in C23 of each sheet/tab. Be sure to select sheet1 or sheet2 or right click on the selected tabs and choose "ungroup" from the context menu to "ungroup" the sheets before making other edits (when you have multiple sheets/tabs selected, edits made on one sheet will also be made to all selected sheets/tabs).

That's how I might do this.

**torachan** · 05-18-2019, 01:38 PM

Macro

Please Login or Register  to view this content.

torachan.

Enter values from series across multiple sheet

LinkBack

Thread Tools

Rate This Thread

Display

Enter values from series across multiple sheet

Re: Enter values from series across multiple sheet

Re: Enter values from series across multiple sheet

Re: Enter values from series across multiple sheet

Re: Enter values from series across multiple sheet

Re: Enter values from series across multiple sheet

Re: Enter values from series across multiple sheet

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] find 1 of 3 values on one sheet, from a lookup table on another sheet, enter on 3rd sheet

ActiveX Command Button Code to enter a cell values from a series of cells to another

VBA to enter form values in sheet

Lookup and enter multiple values

Excel 2007 : To enter series of data onto same cell of multiple sheets

Manually Enter X-Y Range Data to Series Values

[SOLVED] multiple series of values graphed with indep. x values -possible?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions