Skip to main content

Conas luach tagartha / bileog oibre ó bhileog oibre roimhe seo a úsáid in Excel?

Conas tagairt a dhéanamh do luach sonrach cille ó bhileog oibre roimhe seo agus cóip á chruthú in Excel? Mar shampla, agus cóip de Bhileog 2 á chruthú agat, b’fhéidir gur mhaith leat cill A1 de Bhileog 2 a tharchur go huathoibríoch sa bhileog oibre nua cóipeáilte (a deir Bileog3). Conas é a bhaint amach? Cabhróidh an t-alt seo leat.

Luach úsáide / tagartha ón mbileog oibre roimhe seo le feidhm atá sainithe ag an Úsáideoir
Úsáid / luach tagartha ón mbileog oibre roimhe seo le Kutools for Excel


Luach úsáide / tagartha ón mbileog oibre roimhe seo le feidhm atá sainithe ag an Úsáideoir

Le bheith ionraic, ní féidir le haon mhodh tagairt a dhéanamh go huathoibríoch do luach áirithe cille agus cóip de bhileog oibre á chruthú. I ndáiríre, is féidir leat bileog nua a chruthú agus ansin tagairt a dhéanamh do luach na cille ón mbileog oibre roimhe seo leis an bhfeidhm seo a leanas atá sainithe ag an úsáideoir.

1. Tar éis duit bileog oibre bán nua a chruthú (a deir Bileog3), brúigh le do thoil Eile + F11 eochracha ag an am céanna chun an Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

2. Sa Microsoft Visual Basic d’Fheidhmchláir fuinneog, cliceáil Ionsáigh > Modúil. Ansin cóipeáil agus greamaigh an cód VBA seo a leanas i bhfuinneog an Chóid.

VBA: Úsáid / luach tagartha ón mbileog oibre roimhe seo in Excel

Function PrevSheet(RCell As Range)
    Dim xIndex As Long
    Application.Volatile
    xIndex = RCell.Worksheet.Index
    If xIndex > 1 Then _
        PrevSheet = Worksheets(xIndex - 1).Range(RCell.Address)
End Function

3. Brúigh an Eile + Q eochracha ag an am céanna chun an Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

4. Roghnaigh cill bhán de Bhileog 3 (deir A1), iontráil an fhoirmle = Bileog PrevSet (A1) isteach sa Bar Foirmle agus ansin brúigh an Iontráil eochair.

Anois gheobhaidh tú luach cille A1 na bileoige oibre roimhe seo (Bileog 2) sa bhileog oibre reatha.

nótaí: Déanfaidh an cód an bhileog oibre a bhaineann leis an gceann roimhe seo den bhileog oibre reatha a shainaithint go huathoibríoch.


Úsáid / luach tagartha ón mbileog oibre roimhe seo le Kutools for Excel

Leis an Déan tagairt go dinimiciúil do bhileoga oibre fóntais de Kutools le haghaidh Excel, is féidir leat luach tagartha nó bileog tagartha roimhe seo a úsáid go héasca in Excel.

Roimh iarratas a dhéanamh Kutools le haghaidh Excel, Le do thoil é a íoslódáil agus a shuiteáil ar dtús.

1. Más mian leat luach chill A1 a thagairt ón mbileog oibre roimhe seo go dtí an bhileog oibre reatha, roghnaigh cill A1 sa bhileog oibre reatha agus ansin cliceáil Kutools > níos mó > Déan tagairt go dinimiciúil do bhileoga oibre. Féach an pictiúr:

2. Sa Líon Tagairtí Bileoga Oibre bosca dialóige, ná seiceáil ach ainm an bhileog oibre roimhe seo sa Liosta bileog oibre bosca, agus ansin cliceáil ar an Raon Líon cnaipe.

Ansin feiceann tú go ndéantar tagairt do luach chill A1 sa bhileog oibre roimhe seo sa bhileog oibre reatha.

nótaí: Leis an bhfóntas seo, is féidir leat an luach cille céanna ó bhileoga oibre éagsúla a thagairt isteach sa bhileog oibre reatha ag an am céanna.

  Más mian leat triail saor in aisce (30 lá) a bheith agat ar an bhfóntas seo, cliceáil le do thoil chun é a íoslódáil, agus ansin téigh chun an oibríocht a chur i bhfeidhm de réir na gcéimeanna thuas.


Úsáid / luach tagartha ón mbileog oibre roimhe seo le Kutools for Excel

Uirlisí Táirgiúlachta Oifige is Fearr

Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup    VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle    Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla    Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

cluaisín kte 201905


Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (22)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi, Your code works perfectly! The intent for me is to fill a column on one of the worksheets which would update a linear graph. My question is, how do I add the new number entered on a new worksheet, while still maintaining the numbers the same of the previous sheets. For example, I entered PrevSheet(A1) on Sheet 2, which takes the number for A1 cell from Sheet 1. So I went to cell A2 of Sheet 2, and entered PrevSheet(A1) again. The intent is if I copy and paste a new sheet (Let's call it Sheet N), and I update cell A1 in Sheet N with a different value, Sheet 2 will have the value from Sheet 1 in cell A1, and the Value from Sheet N in cell A1 in cell A2. But what happens is that is updates both cell A1 and A2 in Sheet 2 to the same values as in Sheet N. This makes sense because it's taking the values from the previous sheet. Hope I didn't confuse you. Thanks!
This comment was minimized by the moderator on the site
Hey.

I'm using Greek Office 365 with all updates installed, and I can't get this to work.

It returns name error at the cell I input =PrevSheet(A1).

Both sheets have the vba code and the file is saved as .xlsb

Any clues?
Thanks in advance :)
This comment was minimized by the moderator on the site
Hi Sotos,
You need to put the VBA in the Module (code) window (press the Alt + F11 keys to open the Visual Editor, click Insert > Module to add a new Module editor, and the enter the code). Then apply the formula.
This comment was minimized by the moderator on the site
THANK YOU VERY VERY MUCH! VERY HELPFUL!
This comment was minimized by the moderator on the site
Excellent!!! VBA code thanks a lot
This comment was minimized by the moderator on the site
Excellent I have used previous sheet function, but, if i do any additions of rows or columns in previous sheet, the values are getting change I want constant result. for example if i select =prevsheet(A6) where the value is there, but in the previous sheet if I add one row the value will go to A7, but in current sheet it is taking the value of A6 only, can u help me
This comment was minimized by the moderator on the site
I tried the code but it returns formulas not allowed. The cell of the previous worksheet is a result of a formula., how do you bypass the formula and return the result? The previous sheet cell returns the formula of =SUM(L38:L39)-L40
This comment was minimized by the moderator on the site
Hi RudyT,I tested it in the circumstance you mentioned, but I have no problem here. Which Excel version are you using?
This comment was minimized by the moderator on the site
Wow, that was 3 months ago, but surely appreciate the reply...

I don't know how to check Excel 365... The problem is I have a a value that I want to pass to new sheet, that must read, get value of previous sheet without naming it. Just get the previous sheet value. Your code passes the code of the previous sheet, and not the value. Because the value of the cell is a formula that gathers the totals from 2 other cells.
This comment was minimized by the moderator on the site
I followed steps 1-4, but I keep getting a #NAME error. Excel 2016. Please advise?
This comment was minimized by the moderator on the site
Hi Anon,
The code works well in my Excel 2016. Did you find specific error reminded in your code window when getting the #NAME error result?
This comment was minimized by the moderator on the site
I followed steps 1-4 but I keep returning a #NAME? error - Please advise?
This comment was minimized by the moderator on the site
Hi Andrew,
Which Office version do you use?
This comment was minimized by the moderator on the site
What about use value from Next Sheet?
This comment was minimized by the moderator on the site
Hi Alex,
To get the value from next Sheet, please replace the minus sign with plus sign in the sixh line of the code:
PrevSheet = Worksheets(xIndex + 1).Range(RCell.Address)
This comment was minimized by the moderator on the site
Hi Crystal, I tried replacing the minus sign with a plus sign but it didn't work - the relevant cell just returned a zero. I'm just wanting the change(s) required to do exactly as above BUT with the workbook numbering going from right to left, ie. Sheet3, Sheet 2, Sheet1 (the previous sheet will be the one to the right). Thanks
This comment was minimized by the moderator on the site
Hi Mav,
The method works based on the worksheets' order in your workbook. ie. The worksheets order from left to right are Sheet3, Sheet2 and Sheet1. For Sheet2, it's previous worksheet is Sheet3, and it's next worksheet is Sheet1.
This comment was minimized by the moderator on the site
Hi Crystal,
Yes I know. My query (like Alex's) is what change is required to make it work the other way, ie, for the next sheet, where the next sheet is to the left of the preceding sheet? You wrote to Alex,
"please replace the minus sign with plus sign in the sixh line of the code:
PrevSheet = Worksheets(xIndex + 1).Range(RCell.Address)".
My comment was just to say that that particular change did not work and to ask if you have a way that will actually work.
This comment was minimized by the moderator on the site
I've been looking for something to do this forever, thank you so much you have no idea. I have a simple spreadsheet for data entry weekly and a new tab for each week. the days, date, week #, etc goes +1 and then it grabs from previous sheet and moves to new one, this created a fast solution to data transfering to different sheets--- Again thank you
This comment was minimized by the moderator on the site
Good Day,
Sorry I can’t help with this, you can post your question in our forum: https://www.extendoffice.com/forum.html to get more Excel supports from our professional.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations