Skip i'r prif gynnwys

Sut i ddefnyddio / cyfeirio gwerth o'r daflen waith flaenorol yn Excel?

Sut i gyfeirio at werth celloedd penodol o daflen waith flaenorol wrth greu copi yn Excel? Er enghraifft, wrth greu copi o Sheet2, efallai yr hoffech chi gyfeirio cell A1 o Daflen 2 yn awtomatig i'r daflen waith a gopïwyd (meddai Sheet3). Sut i'w gyflawni? Bydd yr erthygl hon yn eich helpu chi.

Gwerth defnydd / cyfeirnod o'r daflen waith flaenorol gyda swyddogaeth wedi'i diffinio gan y Defnyddiwr
Defnydd / gwerth cyfeirio o'r daflen waith flaenorol gyda Kutools ar gyfer Excel

Gwerth defnydd / cyfeirnod o'r daflen waith flaenorol gyda swyddogaeth wedi'i diffinio gan y Defnyddiwr

I fod yn onest, ni all unrhyw ddull gyfeirio at werth celloedd penodol yn awtomatig wrth greu copi o daflen waith. Mewn gwirionedd, gallwch greu taflen newydd ac yna cyfeirio gwerth y gell o'r daflen waith flaenorol gyda'r swyddogaeth ganlynol a ddiffiniwyd gan y defnyddiwr.

1. Ar ôl creu taflen waith wag newydd (meddai Sheet3), pwyswch Alt + F11 allweddi ar yr un pryd i agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.

2. Yn y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr, cliciwch Mewnosod > Modiwlau. Yna copïwch a gludwch y cod VBA canlynol i mewn i ffenestr y Cod.

VBA: Gwerth defnydd / cyfeirnod o'r daflen waith flaenorol yn 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. Gwasgwch y Alt + Q allweddi ar yr un pryd i gau'r Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.

4. Dewiswch gell wag o Daflen3 (meddai A1), nodwch y fformiwla = PrevSheet (A1) i mewn i'r Bar Fformiwla ac yna pwyswch y Rhowch allweddol.

Nawr fe gewch werth cell A1 y daflen waith flaenorol (Sheet2) yn y daflen waith gyfredol.

Nodyn: Bydd y cod yn adnabod y daflen waith sy'n perthyn i un flaenorol y daflen waith gyfredol.

Defnydd / gwerth cyfeirio o'r daflen waith flaenorol gyda Kutools ar gyfer Excel

Efo'r Cyfeiriwch yn Dynamically at Daflenni Gwaith cyfleustodau Kutools ar gyfer Excel, gallwch chi ddefnyddio neu gyfeirio gwerth yn hawdd o daflen waith flaenorol yn Excel.

Cyn gwneud cais Kutools ar gyfer Excel, os gwelwch yn dda ei lawrlwytho a'i osod yn gyntaf.

1. Os ydych chi am gyfeirio gwerth cell A1 o'r daflen waith flaenorol i'r daflen waith gyfredol, dewiswch gell A1 yn y daflen waith gyfredol ac yna cliciwch Kutools > Mwy > Cyfeiriwch yn Dynamically at Daflenni Gwaith. Gweler y screenshot:

2. Yn y Llenwi Cyfeiriadau Taflenni Gwaith blwch deialog, gwiriwch enw'r daflen waith flaenorol yn y Rhestr taflen waith blwch, ac yna cliciwch ar y Llenwch Ystod botwm.

Yna gallwch weld bod cyfeiriad at werth cell A1 yn y daflen waith flaenorol yn y daflen waith gyfredol.

Nodyn: Gyda'r cyfleustodau hwn, gallwch gyfeirio'r un gwerth celloedd o wahanol daflenni gwaith i'r daflen waith gyfredol ar unwaith.

  Os ydych chi am gael treial am ddim (30 diwrnod) o'r cyfleustodau hwn, cliciwch i'w lawrlwytho, ac yna ewch i gymhwyso'r llawdriniaeth yn ôl y camau uchod.

Defnydd / gwerth cyfeirio o'r daflen waith flaenorol gyda Kutools ar gyfer Excel

Offer Cynhyrchiant Swyddfa Gorau

🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog  |   VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr   |  Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd   |  Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau:  12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

Disgrifiad

Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
Comments (22)
No ratings yet. Be the first to rate!
Alex
Alex
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!
Reply
0
0
Sotos
Sotos
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 :)
Reply
0
0
crystal
crystal     Sotos
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.
Reply
Report
0
0
YU
YU
This comment was minimized by the moderator on the site
THANK YOU VERY VERY MUCH! VERY HELPFUL!
Reply
0
0
Sh
Sh
This comment was minimized by the moderator on the site
Excellent!!! VBA code thanks a lot
Reply
0
0
srinath
srinath
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
Reply
0
0
RudyT
RudyT
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
Reply
Report
0
0
crystal
crystal     RudyT
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?
Reply
Report
0
0
rudyT
rudyT     crystal
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.
Reply
0
0
Anon
Anon
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?
Reply
0
0
crystal
crystal     Anon
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?
Reply
Report
0
0
Andrew
Andrew
This comment was minimized by the moderator on the site
I followed steps 1-4 but I keep returning a #NAME? error - Please advise?
Reply
0
0
crystal
crystal     Andrew
This comment was minimized by the moderator on the site
Hi Andrew,
Which Office version do you use?
Reply
Report
0
0
Alex
Alex
This comment was minimized by the moderator on the site
What about use value from Next Sheet?
Reply
0
0
crystal
crystal     Alex
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)
Reply
Report
0
0
Mav
Mav     crystal
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
Reply
Report
0
0
crystal
crystal     Mav
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.
Reply
Report
0
0
Mav
Mav     crystal
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.
Reply
Report
0
0
Ryan
Ryan
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
Reply
0
0
crystal
crystal     Ryan
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.
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations